SQL Server Merge Replication: Update existing merge articles in bulk

Whenever you start to scale things up with SQL Server Merge Replication automation becomes essential.

This becomes very apparent when there are many subscribers connected to the publisher or when the number of articles that make up your publication is large.

As part of this blog post, I am going to demonstrate how to speed up the process of changing multiple merge replication articles at the same time by way of a SQL script.

Updating articles individually

As expected, whenever it comes time to automate something with SQL Server Merge Replication, SQL is the tool that is used to get the job done.

Let’s start off by looking at how we can update the settings of an article individually via SQL before we progress to updating articles in bulk.

sp_changemergearticle

There is a handy replication stored procedure called sp_changemergearticle which can be used to change the settings of an existing Merge Replication article programmatically.

Below is an example of how to execute sp_changemergearticle.

EXEC sp_changemergearticle 
    @publication               = N'MyPublication',
    @article                   = N'DimProduct',
    @property                  = N'stream_blob_columns',
    @value                     = N'true',
    @force_invalidate_snapshot = 0,
    @force_reinit_subscription = 0;

In the above example, the publication and article to update are specified, as well as the name of the article property and the value to which that property should be set to.

Lastly, the options to invalidate the snapshot and reinitialise any associated subscriptions are configured. These can be set to either 1 or o to turn the settings on or off respectively.

Warnings

Before executing the sp_changemergearticle stored procedure, please pay attention to the warnings below.

Snapshot invalidation

Generally speaking, we don’t want to invalidate the snapshot as this will prevent us from being able to add additional subscribers to the publication until a new snapshot is generated.

Subscriber reinitialisation

Additionally, we do not want to reinitialise subscriptions unless absolutely necessary, as this will necessitate the generation of a new snapshot and can potentially result in the loss of data. There may be data on the subscriber that hasn’t been uploaded to the publisher yet. If this data isn’t configured to be uploaded prior to reinitialisation, or the data fails to upload, it can be a difficult and drawn-out process to recover the data.

Updating articles in bulk

If you want to change merge articles in bulk, a SQL script will be needed to automate the process.

The script will ultimately use the same sp_changemergearticle stored procedure covered in the previous sections to update the article settings.

The script

Below is a script that retrieves a list of articles for a specified publication, then iterates through each of them and updates the identity range settings.

USE [distribution] -- CHANGE THIS TO DISTRIBUTION DATABASE NAME.
 
-- Set the publication name.
DECLARE @publication AS SYSNAME;
SET     @publication = N'MyPublication'; -- CHANGE THIS TO PUBLICATION NAME.
 
-- Create a temp table containing a list of article names and row numbers.
SELECT DISTINCT article, ROW_NUMBER() OVER(ORDER BY article ASC) AS row 
INTO  #TempArticleListTable
FROM  MSmerge_identity_range_allocations
WHERE publication = @publication;
 
-- Get the row count / number of articles in the publication from the temp table.
DECLARE @rowCount INT;
SET     @rowCount = (SELECT COUNT(row) FROM #TempArticleListTable);
 
-- Print number of articles info.
PRINT CHAR(13) + 'Number of Articles: ' + CAST(@rowCount AS VARCHAR) +
CHAR(13) + CHAR(13) + 'List of updated Articles:-' + CHAR(13);
 
USE [AdventureWorks] -- CHANGE THIS TO PUBLICATION DATABASE NAME.
 
-- Declare and initialise an iterator.
DECLARE @i INT;
SET     @i = 1;
 
DECLARE @article AS SYSNAME;
 
-- Loop through the rows of the temp table.
WHILE (@i <= @rowCount)
BEGIN
    -- Get the article name from the temp table.
    SELECT @article = article FROM #TempArticleListTable WHERE row = @i;
 
    -- Update the Publisher identity range.
    EXEC sp_changemergearticle 
        @publication               = @publication,
        @article                   = @article, 
        @property                  = N'pub_identity_range', 
        @value                     = 100000,
        @force_invalidate_snapshot = 0,
        @force_reinit_subscription = 0;
   
    -- Update the Subscriber identity range.
    EXEC sp_changemergearticle 
        @publication               = @publication,
        @article                   = @article, 
        @property                  = N'identity_range', 
        @value                     = 100000,
        @force_invalidate_snapshot = 0,
        @force_reinit_subscription = 0;
 
    -- Print the name of article that has just been updated.
    PRINT @article;
        
    -- Increment the iterator.
    SET @i = @i  + 1;
END

The above script first of all switches to the context of the distribution database.

Note that you must update the name of the distribution database before running the script if you happen to have named it something different to the standard ‘distribution’ name.

The publication name is then configured.

Note that you must update the publication name to match the name of the publication containing the articles that you wish to update.

Next, the article name and row number for every article assigned to the specified publication are selected from the MSmerge_identity_range_allocations table into a temporary table.

The number of rows contained in the temporary table is counted and is printed on the screen.

The script then switches context to the database containing the merge articles.

Note that you must update the publication database name to match the name of your replicated database.

A while loop iterates through the list of articles in the temporary table.

For every loop iteration, the name of the current article is selected from the temporary table.

The sp_changemergearticle stored procedure is then called twice; firstly to update the publisher identity range setting and secondly to update the subscriber identity range setting.

The name of the article which has just been updated is printed to the screen and the iterator variable is incremented prior to continuing to the next loop iteration.

The script will complete after iterating through all of the articles contained in the temporary table.

After running the script, all of the articles in your publication should now have new identity range values assigned to them, as per the values shown in the script.

Scenarios

The script covered in the previous section provides a solution to a real-world scenario.

The default identity range values assigned to merge articles are quite low (10,000 IDs for the publisher range and 1,000 IDs for the subscriber range). This means that if a subscriber is offline for a relatively short period of time, the client will potentially run out of identity values to use and will therefore be unable to store additional records until they are back online and can request a new range of identity values from the publisher.

Increasing the identity ranges allows subscribers to remain offline for a longer period of time until the connection or server issue is resolved.

There are many other scenarios where updating merge articles in bulk could be useful. The Microsoft Docs provide a full list of the possible merge article properties that you can update.

In terms of potential improvements and other use-cases, the script from the previous section could be refactored into a reusable stored procedure in its own right. The various settings such as the database and publication names, as well as the property name and value could be passed as arguments to the stored procedure to make it a general-purpose solution.

Summary

In this blog post, I have demonstrated how to update merge articles in bulk using a SQL script.

The scenario I presented provides a real-world example for updating many articles at the same time to resolve limitations in an offline subscriber scenario.

In cases where your database contains hundreds of articles, you can imagine how much time could be saved by automating the process of updating them in bulk.

I hope you found the information presented here useful and that it saves you a lot of time compared to manually administering changes to multiple merge replication articles.