Tuesday 11 August 2015

Merge replication error after SQL 2012 upgrade

After upgrading a server to SQL 2012 that is running as a publisher of a merge replication publication, you may see an error from the subscription agents along the lines of:

The merge process could not perform retention-based metadata cleanup in database 'Grumpy'
Operand type clash: bigint is incompatible with uniqueidentifier

Don't panic - you don't need to drop or delete the replication!

The problem is caused because SQL 2012 has a different data type in the sysmergesubscriptions table for one of the columns, compared to SQL 2008 R2 - but the upgrade does not address this.

This article from Microsoft indicates that you can apply Cumulative update 3 to fix the probem, or an easier fix is to apply the workaround, which just drops and recreates the column in question, but with the correct data type.

if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id 
                where object_id = object_id('dbo.sysmergesubscriptions') 
 and sc.name = 'last_local_recgen' and st.name = 'uniqueidentifier')
        begin
            alter table dbo.sysmergesubscriptions drop column last_local_recgen
            alter table dbo.sysmergesubscriptions add last_local_recgen bigint null
        end


Wednesday 17 June 2015

Managment Studio Icons

As businesses slowly migrate from SQL 2008 R2 to SQL 2012 and SQL 2014, you'll probably find you have multiple copies of Management Studio installed on your pc.
There are times when you might want to use different versions of SSMS, depending on the activity and the SQL instance version you are connecting to.

I stick the icon on my desktop, but by doing that, you have no indication of which version of SSMS the icon relates to, until you rename the text.
That works for identifying it when launching it, but what about if you have a 2008 SSMS and 2012 SSMS open, now you have the same icon on your task bar ...

So here are some new icons - click on them to download the ico file (rather than the png image shown below).

 SSMS 08 Icon SSMS 12 Icon SSMS 14 Icon



Once saved to your machine, right click on your current SSMS icons, choose properties, change icon, and there you go ... custom SSMS icons.

Tuesday 13 January 2015

SQL Login Failed: Token-based server access validation failed with an infrastructure error

Something I'd forgotten when I was setting up a new SQL server, was how the UAC (User Account Controls) permissions affect connections to SQL when the windows account in question is part of a group.

I've only experienced this with sysadmin accounts, but the problem can occur in applications or even Management Studio, where users who should have full sysadmin access find they cannot connect at all.

The only clue is that the SQL log contains the lines:

Source: Logon
Message: Error: 18456, Severity: 14, State: 11.

and

Source: Logon
Message: Login failed for user 'DOMAIN\grumpydba'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1.2.3.4]

Issue is that SQL doesn't seem to apply sysadmin access rights to users / accounts that only exist in SQL groups.

Two quick fixes:

1. If you are opening SQL Management Studio, right click and select "Run as administrator".  This should give you the access you expect.

2. Add the account explicitly as a SQL login, and grant sysadmin.  (Use this if for a system account) This will give the account the relevant permissions and able to connect.