By: Jignesh Raiyani | Comments (1) | Related: > 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.
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.
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.
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.
Here is the change I made by adding the RETURN keyword in all 3
replication triggers on the publisher.
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.
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;
Once the migration is complete, we need to remove the changes which were done
by modifying the replication triggers by just removing RETURN.
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;
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
- Be sure to test this process in a Test or QA environment prior to production.
- Check out these other SQL Server Replication tips.
About the author
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
