Undocumented Gotcha in Merge Replication Tracking

In the spirit of Robert Davis's (blog | @SQLSoldier) blog post: Undocumented Gotchas of Transactional Replication, I decided to contribute an undocumented gotcha in Merge Replication to his list.  First, let me start off by saying that if you happen to have a difficult replication scenario that your looking for advice on, Robert Davis is an excellent resource.  He was kind of enough to let me pick his brain at SQL Saturday #92 in Portland, Oregon about a topic that would send most people running for the door.  Not only did he offer a solution to my problem, he offered 3 different solutions, which was very nice of him.

Gotcha

One gotcha that you need to watch out for in Merge Replication is changing an existing article's tracking type from row-level tracking to column-level tracking, and when using Server subscription types.  When an article is added to a Merge publication, by default it is added using row-level tracking.  Sometimes, in an effort to reduce conflicts, one might decide to change an article's tracking type from row-level tracking to column-level tracking.  However, after doing so, one might realize that even though column-level tracking has been enabled, that conflicts still occur even though updates were made to different columns for a row at different nodes in the topology.  Further complicating the issue is the fact that the article tracking property will actually indicate that column-level tracking is enabled when checked via the Article Properties dialog or via the stored procedure sp_helpmergearticle.  It can be a little confusing.

Example

For example, lets say we have a Merge publication P1 and subscriptions S1 and S2 which are Server subscription types.  If P1 has an article Customer which is set to row-level tracking and we then change it to column-level tracking using the Article Properties dialog or via the stored procedure sp_changemergearticle and reinitialize subscriptions, the following updates will still generate a conflict after syncing both subscriptions:

S1:

UPDATE Customer
SET FirstName = 'Robert'
WHERE CustomerID = 10

Sync

S2:

UPDATE Customer
SET LastName = 'Davis'
WHERE CustomerID = 10

Sync

Solution

The proper way to handle this is to set the article tracking property to column-level from the very start.  Adding articles and setting column-level tracking using the Publication Wizard or sp_addmergearticle when creating the publication will yield the correct results, avoiding conflicts when updates are made to different columns for the same row.  Unfortunately, if the article has already been added to the publication, it will need to be dropped and added back to the publication with column-level tracking enabled.

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

Replication Bookmarks

I started a list of the resources I frequent the most in the MSDN Library SQL Server Books Online - Replication, Technical Articles and Whitepapers, Blogs and Websites, Videos, Forums, and Books - Replication Bookmarks.

I find myself visiting these pages often and thought this could be a useful reference when searching for quality content on SQL Server Replication.  The list is subject to change as I intend to add to it over time.  One list I plan to grow is Blogs and Websites as I'm aware that there have been a number of blog posts written on the topic of SQL Server Replication.  If you're interested in being added to my replication bookmarks, feel free to get in touch, I'm always looking for something to read.