New Merge subscriber fails with sp_MSsetconflicttable
I’ve not seen this problem for year, but it came up again today. We were following the steps to setup a Merge subscriber using Backup\Restore from the Publisher. The database was very large, no filtering, two subscribers, each with complete match set of data from the publisher. Backup\Restore works great, saves time pushing down individual table snapshot to subscribers.
However, today we encountered Merge Agent error when attempting to add the subscribers following the subscriber database restore from publisher backup.
{call sp_MSsetconflicttable (N’Customer’, N’MSmerge_conflict_MergeCustomer_Customer’, N’SQL2K8R2′, N’AdventureWorksLT’, N’MergeCustomer’)}
Replication Monitor or SSMS shows same error:
SQL Profiler trace may contain additional errors
Error: 102, Severity: 15, State: 1
Incorrect syntax near ‘CustomerID’.
Looking closer at “customer” table shown in this repro, the restored database was missing rowguid column added when publication was created used by Merge replication to uniquely identify each published row.
Publisher w/RowGuid
Subscriber missing rowguid
From our documentation, all steps we followed looked correct.
To manually initialize a push subscription to a merge publication
- Ensure that the schema and data exist on the subscription database. This can be done by restoring a backup of the publication database at the Subscriber.
- At the Publisher on the publication database, execute sp_addmergesubscription. Specify the name of the database at the Subscriber containing the published data for @subscriber_db, a value of push for @subscription_type, and a value of none for @sync_type.
- At the Publisher on the publication database, execute sp_addmergepushsubscription_agent. For more information, see Create a Push Subscription.
- Start the Merge Agent to transfer replication objects and download the latest changes from the Publisher. For more information, see Synchronize a Push Subscription.
We even simplified the steps by using SQL Management Studio for steps 2 and 3, selecting “Do Not Initialize” option when adding the subscriber as it already has schema and data from the publisher restored database.
Keep_Replication
Documentation say to NOT use “Keep_Replication” during restore, however, for this customer, without Keep_Replicaiton, RowGuid column was stripped from tables during the restore. Without RowGuid column, subscriber data no longer matches publisher data and internal procedures such as sp_MSsetconflicttable will fail.
However if restored with Keep_Replication, all Merge metadata remains.
RESTORE DATABASE . . . KEEP_REPLICATION
Optional : Temporarily Enable\Disable Publishing on Subscriber
If your Merge Subscriber has no other Replication configured, you’ll need to temporarily “ENABLE Publishing and Distribution”. Simply Right-Click the Replication folder, select Enable, then complete with all default options.
After setting Merge Publish = False, righ-click Replication folder and “Disable Publishing and Distribution” if no other Replication is running on this subscriber.
To remove “publisher” setting following the restore with Keep_Replication, we executed this command on the Merge Subscriber disabling the subscribers as a Merge Publisher but keeping all metadata required for Merge Subscriber.
use master
GO
exec sp_replicationdboption
@dbname = N'MergeSubscriber',
@optname = N'merge publish', @value = N'false'
GO
Once restored, the subscriber database tables contain publishers matching RowGuid column. Merge Agents runs, downloads metadata allowing synchronization to continue. New change since backup was taken are automatically downloaded during the initialize synchronization.
Snapshot Agent
Note: If your publishing a large database, start Snapshot Agent soon after the Publication is created. The Snapshot Agent is required and exports out all objects required for the Merge Subscriber even if Merge Subscriber is setup via Backup\Restore. When adding subscriber using “no initialize” only the metadata, not the exported table data will be applied to the subscriber.
Chris Skorlinski
SQL Server Escalation Services
