Replicating XML Columns

One caveat that’s worth mentioning is replicating the XML data type in SQL Server using Merge Replication.  Sometimes, when replicating a table that contains a column of type XML via a Merge Publication, the XML column data in updated rows sent from Publisher to Subscriber, and vice-versa, arrive at the destination as empty, leading to non-convergence.

If you're experiencing this behavior, verify you have the latest Service Pack applied.  If the problem continues to persist, execute sp_helpmergearticle on the Publisher to retrieve information about the article in question and examine the value of the article property stream_blob_columns.

sp_helpmergearticle

sp_helpmergearticle_result

If the value of stream_blob_columns is set to true then this is the culprit.  When stream_blob_columns is set to true, SQL Server uses data stream optimization when replicating LOBs.  Somewhere in this optimization process things go south leading to non-convergence.  To workaround this, execute sp_changemergearticle, set stream_blob_columns to false, and synchronize your subscriptions.

sp_changemergearticle

 

After setting stream_blob_columns to false, articles containing XML columns should converge correctly in subsequent synchronizations.

Replication Monitor – Friendly Names

You may have noticed when you setup a merge pull subscription using the New Subscription Wizard in SSMS that you weren’t given the opportunity to give your subscription a description.  This is unfortunate because subscription descriptions are displayed in the Friendly Name column in Replication Monitor and give you a convenient way to tag your subscriptions with meaningful names.  You may have also noticed that when you right-click on a merge pull subscription and view it’s properties that the subscription description property is grayed out.

Subscription Properties Capture - Pull Subscription

Executing sp_changemergepullsubscription to change the subscription description to 'Seattle 1' does appear in the Subscription Properties dialog.  But Replication Monitor still displays no Friendly Name.

Subscription Properties Capture - Pull Subscription w description

Replication Monitor - No Friendly Name

Fortunately there is a table MSmerge_subscriptions in the distribution database which contains a row for each subscription in our merge pull topology and is our key to populating the Friendly Name column for our merge pull subscriptions.

-- Use the distribution database

USE distribution

GO


-- Give our merge pull subscription a description

UPDATE MSmerge_subscriptions

SET description = 'Seattle 1'

WHERE subid = 'C281B1EC-3684-42B6-B9B8-AB6DB91F6F18'

GO


MSmerge_subscriptions - w description

Replication Monitor - w Friendly Name

We can now see a Friendly Name for our merge pull subscription in Replication Monitor.  A friendly name is much easier to remember than an obnoxious server name and can save us time when identifying subscribing servers in Replication Monitor.

Showing all posts tagged troubleshoot