0

I've seen several online sources mentioning that you should always have a restore script automated for the possible need to restore a large number of databases at once and quickly get back up and running to deliver on your SLA.

However, considering I'm running synchronous mirroring in two availability zones, I'm struggling to find a scenario in which I would need to do mass restores on a large number of databases where scripting a restore automation solution would be necessary.

Can anyone point to me to realistic scenario in which you'd need the capability to do a mass number of restores in a scripted manner?

  • i had asked this question last week, but i think i deleted the original as a duplicate by accident when doing some cleanup. I couldn't find the original in my profile, so if this is truly a duplicate, i apologize and if link provided will merge/delete this one asap. thanks! – SheldonH Jan 25 '16 at 17:52
  • 1
    Can you cite some of these sources? I'm not so sure the emphasis is going to be on "restoring a large number of databases" - in the event of a catastrophe (let's say your database has crashed and the mirror server is unavailable or unreachable), having a restore script handy even for restoring a single database can save time (which can mean saving money, and in some cases, a LOT of money) - now multiply that by the number of databases you actually have. – Aaron Bertrand Jan 25 '16 at 18:29
  • @AaronBertrand i'll keep a look for those statements. I think I had read this from Paul randal and brent ozars sites, indicating that a scripted restore solution in place was highly recommended to ensure quick recovery. I agree with the concept, but in justifying some additional research time on this, I'm trying to discover likely scenarios I'd need to actually do this when I already have a synchronous mirror up in a different region. The mirror server seems to resolve a lot of the potential risk, but there is always something else that could come up :-) – SheldonH Jan 25 '16 at 18:43
  • 1
    Having a scripted restore solution in place is highly recommended whether you have one database or 50,000, and whether or not you have complete and utter faith in your DR site. – Aaron Bertrand Jan 25 '16 at 19:47
2

So you've got two zones. Let's say zone one floods or burns... it's completely gone. All the hardware is destroyed, and the old site isn't even usable. It could be weeks putting it back together. Thankfully, you're still online thanks to zone two.

Of course, you might try something like short-term leasing servers to get redundancy back while you put together your real new production facility, but really the time spent getting those online is a distraction from your main task, which should be getting a new primary facility up to snuff as quickly as possible. If you do attempt the temporary facility, having the restore scripts will greatly aid getting the temporary facility online quickly, allowing you to get back to your main task.

If you don't opt for the temporary facility, how capable is your zone two site really? I hear all the time about failover equipment that's really just retired equipment from from the primary site. This often means equipment that is older, which raises concerns about both performance and reliability. How long do you really want to leave that facility running as your only data center? Are you taking good backups while this site runs as primary?

In short, wouldn't you like the setup process for your new site to be as quick, reliable, and well-understood as possible? The longer it takes to get the main facility back up and running, the more likely it is for something to also go wrong at your second facility.

Of course, this is just one scenario. It's the big scary total destruction scenario the seems to unlikely to ever actually happen for you, and maybe even that's true. But this kind of things plays out on a smaller scale all of the time. The ability to do quick, reliable, scripted restores for your entire data center for the big emergencies implies the ability to also do quick, reliable, scripted restores for the small emergencies. And that's a good thing.

  • this is all housed in amazon ec2.. two different availability zones. I'd think the odds are against both entire regions being completely out... but you never know! – SheldonH Jan 25 '16 at 18:36
  • 2
    Individual Amazon regions go down for a few hours all of the time. That's why you're supposed to use two of them. What happens if one of those has a major problem that keeps it offline for longer? You don't really want to trust the other zone to stay online for the duration. The only good news here is that Amazon has more than two zones... if you have got your scripts handy, bringing up your environment in a new zone should be fairly easy. – Joel Coehoorn Jan 25 '16 at 18:39
  • Sorry, last question on this. If I have the second region up.. and i've been using synchronous mirroring.... it would fail over. Why would I need to do a mass restore and script to do this? That's the area I'm unclear on. Doesn't the high-safety mirroring entail the risk reduced by already having the database up and running? – SheldonH Jan 25 '16 at 18:45
  • 1
    This isn't about failing over to the 2nd region. It's about being ready and able to bring a new 3rd region online should the need arise. It's about being able to do a smaller restore if something is going wrong in syncing across the two regions. It's about being prepared for something you can't predict. – Joel Coehoorn Jan 25 '16 at 20:02

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.