One of the challenges in working with Merge Replication is handling conflicts since writes can occur at any node in a topology.  This opens the door for the possibility of the same row being updated and different subscribers between synchronizations.  In an earlier post I demonstrated how to handle conflicts using the business logic handler framework but today I'd 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 by utilizing a location specific identifier column and extending the primary key to this column, filtering, or some combination of the three.  However, sometimes a pesky developer can unknowingly introduce an application change which results in an onslaught of conflicts and having alerts in place is a smart precaution for just this scenario.

To setup a conflict alert we must first identify the Performance Monitor counter SQLServer:Replication Merge Conflicts/sec instance name that we would like to monitor.  In the Add Counters dialog in Performance Monitor we can identify the instance name for the Merge conflicts counter for a publication:

For this example the instance name that I will be monitoring is WS2008R2_1-AdventureWorksLT-TestMergePub1-WS2008R2_1-49.

From here we can use sp_add_alert and 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'

In this example I specified the performance condition to alert me whenever a conflict arises.  This can easily be modified to only raise alerts when a certain threshold is met to suit your needs.  An alert response can also be configured to send an email when the conflict performance condition is met.  I hope this sheds some light on how to create conflict alerts for Merge Replication and as always, if you have any questions feel free to contact me or leave a comment below.