SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Disaster Recovery Week / Latest Posts

RE: Disaster Recovery Week

john.w.walker — Fri, 14 Dec 2012 11:30:58 GMT

I am the IT Manager and I spend alot of time with policy, producedure and oversight for both BC and DR for App servers, SQL, D/C, terminal servers, fax server - what is critical to us.But, I have never been through a real disaster, and I would love to hear stories of failing over to a DR site and failing back. I would love to hear what sort of DR sites they have. We use SunGard, I knowthere are other ways to skin the cat, and not build another data center at another of our sites where we don't have the right power, AC or tech people.I have had a half dozen database servers die on me, but when you have spare hardware - and all you are talking about are backups and restores, it is important, it is concerning, but you have an office and a data center. You have AC, you have power, you have space and desks to work.

RE: Disaster Recovery Week

Rudy Panigas — Fri, 14 Dec 2012 11:21:26 GMT

I agree but the app systems (web, routers, networking, apps, bathrooms) are not "really" the job of the DBA. If you are responsible for those things then you need to document that too. Most companies have network teams, development teams, etc. that would manage those parts of the disaster. If your a DBA with those responibilities then I hope your well paid.Rudy

RE: Disaster Recovery Week

ashepard — Fri, 14 Dec 2012 11:14:54 GMT

(grins a sly grin) By the way - there is one more part called "the application" I never restore them myself but I hear tell that a database with out web server, app server and such is not useful. I disagree (smiles) I use the database all day long without every having to log in and use the app. :-D Seriously, (sigh) there is more to the system than just the database. 1) Web and app servers 2) AD servers for authentication. SA works fine for me but ..... 3) Tape/Disk backup servers to backup my backups. 4) Routers, and other hardware. 5) Bathroom and vending machines. I've been surprised at how many DR discussions about DR sites never, *never* discuss security, bathrooms, vending machines or where we will live/stay. Some how everyone will go to the DR site and laundry, living, etc will just take care of itself. :w00t:

RE: Disaster Recovery Week

Rudy Panigas — Fri, 14 Dec 2012 11:14:48 GMT

Well, like I mentioned before it appears that lots of people for get about DR situations where you need to rebuild basically everything from scratch. I would be interesting to see a poll on how many people have bare metal build documentation and if it's up to date.Rudy

RE: Disaster Recovery Week

john.w.walker — Fri, 14 Dec 2012 10:17:14 GMT

My point exactly Rudy, I was excited to hear about DR, but instead it is BC and all of us can tell about servers and storage blowing up.

RE: Disaster Recovery Week

Rudy Panigas — Fri, 14 Dec 2012 08:50:20 GMT

So anyone else have experienced an actual DR? I would like to here more from them. What did they learn? That doesn't mean just like a Sandy situation but also like a server(s) have died for some reason, prolong power outage, A/C down, etc.I've help out with DR in many situations that are not weather related. Even worked on a DR for a company that their building was damage from an earthquake. It didn't fall but no one was allowed in for months. That to me is also a DR situation.Rudy

RE: Disaster Recovery Week

john.w.walker — Fri, 14 Dec 2012 08:43:10 GMT

Right Sandy is DR. None of your stories are, no matter what scewy name you give someone that expects to read about DR when advertised Steve.

RE: Disaster Recovery Week

Steve Jones - SSC Editor — Fri, 14 Dec 2012 08:32:55 GMT

Which original story? Losing power in a data center? That's a DR, technology related issue.Companies trying to be sure their data centers are running after hurricane Sandy, that's DR. There's a BC component to that, but the piece we are talking about here, with people working in technology, is the technology end of a disaster, which is usually known as DR.If you want to be pedantic about it and dig into what is BC and what is DR, that's fine, but I think that's a bit of a waste of time.

RE: Disaster Recovery Week

john.w.walker — Fri, 14 Dec 2012 07:19:20 GMT

All businesses should plan for DR and BC, but the weekly topic is DR and this is all about BC. I would thing SQL Server Central would know the difference. Even Steve's original story to start it is BC.

RE: Disaster Recovery Week

Rudy Panigas — Fri, 14 Dec 2012 06:56:54 GMT

DR + BCP = Success :-)

RE: Disaster Recovery Week

crazy4sql — Thu, 13 Dec 2012 21:19:55 GMT

dont you think DR plan is also part of business continuity? Which business will plan DR without considering business continuity :-D

RE: Disaster Recovery Week

john.w.walker — Thu, 13 Dec 2012 07:39:49 GMT

All of these articles this week are interesting, but they are not about disaster recovery - they are about business continuity. The difference is big, with a disaster you lose your infrastructure so you have to run off an alternate site such as SunGard or a cloud provider. Having backups and backup servers is great, but a disaster is when you have no place to restore your tape or USB drive or cloud backups, if you don't have a Disaster Recovery plan.

RE: Disaster Recovery Week

Rudy Panigas — Tue, 11 Dec 2012 08:36:12 GMT

Hello Everyone,Here are several observations I have notice over a period of 10+ years working on disaster recovery. 1) Most people have no idea of how their SQL production servers are setup. Like port numbers, Link Connections, sp_configure settings, etc. Documentation is very poor in 90% of the cases I've worked on. I have created a script that helps in this area and is posted on this site. Just seacsearch "SQL Server Documenter". It not perfect but a good start. 2) Backups. You won't believe how many people have never tested the restores of their databases. Backups are only as good as your restores. Lots for companies have large databases but do not replicate in any manner. Then they complain that the restores are too long. 3) Testing. You would be shocked how many companies have no DR testing. Some say "we can restore to the cloud" and yet have never tried it. If we do more DR testing then we can weed out the first two issues listed above.4) Bare Metal builds. This seem like a lost art. With replication and HA solutions we forget that you still to know how to perform bare metal builds.Well, that's all I have for now.Rudy

RE: Disaster Recovery Week

Steve Jones - SSC Editor — Mon, 10 Dec 2012 09:36:50 GMT

You can restore a filegroup, which might help if you have data split by filegroups. Perhaps current data in one, archived in another(s). That might allow you to restore one group and be running.Be aware, however, that if you put indexes on one and data in another filegroup, if you restore one, the app may not work well.

RE: Disaster Recovery Week

crazy4sql — Mon, 10 Dec 2012 09:18:23 GMT

[quote]No option. You can't do it. Physically it will take time to read the backup file and rewrite the disk. If you haven't pre-built the log file, that has to be zero'd out. If you don't have IFI enabled, the data file also has to be zero'd out. Both take time.I don't know what your I/O subsystem can do, but 14TB is substantial. At the very least, I'd build a cheap, whitebox system that had consumer level drives of 16-20TB. That could probably be done for $2-3k these days and it would get you running quickly if you had some sort of log shipping enabled.[/quote]thanks Steve for the reply and suggestion. Just one doubt, can we consider "restoring with partial option" ?

RE: Disaster Recovery Week

pbarbin — Mon, 10 Dec 2012 08:34:07 GMT

Good timing on the article. Our company just completed our yearly DR practice test. It's not an exhaustive test, but we take some of the more critical systems and try to bring them up in a virtual network at our mirrored site (affectionately known as "the bubble"). As part of our DR strategy, we employ Sql Server Database mirroring. All web, app, and reporting servers are virtual and are replicated to our DR site.To start the test, the network guys create the bubble and bring up domain controllers and other infrastructure needed to support the network.Databases mirroring is broken. Mirrored Sql servers are brought into the bubble and renamed to match production server names. Sql Server allows you to rename the server and instance easily with the sp_addserver, sp_dropserver commands. We then recover the mirrored dbs and have to setup linked servers and verify all logins and users have the correct passwords and permissions. All of this is scripted prior to the mirror break to minimize our virtual downtime.We hand over the environment to a small team of testers with test scripts and they go to work.While we didn't plan perfectly, overall, the test was a success. We learn valuable lessons each time we run the test. Interestingly, many difficulties are [b]due [/b] to the fact that we are simulating a disaster. But experiences tells us that in a real disaster there will be many other problems that we didn't forsee. This keeps us honest with ourselves that bringing up a production environment in a real disaster won't be as easy as it looks on paper.Paul

RE: Disaster Recovery Week

Steve Jones - SSC Editor — Mon, 10 Dec 2012 08:31:36 GMT

[quote][b]crazy4sql (12/10/2012)[/b][hr]I have a question on one of the DR scenario,Lets say I have 14 TB database in oltp mode with no/least performance problem. (yes weired but lets accept it). Now lets say the system goes down because of any problem, and I have the valid set of latest backup. I can start restoring but the problem is how to meet the objective of RTO (recovery time objective) of 3 hrs. As restoring this ~tb size of full backup will definately take time to restore.*************************************I know there should be some faiover configured like replication,mirroring,cluster-failover or logshipping(least preferred). Or should have some offline site where backups getting checked and restored in time.****************************************Lets assume because of any reason these options was not considered and the only option now is restore. What would be my strategy to bring this database online within RTO?[/quote]No option. You can't do it. Physically it will take time to read the backup file and rewrite the disk. If you haven't pre-built the log file, that has to be zero'd out. If you don't have IFI enabled, the data file also has to be zero'd out. Both take time.I don't know what your I/O subsystem can do, but 14TB is substantial. At the very least, I'd build a cheap, whitebox system that had consumer level drives of 16-20TB. That could probably be done for $2-3k these days and it would get you running quickly if you had some sort of log shipping enabled.

RE: Disaster Recovery Week

crazy4sql — Mon, 10 Dec 2012 08:22:39 GMT

I have a question on one of the DR scenario,Lets say I have 14 TB database in oltp mode with no/least performance problem. (yes weired but lets accept it). Now lets say the system goes down because of any problem, and I have the valid set of latest backup. I can start restoring but the problem is how to meet the objective of RTO (recovery time objective) of 3 hrs. As restoring this ~tb size of full backup will definately take time to restore.*************************************I know there should be some faiover configured like replication,mirroring,cluster-failover or logshipping(least preferred). Or should have some offline site where backups getting checked and restored in time.****************************************Lets assume because of any reason these options was not considered and the only option now is restore. What would be my strategy to bring this database online within RTO?

RE: Disaster Recovery Week

ashepard — Mon, 10 Dec 2012 07:50:06 GMT

Backups have only given me grief. Its restores that count & make me look good. However I have gotten burned with "level of expectation" 1) A dual RAID-5 SAN died while I was on vacation. It was in the cleanest part of the city garage but too many disks failed. I came in, restored the database and maintanece jobs to another server in under six hours. I was proud - my boss was livid. Why did it take six hours to restore and reconfigure an 18 gig database? He thought it should only take twenty min and the process be documented so anyone could do it. Lesson learned - have a system up and ready. 2) We have several databases where we only keep six months or less of backups. I like to keep three years. Why? Problems do not always surface in six months. Year end processing has found issues with data from a year ago. 3) Would I use the cloud? Not even in an emergency - only a catastrophy. Why? (laughs) our internet connection is one of the things to go. Its not under our control - but the data center is. Those that can work while others are down make the money and keep their jobs.

RE: Disaster Recovery Week

ALZDBA — Mon, 10 Dec 2012 00:49:44 GMT

just a very primitive tip: Start small ! Start building your disaster recovery project with a [b]very small scope[/b], [b]not that critical to business activity[/b].[b]Only read the headers[/b] of the articles / BP / books you find about Disaster Recovery, [b]so you know the topics to keep in mind and are not overwhelmed by the tiny details behind it. [/b]That is for later on, after you review your first, second, third cycle of building your general DRP.Pick any non-critical database and start from there on.[u]Once you are comfortable with the elementary concepts[/u], and their impact to the topic from your point of view, make sure your project gets [b]sponsored [/b]by your superiour(s), because at certain point in time, you will have to [b]contact business responsibles [/b]to discuss your findings and align them [b]budget[/b] wise.[u]There is more to disaster recovery than SQLServer, but it makes a good starting point.[/u]

Disaster Recovery Week

Steve Jones - SSC Editor — Sun, 09 Dec 2012 03:43:49 GMT

Comments posted to this topic are about the item [B]Disaster Recovery Week[/B]