Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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




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.



    



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


Learn more about SQL Server tools