October 20, 2009 at 9:31 am
Hi all,
I read alot of opinion on good DR strategy - but I find that is the easy part - just a case of choose your technology and use some common sense. The bit I'm trying to get my head around is when you get to the stage of going back to the old server once it/or the network connecting to it are back up?
How do you all go about rolling back to Server A after a period of time of using the Server B Recovery box?
Shark
October 20, 2009 at 1:58 pm
Do you mean how do you get the new data in "B" back to "A" after "A" is back online ? I suppose that depends on how "A" and "B" are configured. Maybe you need a more detailed, specific question (not that I will know the answer.)
October 20, 2009 at 3:08 pm
Shark Energy (10/20/2009)
How do you all go about rolling back to Server A after a period of time of using the Server B Recovery box?
Depends on what High Availability technology you're using.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2009 at 4:23 pm
Ok lets go with 2 scenarios -
1) Transactional Replication is used to keep B up to date. The line between our front end and A goes down so we start using B. A comes back up. What is the best method to resync.
2) Log shipping set up to B. B (as the warm standby) gets activated. A comes back. What happens with the new data on B?
I did wonder in another topic if merge replication is a good solution but nowhere seems to list it as a good DR solution. I'm thinking A is always merging with B, so whichever becomes the front end, when the other is up it gets its data merged....?
October 20, 2009 at 5:22 pm
Regarding your second point read
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm
MJ
October 21, 2009 at 4:47 am
Thanks for the link. I've written Log Shipping out. Gonna go with Transactional or Merge replication.
Can anyone think of a reason to NOT use Merge?
And how would you resync if using transactional? I have a data examiner utility that can do this but would take ages and need to be carefully managed. Backup and restore would be awkward due to replication in place to other servers.
October 21, 2009 at 4:47 am
Regarding #1, Server A becomes the backup once it becomes available again. Don't try to transfer. It's too much work. Just re-snapshot the database(s) in question and restart Replication using B as the publisher and A as the subscriber.
October 21, 2009 at 4:51 am
BTW, have you looked into Data Mirroring? That might stand you in better stead.
What about passive / active clustering with RAID 5 or RAID 10? That is probably the easiest solution to "rollback" from. Use a SAN and you don't have to worry about migrating the data because the SAN switches between the servers when one server goes down.
The caveat to that is the data redundency problem. Which is why you'd need RAID 5 minimum for the hard drives. RAID 10 is probably bad for the data files on an OLTP, but works wonders for transaction logs I'm told.
Your HA solution depends on what types of databases you have too. OLTP. OLAP. Staging DBs. That sort of thing.
October 21, 2009 at 5:44 am
Why did you rule out Log Shipping?
Replication isn't really the tool to use here... even with Merge replication, you still have the concept of a Publisher, Distributor and a Subscriber.
In a DR scenario, what are you going to do if the Publisher and/or Distributor fails (these are often the same server)?
How are you going to keep the metadata (stored procs, functions, indexes etc) in sync? Every time you add a new table, you will need to add it to the publication.
October 21, 2009 at 5:47 am
I have to agree with Ian here. Replication is not meant as a disaster recovery tool. It's meant to keep data synchronized between separate offices such as bookstores or airlines.
Log Shipping was the original DR tool for non-clustered servers.
October 21, 2009 at 5:48 am
The main aim for us with this is protecting our VPN into the building. If the line goes down (and it has before) we need the external server to take over. So once the line is up we need to go back to the internal faster and more manageable server.
EDIT: Didn't see the previous 2 comments.
1) Ruled out log shipping due to the overhead of needing a "monitor" server. Not Totally written out tho...
2) Replication may not have been designed for DR, but it IS listed as an option in official microsoft documentation (but only Transactional or peer to peer in 2K5).
3) With our replication situation we have the publishers and distributor all on one server. We haven't added any articles for a long while and wouldn't expect to.
As mentioned the main aim is to cover our app not being able to see Server A, as opposed to Server A crashing (if server A crashes we can pull it back with Backups and standby hardware).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply