One of the challenges in working with Merge Replication is handling conflicts since writes can occur at any node in a topology. In an earlier post I demonstrated how to handle conflicts using the business logic handler framework - now I would like to show how to create Merge Replication conflict alerts based on the Performance Monitor counter SQLServer:Replication Merge Conflicts/sec.
Ideally proper planning would be done in an application to minimize the chances of conflicts occurring, whether that is achieved through column-level tracking, partitioning the writes, filtering, or some combination. However sometimes application changes can introduce conflicts and having alerts in place is a smart precaution to take.
To setup a conflict alert - identify the Performance Monitor counter SQLServer:Replication Merge Conflicts/sec instance name to monitor. In the Add Counters dialog in Performance Monitor the instance name can be identified for the Merge conflicts counter for a publication:
For this example the instance name that I will monitori is WS2008R2_1-AdventureWorksLT-TestMergePub1-WS2008R2_1-49.
From here sp_add_alert can be used to specify a merge conflict performance condition using our instance name to alert us when a conflict arises. This can be done with the following bit of T-SQL:
USE msdb GO EXEC sp_add_alert @name=N'Merge Conflict Alert', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=0, @category_name=N'[Uncategorized]', @performance_condition=N'SQLServer:Replication Merge|Conflicts/sec|WS2008R2_1-AdventureWorksLT-TestMergePub1-WS2008R2_1-49|>|0'
This can also be modified to raise alerts only when a certain threshold is met to suit your needs. An alert response can be set to send an email when the conflict performance condition is met which would give a much needed heads-up when things start to go awry. I hope this provides a technique to detect precisely when conflicts occur and a head start on tracking down the culprit.