?
Solved

Strategy for restoring merge replication on SQL Server 2008

Posted on 2013-01-15
3
Medium Priority
?
814 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 40

Accepted Solution

by:
lcohan earned 1500 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 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