SQL Server, PASS, and other data mishaps
How do you do Disaster Recovery
Going through the process of a large scale multi-location disaster recovery made me stop and think about all the different incarnations that can be used to recover database servers.
Living with a datacenter in Hurricane alley, We’ve been doing disaster preparedness(recovery) on a small scale for many years but this year we’ve been working towards recovering all of our assets to an offsite colocation. That part of the decision is easy, the actual method used to do these recoveries is definitely up in the air and I fully expect our processes to change for the better, every time we redo our disaster testing (many times a year going forward).
In exploring the recovery process we quickly realized that our “hardware failure” recovery documents weren’t going to work effectively in a datacenter failure situation. So, it was time to design a new set of criteria for success. I thought Id share our thought process and how we plan on tackling this always fun experience. Its worth mentioning in a side note that no SQL replication is wanted/allowed for in our case.
1st thought: Bring up blank OS builds for the database servers, load SQL Server, Patch it to the correct level while the tape restores of the database backups are happening, Recover the system databases then kick off the individual restores(that are scripted with the regular nightly backup jobs)
- Benefits to DBA: clean, repeatable, documentable process that we are mostly in control of.
- Drawbacks: Time consuming, potential version match issues, recovering system databases is always “fun”
2nd thought: Use a windows snapshot to restore the OS/Sql Binaries and Sql System databases then recover the user databases using the aforementioned scripts. This also buys us the nicety of having litespeed already installed
- Benefits to DBA: Faster, System level recovery done in a standard (for our system group) method
- Drawbacks: system/SQL recovery out of our (DBA) control
Since our Systems engineers are already asking to go the snap route (because thats common for other application servers), and we expect this method to take less overall time, we are planning on trying that first. Depending on how that test goes, we will likely have option 1 as a backup plan or potentially try that next time thats why we’re testing it, so that we can make sure we have it right.
As always, there’s more than 1 way to accomplish the same outcome so my question is how do you do off-site disaster recovery (testing)? Or maybe the better question is do you do disaster recovery testing? If not why?
| Print article | This entry was posted by Allen Kinsel on January 27, 2010 at 7:45 am, and is filed under Backup and Recovery, SQL Server. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
No trackbacks yet.
Adventures in SSRS
about 4 months ago - No comments
Came across a strange permission issue today that I thought I’d blog about since it is apparently somewhat common judging by the number of unanswered forum posts about it.
Adding/editing or Testing SSRS Datasources in Report Manager generates a permission error
A developer was trying to add a datasource and use “test connection” in the report manager web interface instead of BIDS which, incidentally
Have you checked your datatypes lately?
about 10 months ago - No comments
Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data
Invitation for T-SQL Tuesday #19 – Disasters & Recovery
about 1 year ago - 40 comments
Disasters
Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season. It also means its time for this months installment of T-SQL Tuesday.
This Months Topic
Disaster Recovery. This topic is very near and dear to me based on the fact that
How deep can you dive?
about 2 years ago - 11 comments
After last years Summit we launched a feedback site http://feedback.sqlpass.org in hopes of gathering all of the feedback about the event in one place. The number one thing people have asked for on that site is for there to be a track of sessions in the 400-500 level range. The problems with the community desire
The 3 most important words for a DBA
about 2 years ago - 2 comments
Occasionally I’m asked what the most important thing for a DBA to know is. It seems some people want to know what 1 thing to focus on in order to be successful long term as a DBA. I’ve pondered this question for quite a long time and I’ve given various answers over the years.
Reality intervenes
Ive
Database Automagic
about 2 years ago - 5 comments
This months TSQL Tuesday is hosted by a good friend Pat right over at SQL Asylum
For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.
The Meta Script
In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something
A tall tale of SQL database corruption
about 2 years ago - 4 comments
This corruption story begins like many. Somebody in a server room far far away decided to make a change to a VMware guest machine and that little change rippled through our poor server like a lady Gaga Meat Dress through the VMA’s. Needless to say, it wasnt pretty. The full set of events may never
Changing the owner of nearly anything in SQL Server
about 2 years ago - 1 comment
The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL. Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner. Alternatively at times there were those of us who used some other undisclosed hack of system tables
Something old is something new
about 2 years ago - No comments
For the new year I have decided to try something different with a series of small regular posts.
Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked. Often times these discussions end in a exclamation of “I knew that!!” Of
Im a Winner!
about 2 years ago - 1 comment
And no, I dont mean in my mothers eyes, thats a given.
I won the Ipad/SQL Monitor Contest that Redgate was sponsoring over on Grant Fritcheys Blog
Im thrilled to have won!! Ive been eyeing Ipads for quite some time but, the novelty & cost has kept me at arms length.
Im always in the market for DBA tools that can make
about 3 years ago
What about mirroring? to another SQL Server somewhere else? Is that an option? Also what kind of time do you need to be back up in? That usually helps to determine some of your options as well.
about 3 years ago
One of the slickest ways I’ve seen recently is to have a completely virtual DR farm. Run just a few VMware (or Hyper-V) hosts, and let each team build up their own virtual servers in DR. You have limited hardware horsepower in DR, but you only do testing of apps there, not full blown production. You might not even have enough horsepower to turn on all of your DR VMs at the same time.
Then when disaster strikes, you can easily grab more VMware host hardware, spread the load across more servers, and add capacity easily. You don’t have to buy hardware before you need it, and you can usually beef up the hardware fairly quickly even in disaster situations. It’s much easier to do this than to build the servers from scratch, and users are tolerant of decreased performance rather than having no servers at all.
about 3 years ago
Funny you should mention a virtual DR Farm, I believe that we’re doing something similar, but the problem as I see it is how do you physically get the servers recovered? regardless of the hardware, there has to be a better method of getting the software up. Since most DR sites arent permanent having everything running fulltime offsite isnt an option
about 1 month ago
Thanks for finally talking about >SQL Server Database Backup and Recovery, Disaster Recovery | Allen Kinsel – SQL DBA <Liked it!