Disable Data Sync for SQL Server Merge Replicated Table via Publisher

By:   |   Comments (1)   |   Related: > Replication


SQL Server replication enables synchronization of data between tables across databases on different instances.  SQL Server replication maintenance is a very critical activity and in my case the production environment is spread across multiple data centers in different regions. Unfortunately, there is high latency between the data centers.  We have experienced issues when an engineer deploys a column change for a large table during an update. Because all rows will be affected on the subscribers the update is executed on the publisher. If we wait for the data to synchronize to the subscribers it will take a long time exceeding the maintenance window.  How can I stop the data sync process for SQL Server Merge Replication for a large table updates during our maintenance window?


There are the steps I followed to resolve this problem.

Here is a script to create a sample table and data on the publisher.

CREATE TABLE user_session(user_id BIGINT IDENTITY(1,1) PRIMARY KEY, token_id VARCHAR(128)); 
DECLARE @start BIGINT = 0; 
WHILE(@start <= 1010093) 
    INSERT INTO user_session(token_id) 
    SET @start = @start + 1; 

I then setup Merge replication and here is the Publisher and Subscriber on the local server as shown below.

ssms object explorer

After synchronization, the tables have the same number of rows on the publisher and subscriber.

Below we can see that three triggers were added to the table for replication.  These triggers will be used when Insert, Update or Delete actions are performed on the table. The triggers were automatically created when the table was added to Merge replication.  The triggers are also automatically dropped if the table is removed from replication. In the below image, we can see 3 triggers and based on the name you can see what they are used for.

merge replication triggers

Altering a Table

We will add one column for the migration by using the script below.  I need the new value on all subscribers. By default, the value will be NULL, but in a real world scenario it would probably have a value.

ALTER TABLE user session ADD is_active BIT

After adding the new column, the schema changes were applied to the subscriber.

If we perform the migration on the existing columns of the table, then it will take time to replicate to the subscribers due to latency issues and the size of changes.

Adding Data in Bulk

When the requirement is to add new rows in bulk to the tables, one good approach is to perform the operation on all the of the subscribers as well.

Disabling Replication Triggers

One thought was to disable these replication triggers, so they don't get fired.  Unfortunately, when I try to disable the Replication Trigger on the Publisher, I get an error.

replication merge trigger

So, to stop the synchronization, I altered the triggers and used the RETURN keyword at the start of the code. It is only possible to do this on Publisher. If we try to alter the trigger on the subscriber side it raises an error. The error is because Merge Replication doesn’t allow trigger changes at the subscriber. But modifying a trigger on the publisher will get synchronized on subscribers.

replication merge trigger

Here is the change I made by adding the RETURN keyword in all 3 replication triggers on the publisher.

replication merge trigger

Now to test, we added one new row to the table on the publisher.

INSERT INTO user_session(token_id) 

After 2 minutes (default Polling Period is 60 seconds), it was not synchronized at the Subscriber because the trigger did not execute because of the RETURN statement.

sql query results

I even modified a few rows on the Subscriber, and these changes were not reflected on the Publisher. No need to worry if we have multiple subscribers, because rows will get synchronized by the publisher with cross subscriber synchronization.

UPDATE subscriber_db..user_session 
SET is_active = 1 
WHERE user_id <= 10; 
update data

Once the migration is complete, we need to remove the changes which were done by modifying the replication triggers by just removing RETURN.

merge replication trigger

After modifying some rows, the data is updated on both the publisher and subscriber.

UPDATE user_session 
SET is_active = 1 
WHERE user_id BETWEEN 11 AND 20; 
query results

We can use this approach when data changes need to be released on a large table in Merge Replication and changes are going to affect a large number of rows.  We can stop the data synchronization, manually update the data on the publishers and subscribers and then re-enable the data synchronization.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jignesh Raiyani Jignesh Raiyani is a SQL Server Developer/DBA with experience in design and development of T-SQL procedures and query performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, November 30, 2018 - 2:19:58 PM - Yelender Merugu Back To Top (78377)

Hi Jignesh,

It is really helpful article. Thank you for your efforts to bring it out. 

get free sql tips
agree to terms