sql server
Another interesting question came up in a class this week. How can we automatically alert someone based on a conflict in Merge replication? Conflicts are resolved automatically but it would be nice to be notified via email that an update was overridden or at least have it logged. Let’s take a look… Merge conflicts are stored in a table called MSMerge_Conflicts_Info that is a system table in the Publication and the Subscription databases. SSMS has an interactive Conflict Viewer that uses this table to show how conflicts were resolved and provides an option to Submit Loser which turns a losing update into a winning update and a winner into a loser. Being interactive, that can be done on demand. Just right-click the merge publication and choose View Conflicts. But what if you wanted an alert email sent based on a conflict occuring. Well, you can put an INSERT trigger on the MSMerge_Conflicts_Info table that uses RAISERROR to log an informational message to the Windows Application Log (WITH LOG). MSMerge_Conflicts_Info has a column called Reason_text that contains who won and lost. Based on the error code you define, you could then define a SQL Server Agent Alert based on that error and have an email sent using Database Mail. Voila. If you wanted more information about the losing data, you can look at the “MSmerge_conflict_publication_article” table for each article in the publication. SQL Server plugs in the publication and article name in the table name. These are the tables that the Conflict Viewer uses to display the winning and losing data. You can reference these tables in your trigger code. Now in general, I recommend designing conflicts out of the system by careful database design especially for replication. For instance, adding a column to the Primary Key such as a region code or similar will effectively “design away” conflicts. This is the conflict avoidance strategy. But if you do have to deal with conflict resolution, this is one way of keeping up to date with the results. And you can then turn a loser into a winner. Hmmm…this would be good in the NFL playoffs… cheers Brian Advanced Merge Replication Conflict Detection and Resolution: http://technet.microsoft.com/en-us/library/ms151257.aspx
