Solved

Strategy for restoring merge replication on SQL Server 2008

Posted on 2013-01-15
3
745 Views
Last Modified: 2013-03-18
We have merge replication setup with a publisher, a distributor and 28 subscribers. Now we need to migrate the publisher and distributor servers to another server running on VMWare environment over the private Cloud.

There are a few challenges for us for this migration exercise -

1) Our current data center has very limited access to the cloud environment which only allows us to do remote desktop or web portal.

2) Limited bandwidth between our Data Center and Cloud which is only 40 MB.

3) After restore the publisher and distributor, we couldn't afford to reinitialized all the 28 subscribers due to limited bandwidth of our subscriber connection which is only 2 MB.

4) Our down time is only 8 hours and we have to complete everything within this window.

I have experience in migrating other database servers to the cloud but those database servers did not have any replications. This is my first time to migrate the database servers with merge replication to another server.

Kindly advise me what should be the steps to restore the publisher and distributor without the need of reinitializing the replications.

I have both push and pull merge replications and subscribers are using the Microsoft Synchronization Manager to sync the pull replication.

Thank you in advance.
0
Comment
Question by:CalvinWin
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 38782781
You could try to build a "Stand By" server on the cloud using log shipping including publisher/distribution dbs hoewever with limmited bandwith that may be impossible to achive - you need to try it though.

http://msdn.microsoft.com/en-us/library/ms151224.aspx


This way you could switch during downtime from current live to cloud stand by server however....what would be the rollback in case your bandwith is not enough for the 28 subscribers to pull/refresh from your new merge replication server? On 40MB it may be a challange and again - in my opinion only testing could give you the answer because traffic in this case is very much related to your replication objects.
0
 
LVL 1

Assisted Solution

by:CalvinWin
CalvinWin earned 0 total points
ID: 38981459
I successfully completed the above job as below steps -

1) Wait till all outlets are closed and stop their synchronization and ensure that no synchronization during the downtime.
2) Backup Master, msdb and all replicated databases on the publisher
3) Backup service master key of the publisher
4) Copy all those backups to the cloud's to-be-publisher server
5) Backup Master, msdb and distribution databases on the distributor
6) Backup service master key of the distributor
7) Copy all those backups to the cloud's to-be-distributor server
8) Disjoint the current publisher and distributor from the domain and unplugged from network.
9) Rename cloud's to-be-publisher server name to original publisher server name
10) Rename cloud's to-be-distributor to original distributor server name
11) Restore the publisher
       - Restore Master DB
       - Restore Service Master Key
       - Restore MSDB
       - Restore replicated databases (WITH REPLICATION OPTION)
12) Restore the Distributor
       - Restore Master DB
       - Restore Service Master Key
       - Restore MSDB
       - Restore Distribution database (WITH REPLICATION OPTION)
       - Define shared permission for REPL folder on distributor
13) Check replication and start the synchronization
14) Test some data and ensure the synchronization is working as intended.
15) Just wait for the next day morning to open the outlet to start operation.

For my experience is we need to test until we have enough confidence level to do the actual migration and must also plan for fall back and test the fall back plan as well.

I did test few rounds and till the whole process is smooth.
0
 
LVL 1

Author Closing Comment

by:CalvinWin
ID: 38995012
Thank you @lochan for the Log shipping approach which is definitely better for the large database migration.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question