By: Ranga Babu | Last Updated: 2016-05-10 | Comments (4) | Replication
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.
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.
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.
- 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
About the author
View all my tips