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


By:   |   Updated: 2016-05-10   |   Comments (4)   |   Related: More > 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.


Last Updated: 2016-05-10


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




Tuesday, January 17, 2017 - 9:56:05 AM - Ranga Back To Top

Hi Harry,

 

We had done this couple of times on our QA and production servers.

 


Tuesday, January 17, 2017 - 8:11:27 AM - Cittyroller Back To Top

 Hi Ranga,

thank you for your article.

Maybe i need this approach in the next days to troubleshoot replication problems faster (because my subscriber at the moment has a poor connection...).

You had done this only one time or it is a "good" testet way to move a subscription to another server?

Kind regards

Harry

 

 


Wednesday, May 11, 2016 - 3:12:45 AM - Ranga Back To Top

Thank you Greg.

 


Tuesday, May 10, 2016 - 3:44:18 PM - Greg Robidoux Back To Top

Hi Ranga,

Welcome to the MSSQLTips.com Team.

-Greg



download


Recommended Reading

Troubleshooting transactional replication latency issues in SQL Server

Add new article to existing publication for SQL Server Transactional Replication

Change Not For Replication Value for SQL Server Identity Columns

Steps to clean up orphaned replication settings in SQL Server

SQL Server Replication Scripts to get Replication Configuration Information





get free sql tips
agree to terms


Learn more about SQL Server tools