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.