September 30, 2011 at 10:47 am
Hello everyone,
I am looking for some ideas and best options available to implement a disaster recovery solution for our SQL 2008 Merge Replication publisher.
Background:
We have 1 merge replication publisher with 7 remote subscribers. The volume of transactions are very low ~200MB/month. The published db size is 3.2GB. Changes are communicated between the subscribers and the publisher every minute over our VPN network.
The above solution has been working reliability for over two years now. However, if the publisher becomes permanently inaccessible, our current solution is to restore from backup and recreate replication. This means downtime and a mess to capture the data from the subscribers when the publisher was offline. We are considering database mirroring with merge replication so the publisher's data is mirror to another server across the country as part of our disaster recovery requirement. However, I have not had much success in getting it going in our test environment and beginning to question if that is the best solution. Your assistant and guidance is greatly appreciated.
Cheers,
Raymond
October 6, 2011 at 8:40 am
I dont see why restoring the publisher will break merge replication. Restore it to a server with the same name and use keep_replication. Your replication environment should then be up and running again. No need to reinitialize anything.
At least this worked for me in a test environment.
November 22, 2011 at 1:53 am
wanna recommend an article about SQL disaster recovery solution, which helped us out.
hope it can help.
November 22, 2011 at 6:48 am
The only things you need to do in conjunction with standard DR preparation (DB Backups, login scripting etc) is script all publications and subscriptions.
If all you have is a corrupt database then restoring the full backup, diffs and transaction logs with keep replication option will bring you almost in line (depending on merge status and transaction log backup time).
With the loss of the whole server you will need all the scripts to make things a bit easier.
November 22, 2011 at 1:46 pm
Thanks everyone for your input. Since the last post, we ran multiple test scenarios and found out that Replication with Mirroring is a great high availability solution. However, that solution will not be effective in a disaster recovery situation in event of a server failure. Replication with Mirroring is done at database level and not server level. Currently we are looking into Geo-Clustering.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply