When Did Merge Replication Subscribers Last Sync?
The article presents an automated process to see when remote servers last synced to publisher and send notification reminders.
2011-08-18
2,043 reads
/******************************************************** <# .SYNOPSIS Generates a script to help resolve type 5 & 6 conflicts .DESCRIPTION When a record with the same primary key value(s) is inserted on 2 replicated databases at the same time, a pair of conflicts 5 and 6 will occur: Both copies can not be transfered to the database that already holds the other copy. The only feasible way to resolve this is to remove one copy and have the other copy re-replicated to make it available in all locations. Usually it is arbitrary which copy is maintained, so we pick the easiest method: delete the copy on the hub (= upload failed) and have the copy on the publisher sent out to all hubs (most hubs will already have the publisher's copy). A complication can occur when a foreign key exists referencing the row to delete. Although the value will 'come back' once the conflict is resolved, we need to delete it with the referencing values still in place. Merge replication can do so because it ignores all foreign key constraints, but for us the best way to do so is to temporary disable the foreign keys. Don't forget to re-enable those constraints when you're done! A separate script exists that can be used to disable/enable a table's foreign keys if you encounter this issue. The way this works: - Run this script on the publisher to generate from the replication conflicts a statement that can be copied to and run on the subscriber(s). Run the generated script (unaltered) on each hub that has at least one conflicting row (the name of the conflicting hubs is listed in the generated script). - Once you've verified that the hub has indeed one or more of the conflicting rows, you can alter the generated script from a select statement into a delete statement: simply comment the line with "--select *" and uncomment the line with "--delete" and run the delete statement on this hub. If a foreign key exists, the delete will fail and you'll have to disable the foreign key before retrying the delete. And enable it after the delete (without checking the existing values!) - After you've deleted all conflicting rows from the tables on all hubs you can go back into Conflict Viewer and click 'Submit winner' on all 'download' conflicts for this table and click 'Remove' on all 'upload' conflicts. .EXAMPLE No example available .TAGS Download insert failed, replication, merge replication, conflict, Upload Insert Failed ********************************************************/-- type 5: upload insert failed - chosen solution: submit winner (= delete from office hub and re-submit from central) -- type 6: download insert failed - chosen solution: submit loser (= delete from office hub and re-submit from central) set nocount on; declare @stmt nvarchar(max); if object_id('tempdb..#conflict_values') is not null drop table #conflict_values; -- Temporary table to hold all key with rowguid values for the affected rows on the publisher. create table #conflict_values ( pubid uniqueidentifier not null, nickname int not null, MSrepl_create_time datetime not null, [values] nvarchar(max) not null ); -- Generate a statement to retrieve literal values from the conflict tables, specific -- for each table. The columns to retrieve are all key columns + the rowguid column. with cteKeyColumns as ( select ix.object_id, ic.column_id from sys.indexes ix inner join sys.index_columns ic on (ic.object_id = ix.object_id and ic.index_id = ix.index_id and ic.is_included_column = 0) -- where ix.name = 'ixTransportLegs' where ix.is_primary_key = 1 ), cteRowGuidColumns as ( select col.object_id, col.column_id from sys.columns col where col.is_rowguidcol = 1 ), cteTypesToLiteral as ( select --ut.system_type_id, --ut.user_type_id, ut.*, case st.name --when 'image' then 'binary' when 'text' then 'text' when 'uniqueidentifier' then 'uniqueidentifier' when 'date' then 'datetime' when 'time' then 'datetime' --when 'datetime2' then --when 'datetimcrlffset' then when 'int' then 'number' when 'tinyint' then 'number' when 'smallint' then 'number' when 'smalldatetime' then 'datetime' when 'real' then 'number' when 'money' then 'number' when 'datetime' then 'datetime' when 'float' then 'number' --when 'sql_variant' then when 'ntext' then 'ntext' when 'bit' then 'number' when 'decimal' then 'number' when 'numeric' then 'number' when 'smallmoney' then 'number' when 'bigint' then 'number' --when 'varbinary' then 'binary' when 'varchar' then 'text' --when 'binary' then 'binary' when 'char' then 'text' when 'timestamp' then 'number' when 'nvarchar' then 'ntext' when 'nchar' then 'ntext' when 'xml' then 'ntext' end as literal_type from sys.types ut left outer join sys.types st on (st.user_type_id = ut.system_type_id) ) select @stmt = stuff(( select N'' + s.crlf + N'insert #conflict_values(pubid, nickname, MSrepl_create_time, [values])' + s.crlf + N'select ci.pubid,' + s.crlf + N' ci.tablenick,' + s.crlf + N' ci.MSrepl_create_time,' + s.crlf + N' stuff((' + s.crlf + N' select '','' + t.txt as [text()]' + s.crlf + N' from (' + stuff(( select s.crlf + N' union all select ' + convert(varchar(36), col.column_id) + ', ' + case ttl.literal_type when 'datetime' then 'isnull(''{ts '''''' + convert(varchar(36), c.' + quotename(col.name) + ', 121) + ''''''}'', ''NULL'')' when 'ntext' then 'isnull(N'''''''' + replace(c.' + quotename(col.name) + ', '''''''', '''''''''''') + '''''''', ''NULL'')' when 'number' then 'isnull(convert(varchar(36), c.' + quotename(col.name) + '), ''NULL'')' when 'text' then 'isnull('''''''' + replace(c.' + quotename(col.name) + ', '''''''', '''''''''''') + '''''''', ''NULL'')' when 'uniqueidentifier' then 'isnull('''''''' + convert(varchar(36), ' + case col.is_rowguidcol when 1 then 'case ci.conflict_type when 5 then tbl.' + quotename(col.name) + ' when 6 then c.' + quotename(col.name) +' end' when 0 then 'c.' + quotename(col.name) end + ') + '''''''', ''NULL'')' end from sys.columns col left outer join cteTypesToLiteral ttl on (ttl.user_type_id = col.user_type_id) where col.object_id = sma.objid and col.column_id in ( select kcn.column_id from cteKeyColumns kcn where kcn.object_id = sma.objid union all select rgc.column_id ) order by col.column_id for xml path(''), type ).value('.','nvarchar(max)'), 1, 24, '') + ' + '' /* '' + ci.origin_datasource + '', conflict_type = '' + convert(varchar(36), ci.conflict_type) + '', reason_code = '' + convert(varchar(36), ci.reason_code) + '' */ ''' + s.crlf + N' ) t (column_id, txt)' + s.crlf + N' order by t.column_id' + s.crlf + N' for xml path(''''), type' + s.crlf + N' ).value(''.'',''nvarchar(max)''), 1, 1, '''')' + s.crlf + N'from dbo.msmerge_Conflicts_info ci' + s.crlf + N' inner join ' + 'dbo.' + quotename(sma.conflict_table) + ' c on (c.' + quotename(col_name(rgc.object_id, rgc.column_id)) + ' = ci.rowguid)' + s.crlf + N' left outer join ' + quotename(object_schema_name(sma.objid)) + '.' + quotename(object_name(sma.objid)) + ' tbl on (' + stuff(( select case col.is_nullable when 0 then ' and tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' = c.' + quotename(col_name(col.object_id, col.column_id)) when 1 then ' and ((tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' is null and c.' + quotename(col_name(col.object_id, col.column_id)) + ' is null) or tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' = c.' + quotename(col_name(col.object_id, col.column_id)) + ')' end as [text()] from sys.columns col where col.object_id = sma.objid and col.column_id in ( select kcn.column_id from cteKeyColumns kcn where kcn.object_id = sma.objid ) order by col.column_id for xml path(''), type ).value('.','nvarchar(max)'), 1, 5, '') + ')' + s.crlf + 'where ci.conflict_type in (5,6);' as [text()] from ( select char(13) + char(10) ) s (crlf) cross join dbo.sysmergearticles sma left outer join cteRowGuidColumns rgc on (rgc.object_id = sma.objid) where exists ( select * from dbo.msmerge_Conflicts_info ci where ci.conflict_type in (5,6) --and ci.reason_code = 2627 -- Primary key violation -- -- = 2601 -- Cannot insert duplicate key row in object ....' -- -- = 3 and ci.tablenick = sma.nickname and ci.pubid = sma.pubid ) for xml path(''), type ).value('.','nvarchar(max)'), 1, 2, ''); --select @stmt as [text()] --for xml path(''), type; if @stmt is not null exec sp_executesql @stmt; --select @stmt; with cteKeyColumns as ( select ix.object_id, ic.column_id from sys.indexes ix inner join sys.index_columns ic on (ic.object_id = ix.object_id and ic.index_id = ix.index_id and ic.is_included_column = 0) -- where ix.name = 'ixTransportLegs' where ix.is_primary_key = 1 ), cteRowGuidColumns as ( select col.object_id, col.column_id from sys.columns col where col.is_rowguidcol = 1 ) select s.crlf + N'--delete t' + s.crlf + 'select t.*' + s.crlf + 'from ' + isnull( sma.destination_owner + '.', '') + quotename(sma.destination_object) + ' t' + s.crlf + ' inner join (' + stuff(( select s.crlf + ' union select ' + v.[values] as [text()] from #conflict_values v where v.pubid = sma.pubid and v.nickname = sma.nickname order by v.MSrepl_create_time for xml path(''), type ).value('.','nvarchar(max)'), 1, 12, '') + s.crlf + ' ) s (' + isnull( stuff(( select ',' + quotename(col.name) as [text()] from sys.columns col where col.object_id = sma.objid and col.column_id in ( select kcn.column_id from cteKeyColumns kcn where kcn.object_id = sma.objid union all select rgc.column_id ) order by col.column_id for xml path(''), type ).value('.','nvarchar(max)'), 1, 1, ''), 'ERROR - NO COLUMNS FOUND') + ') on (' + stuff(( select s.crlf + case col.is_nullable when 0 then ' and s.' + quotename(col.name) + ' = t.' + quotename(col.name) when 1 then ' and ((s.' + quotename(col.name) + ' is null and t.' + quotename(col.name) + ' is null) or s.' + quotename(col.name) + ' = t.' + quotename(col.name) + ')' end as [text()] --s.crlf + ' and s.' + quotename(col.name) + ' = t.' + quotename(col.name) as [text()] from sys.columns col where col.object_id = sma.objid and col.column_id in ( select kcn.column_id from cteKeyColumns kcn where kcn.object_id = sma.objid ) order by col.column_id for xml path(''), type ).value('.','nvarchar(max)'), 1, 7, '') + ')' + s.crlf + 'where not ' + 's.' + quotename(col_name(rgc.object_id, rgc.column_id)) + ' = ' + 't.' + quotename(col_name(rgc.object_id, rgc.column_id)) as [text()] from ( select char(13) + char(10) ) s (crlf) cross join dbo.sysmergearticles sma left outer join cteRowGuidColumns rgc on (rgc.object_id = sma.objid) where exists ( select * from dbo.msmerge_Conflicts_info ci where ci.pubid = sma.pubid and ci.tablenick = sma.nickname and ci.conflict_type in (5,6) ) for xml path(''), type;