Counting Merge Replication Article Changes

One of the cool things about Merge Replication is that it has the capability of revealing very interesting information regarding applications and their associated statements ran against published articles.  We can learn quite a bit about an application just by the sheer number of data changes that are replicated as a result of the statements it executes.  This can reveal not only which tables an application changes the most, but may even be useful in identifying flaws in application statements when the amount of data changes being replicated is more than anticipated.

Merge Replication tracks data changes through triggers and system tables in the publication and subscription databases.  The replication system tables contain metadata that indicates which data changes should be propagated.  One of Merge Replication system tables, MSmerge_genhistory, contains one row for each generation (collection of changes) that is delivered to a Publisher or Subscriber.  MSmerge_genhistory contains a column changecount, which can be used to determine which table articles contain the most data changes.  So to get an idea for which articles contain the most data changes within the retention period, we can do something like this:

USE MyPublicationDB
GO
 
SELECT
name AS [Object Name],
SUM(changecount) as [Change Count]
FROM dbo.MSmerge_genhistory WITH (NOLOCK)
INNER JOIN dbo.sysmergearticles  WITH (NOLOCK) ON art_nick = nickname
WHERE genstatus = 1 OR genstatus = 2
GROUP BY name
ORDER BY [Change Count] DESC

In this scenario, the articles in my test Merge topology that have been changed since my retention period are Customer, SalesOrderDetail, Address, Product, and SalesOrderHeader,  which have had 847, 542, 450, 295, and 32 data changes respectively.  If you find yourself wondering which articles in your Merge topology are exhibiting the most data changes, the changecount column in MSmerge_genhistory is what you're looking for.

Merge Article Count PivotTable And Chart

Merge Business Logic Handler Conflict Resolver

Introduction

This example demonstrates how to implement a business logic handler for a Merge article to perform custom resolution of conflicting update data changes that occur at Publisher and Subscribers.

Building the Sample

This sample was built using Visual Studio 2010.

Description

With Merge Replication you can utilize the business logic handler framework to write a managed code assembly that gets executed during the merge synchronization process.  The assembly can respond to a variety of replication events including data changes, conflicts, and errors.

For this example I will demonstrate how to implement a business logic handler for a Merge article that handles update conflicts.  In order to do this, we will override the UpdatesConflictHandler and make use of the ADO.NET data sets that are provided by the merge process to not only mimic, but extend the functionality included in the Microsoft SQL Server Priority Column Resolver.

The Microsoft SQL Server Priority Column Resolver, when used, uses a column to determine the conflict winner.  Basically, whichever column in the conflicting rows at Publisher or Subscriber that has the highest value, wins the conflict.  This is limited in the fact that you can only incorporate one column into the equation.  This example will extend this functionality to incorporate two columns by making use of the business logic handler framework.

Code Snippet

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
 
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
 
namespace BLH_CustomConflictResolver
{
    public class SalesOrderHeaderBusinessLogicHandler : Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
    {
        // Variables to hold server names.
        private string publisherName;
        private string subscriberName;
 
        public SalesOrderHeaderBusinessLogicHandler()
        {
        }
 
        // Implement the Initialize method to get publication
        // and subscription information.
        public override void Initialize(string publisher, string subscriber, string distributor,
            string publisherDB, string subscriberDB, string articleName)
        {
            // Set the Publisher and Subscriber names
            publisherName = publisher;
            subscriberName = subscriber;
        }
 
        // Declare what types of row changes, conflicts, or errors to handle
        override public ChangeStates HandledChangeStates
        {
            get
            {
                // We are handling update conflicts for this example
                return ChangeStates.UpdateConflicts;
            }
        }
 
        // This handler mimics the Microsoft SQL Server Priority Column Resolver but extends its functionality to incorporate 2 columns
        // in the resolution process.
        public override ActionOnUpdateConflict UpdateConflictsHandler(DataSet publisherDataSet, DataSet subscriberDataSet, ref DataSet customDataSet,
                ref ConflictLogType conflictLogType, ref string customConflictMessage, ref int historyLogLevel, ref string historyLogMessage)
        {
            // Priority column #1
            int publisherRevisionNumber = Int32.Parse(publisherDataSet.Tables[0].Rows[0]["RevisionNumber"].ToString());
            int subscriberRevisionNumber = Int32.Parse(subscriberDataSet.Tables[0].Rows[0]["RevisionNumber"].ToString());
 
            // Priority column #2
            DateTime publisherModifiedDate = DateTime.Parse(publisherDataSet.Tables[0].Rows[0]["ModifiedDate"].ToString());
            DateTime subscriberModifiedDate = DateTime.Parse(subscriberDataSet.Tables[0].Rows[0]["ModifiedDate"].ToString());
 
            // Check priority for column #1
            if (publisherRevisionNumber > subscriberRevisionNumber)
            {
                customDataSet = publisherDataSet.Copy();
            }
            else if (subscriberRevisionNumber > publisherRevisionNumber)
            {
                customDataSet = subscriberDataSet.Copy();
            }
            else if (publisherRevisionNumber == subscriberRevisionNumber)
            {
                // Priority column #1 for publisher and subscriber are equal
                // Check priority for column #2
                if (publisherModifiedDate > subscriberModifiedDate)
                {
                    customDataSet = publisherDataSet.Copy();
                }
                else
                {
                    customDataSet = subscriberDataSet.Copy();
                }
            }
 
            return ActionOnUpdateConflict.AcceptCustomConflictData;
        }
    }
}

Downloading the Sample

This sample can be downloaded from the MSDN Code Gallery.

More Information

For more information on executing business logic during merge synchronization see:

Showing all posts tagged internals