We currently have a number of SQL Server 2005 Standard edition instances which support an intranet and need to be integrated into our disaster recovery plan. I was hoping to use database mirroring, on the grounds that the description of this on the package made it out to fit the situation, but the price of SQL Server Enterprise edition has scuppered that. I'm merely a developer given a task out of my comfort zone, so I'm hoping that you guys could give me a steer in the right direction.

The RPO for the disaster recovery plan is unclear, but max of a couple of hours.

As far as I can see we have three options:

  1. A bit-level solution, whereby the whole server is replicated as a bit level.
  2. Database log shipping - could that work? Would it need a frequent backup schedule that would periodically kill intranet applications (which are in constant use)?
  3. Replication. This doesn't seem like the right kind of solution to me, but I guess that it could be put into effect with a large amount of effort.

Of these three, which seems best, given the there is a cost limitation? Are there other obvious options that I'm missing?

Many thanks for your help

  • I'm a little confused. You talk about "Disaster Recovery" but you/re mostly talking about high availability. The two things, while often related, are very different. – Rob Moir Nov 15 '10 at 12:28
  • Sorry for the confusion. I guess that I'm talking about Disaster Recovery with high availability (if a two hour RPO counts as high). What has the conflation left you confused about? – Yellowfog Nov 15 '10 at 14:59

Were I you I'd go with log shipping. Yes you'd want it to be on a frequent schedule, however no it does not kill your applications accessing that server. Your secondary server (the one you are log shipping TO) will be unavailable during the restore of the log shipped files - however the source machine (your production server) is available the entire time.

Keep it in mind that I'm also giving this answer with a limited knowledge of your DB here. Some things that are relevant to answering the question are how large is the database, is it a single database or multiple, how often does the data in the database(s) get changed? It may also be feasible to just do full backups and transactional backups depending on your needs.

  • About twelve databases that need Full recovery and have frequent and large updates. The largest DB is about 22Gb in size and the transaction log grows to around 10Gb I think. How does this affect the thinking? – Yellowfog Nov 15 '10 at 10:40
  • Then I think I was right on with my original suggestion. I'd go with log shipping. One of my servers has around the same number of DB's with about 100GB total size - lots and lots of updates and I have them on a 15 minute logship frequency. – Dave Holland Nov 15 '10 at 14:23
  • We use logshipping locally and it works quite nicely (our max db size is 250 GB). – jl. Nov 15 '10 at 14:52
  • Thanks then, I'll give it a go. (I can't yet vote up, sorry) – Yellowfog Nov 15 '10 at 15:00
  • Yellow - no problem. Good luck with it. It's fairly easy to set up. – Dave Holland Nov 15 '10 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.