Solved

Strategy for restoring merge replication on SQL Server 2008

Posted on 2013-01-15
3
725 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now