Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Strategy for restoring merge replication on SQL Server 2008

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 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