Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Disable Data Sync for SQL Server Merge Replicated Table via Publisher


By:   |   Last Updated: 2018-11-30   |   Comments (1)   |   Related Tips: More > Replication

Problem

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?

Solution

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) 
BEGIN 
    INSERT INTO user_session(token_id) 
    SELECT NEWID(); 
    SET @start = @start + 1; 
END 

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) 
SELECT NEWID(); 

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


Last Updated: 2018-11-30


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Hi Jignesh,

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


Learn more about SQL Server tools