Solved

Strategy for restoring merge replication on SQL Server 2008

Posted on 2013-01-15
3
776 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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