What solutions make returning from a disaster easier?

Ken Hutchinson


I'm trying to figure out what solution (replication, mirroring, clustering) would work best for me.

I have been reading many articles in BOL and in this forum. Most talk about getting data TO a backup/standby/subscriber, but I can't find a lot of info regarding getting the data BACK after a disaster is over.

We have a main office and a disaster recovery facility. Most of the time there are no data updates at the disaster location. So, I need to get data to the disaster facility via WAN (latency is not a huge issue - end of day syncing is fine) for backup purposes. In the event of a disaster, the main office will be offline and data changes will happen at the disaster site. When the disaster is "over" and we return to the main office, what's the best scheme to reverse the data back to the main office to start business again We are a financial company, and have gigabytes of relatively static data. Most changes are current day. So, to snapshot a 100GB database when I know only a few hundred MB changes a day doesn't seem feasible to me.

Most replication scenarios (at least from what I see) can't easily "reverse" the replication after a disaster situation. I'm looking at merge replication on a schedule which seems to look good, but was wondering if anyone else has any ideas or suggestions

TimDBA

Well, really, it depends on your budget. It is possible to have clustered machines in seperate geographic locations, and that would allow you to failover to your dr site when needed. When you need to, you can just fail back to your main site. I've only read of this happening, and the cost is likely not all that feasible either...it just depends. There is a new feature in 2005 call peer-to-peer replication. In this new topology, the nodes in the replication circle always relay their data to each other, which means any changes on any of the nodes are replicated to the other nodes as they happen. It gets ride of the heirarchical structure that was present in the former replication models....its something to think about. At my current company, we use a third party software/hardware from falconstor that replicates the sectors of data of the machine to our dr site....it is expensive, and honestly hasn't worked all that great. Another idea is to just put your production machines at your dr site and cluster them there....that way, it is unlikely a natural disaster will effect you, only hardware failures, which the cluster should cover you on. Hope this helps.

Tim Chapman

Ken Hutchinson

I didn't know about peer-to-peer replication. I started researching it in BOL and it looks pretty good, right up to the point where it said:

Use of identity columns is not recommended.

In some of our databases, we not only use identity columns, but we use them as foreign keys in other tables, so the workarounds don't seem to help.

Nonetheless, in some of our other database (where we don't use identity columns), this looks like a feasible option.

So, now I'm up to database mirroring. I'm going to give that a try....

Michael Hotek

That's the $64K question. There are a LOT of things that go into this, which is why I teach a 5 day class that just starts to cover the basics. I'll try to recap some of the highlights in a short space.

Getting the data to the standby is the easy part and can be accomplished all kinds of ways. Getting the data back, as you're finding, is the one thing that virtually no one ever talks about. Want to watch a sales rep from one of these companies that either has DR services or sells DR software quirm Ask them this question and you'll get a lot of stuttering, stammering, and non-answer answers.

To effectively do this, you need to employ something that inherently allows changes to be made at any location since it has built in capabilities to queue all of the changes and then just move all of the deltas, bringing the entire system back in synch. Log shipping can't do this and neither can clustering. You can do this with either replication or Database Mirroring.

Database Mirroring accomplishes this by first controlling where you write to and only allowing writes in one location, which is why you can reverse roles back and forth easily and both database remain in synch. If you are using Database Mirroring to a DR site, I VERY STRONLY suggest using High Performance operating mode (asynchronous). This way, you do not impact the application throughput with your DR stuff.

The other option is to utilize replication. Transactional with queued updating subscribers or merge would be two options. With either option, you would want to have writes only occuring on one side or the other although this is not strictly required or enforced. Both of these methods will send changes as they occur to your DR site, applications can be re-pointed to access the DR servers if needed and continue to write transactions, as soon as the primary site is back online replication will automatically recognize it and send any changes back, then you can decide when to switch applications back over.