Move SQL Server Merge Replication Subscriber to new Server without Reconfiguring Replication

By: Ranga Babu   |   Updated: 2016-05-10   |   Comments (5)   |   Related: > Replication

Problem

We had a requirement to move the Subscriber for a Merge publication to a new SQL Server. We could have dropped the old subscriber and created a new subscriber, but in this tip we look at another option.

Solution

In this tip we will look at how this can be done by restoring a full backup of the existing subscriber onto the new server and altering a few replication tables without having to drop the old subscription and create a new subscription.

Scenario

We have two instances SERV1 and SERV2 with publication database db1 on SERV1 and subscriber database db1 on SERV2. Now we need to move the subscriber from SERV2 to SERV3 without dropping the existing subscription and creating a new subscription on the new server. Here are some of the details for the merge publication.

• Publication name : MergeReplication1
• Publication database: db1
• Subscriber database : db1
• Subscription type: push

Steps to move Merge Replication to a new SQL Server

Stop synchronizing the publication by using Replication Monitor.

Create a full SQL Server database backup on existing Subscriber

Take a full backup of the subscriber database db1 on SERV2.

BACKUP DATABASE db1 TO DISK = N'C:\db1.bak' WITH NOFORMAT, NOINIT,  
NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Copy SQL Server backup and restore on new Subscriber

Copy the backup file from SERV2 to SERV3 and restore it with KEEP_REPLICATION.

RESTORE DATABASE db1 FROM DISK = N'C:\db1.bak' WITH FILE = 1, KEEP_REPLICATION, NOUNLOAD, STATS = 10
GO

Update replication tables on SQL Server Publisher database

Now we need to alter the server name in a few replication tables. Go to the Publisher database and change the server name in sysmergesubscriptions with the appropriate values. You can get the pubid from sysmergepublications.

-- run in the Publication data

SELECT * FROM sysmergepublications WHERE name = 'MergeReplication1'

UPDATE sysmergesubscriptions 
SET subscriber_server = 'SERV3' 
WHERE subscriber_server = 'SERV2' 
AND DB_NAME = 'db1'
AND pubid = '4936E2B1-4B35-4658-890A-AE7F05C90AEC'

Update the SQL Server replication tables on new Subscriber database

Repeat the above step in the new subscriber database on server SERV3.

Update SQL Server replication tables on Distribution database

Change the server name in MSmerge_agents and MSmerge_subscriptions in the distribution database.

SELECT * FROM MSmerge_agents

UPDATE MSmerge_agents 
SET subscriber_name = 'SERV3' 
WHERE subscriber_db = 'db1' 
AND publication = 'MergeReplication1'
AND subscriber_name = 'SERV2'


SELECT * FROM MSmerge_subscriptions 

UPDATE MSmerge_subscriptions 
SET subscriber = 'SERV3' 
WHERE subscriber = 'SERV2'
AND publisher_db = 'db1'

Modify the SQL Server Agent Job

Now go to merge agent job properties then steps, double click on run agent step and replace -Subscriber [SERV2] with -Subscriber [SERV3].

Re-enable Synchronizing for the SQL Server Publication

Now start synchronizing again from replication monitor and verify that changes are being replicated.

Next Steps

• Please test in a QA or development environment before doing in production. This way you will know the steps to take when you run on your production servers.
• Check out more Replication Tips.

About the author

Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

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

View all my tips