Conflicts will happen in Merge Replication. There is no error on synchronization so we need to know when this occurs, before users complain! You can continually refresh the conflict viewer all day if this is your thing 🙂 or we find something slightly more sophisticated. So, I have used 2 methods:
(1) simply check the table “msmerge_conflicts_info” using a select statement. If there are records in the table send an email to the support team using sp_send_dbmail. This can easily be scripted into a regularly-running job.
(2) create a custom alert. This is not as daunting as it seems. Here is one below so you can see which counters to use…
The response will be an email to the operator defined for the alert – just have an operator for the support team. Now there are drop-downs in this interface there’s no problem creating this alert.
Suppose we have the publisher adds a record with a PK = ‘1’ and a subscriber also adds a record with a PK = ‘1’ and then they synchronize.
As far as merge is concerned these are different rows because they have different rowguid values.
The synchronization tells us there is an issue. No error recorded but we can see something is wrong:
So next we check the conflict viewer and see more info there;
You have 3 options in the conflict viewer;
Which one would you use to fix the issue? Each one removes the conflict from the conflict monitor. However – the next synchronization will simply recreate the conflict! A PK-PK conflict like this cannot be solved using these tools. You need to either update the PK value on one node or delete one of the rows, and then sync up. If there are loads of FK records hanging off this record this can become a huge pain as you’ll need to tackle these first. In fact the longer you leave it the bigger the problem becomes. So – keep an eye on the conflicts and resolve them ASAP. Alternatively add another column and use a composite PK to avoid the conflict in the first place :).