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.