January 16, 2015 at 8:12 am
We have a SQL Server 2008 database that is replicated to Australia. The primary data tables are merge replicated. The replication cycle is 1 hour. We have a situation where a user inserted a row into a table, deleted the row, then reinserted it within an hour. The merge replication failed because of a primary key uniqueness violation on that table. We have been told that merge replication cannot handle this scenario.
From what little I can get from the MS literature, the replication triggers should have handled this. The delete trigger should have removed the insert entry from the replication tables and the second insert should have removed the entry from the delete replication tables, leaving the replication processing to only do the second insert. Can anyone clarify what is really supposed to happen here or if this is in fact a limitation of merge replication?
January 16, 2015 at 1:08 pm
Merge replication uses GUID columns to keep track of unique rows.
So the delete is using the GUID value in the where clause.
The dependency on the GUID also makes it so that the system does not recognize the relationship between the two insert statements since they have different GUIDs
Also the transactions are being applied in batches, so it is likely that the second insert is being applied before the delete or in the same batch.
You can change the parameters of the merge agent and add:
-UploadGenerationsPerBatch 1 -UploadReadChangesPerBatch 1 -UploadWriteChangesPerBatch 1
and restart the merge agent
That might help.
Once the issue is cleared up you can revert to the default.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply