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')
            alter table dbo.sysmergesubscriptions drop column last_local_recgen
            alter table dbo.sysmergesubscriptions add last_local_recgen bigint null