SQL Server – How to Delete Duplicate Rows in Merge Replication

The biggest time saving option is to generate the snapshot and compress it with your favorite program, like 7-Zip, copy the compressed snapshot to the subscriber using file-copy or FTP, and then apply it locally using the -AltSnapshotFolder Merge Agent parameter. The existing Product database can be left in place while the snapshot is being copied over and client applications can operate normally.

The way this works is open the SQL Agent job for the Subscriber you wish to deploy the snapshot to and get the Run Agent. command. It looks something like this:

-Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1

Then, grab the snapshot files from the publisher and compress it. For example, on my server I compress the snapshot folder found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc. I then copy the snapshot folder to the subscriber using file-copy or FTP and decompress it in a folder called C:\TEMP\unc.

So looking at this on the Subscriber I would see this for the snapshot folder:

C:\TEMP\unc\PACIFIC_TESTDB2_TESTMERGEPUB1\20130207200123

Then from the command line on the Subscriber, run this:

c:\Program Files\Microsoft sql server\100\com\replmerg.exe -Publisher [PACIFIC] -PublisherDB [TestDB2] -Publication [TestMergePub1] -Subscriber [PACIFIC] -SubscriberDB [TestSubDB2] -Distributor [PACIFIC] -DistributorSecurityMode 1 -AltSnapshotFolder C:\TEMP

This will apply the snapshot locally and will be significantly faster then applying it over the wire. Downtime at the Subscriber will be minimal.

Primary key conflicts/violations will not be resolved automatically. You will need to manually delete one of the offending rows to get the primary key conflicts from reoccurring on every synchronization.

If you expect primary key conflicts to occur regularly then you may want to consider doing one of the following:

1. Horizontally partition your data by adding a location specific identifier column and extend the primary key(s) to include this column. Then have subscribers only insert rows belonging to their partition.

2. Set the Merge article property @compensate_for_errors to true which sends a compensating change to the source replica to undo the failed change when a primary key conflict occurs.