Merge Replication Test

The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions. By default, the Merge Agent uploads changes from the Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber.

The speed with which the Merge agent uploads/downloads changes between the Publisher and Subscriber and the count of conflicts it detects in the process influence how efficient the Merge agent is and how quickly data replication occurs. If data replication stalls, you may want to check the performance of the Merge agent to figure out possible reasons for the slowdown. The Merge Replication test enables this analysis. This test monitors the operations of the Merge agent and reports how quickly changes were uploaded and downloaded by the agent and how many conflicts were detected (per second) while merging. These statistics are useful when trying to determine whether/not issues with the Merge agent are causing problems in replication.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the Microsoft SQL server being monitored

Configurable parameters for the test
TEST PERIOD - How often should the test be executed Host – The IP address of the Microsoft SQL server. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Downloaded changes:	Indicates the number of rows per second replicated from the Publisher to the Subscriber.	Rows/Sec	A high value is desired for these measures. If the value of these measures dips consistently over time, it could indicate a slowdown.
Uploaded changes:	Indicates the number of rows per second replicated from the Subscriber to the Publisher.	Rows/Sec
Conflicts:	Indicates the number of conflicts per second occurring during the merge process. .	Conflicts/Sec	Merge replication allows multiple nodes to make autonomous data changes, so situations exist in which a change made at one node may conflict with a change made to the same data at another node. In other situations, the Merge Agent encounters an error such as a constraint violation and cannot propagate a change made at a particular node to another node. Ideally, the rate of such conflicts should be low. A high value or a steady increase in this value indicates that too many conflicts have been detected, but only a few have been resolved. A large number of merge conflicts and delays in their resolution can adversely impact replication. The Merge Agent detects conflicts by using the lineage column of the MSmerge_contents system table; if column-level tracking is enabled for an article, the COLV1 column is also used. These columns contain metadata about when a row or column is inserted or updated, and about which nodes in a merge replication topology made changes to the row or column. As the Merge Agent enumerates changes to be applied during synchronization, it compares the metadata for each row at the Publisher and Subscriber. The Merge Agent uses this metadata to determine if a row or column has changed at more than one node in the topology, which indicates a potential conflict. After a conflict is detected, the Merge Agent launches the conflict resolver specified for the article with a conflict and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table. Conflicts are resolved automatically and immediately by the Merge Agent unless you have chosen interactive conflict resolution for the article.