March 2, 2004 at 10:03 pm
I have searched through the forum, but did not find anything that directly addressed my issue. If I have missed a relevant thread please let me know.
In brief, here is my situation.
We have about 8 production SQL Server boxes, each running a single instance. We use the SQL server native backups, and sweep the backup files nightly into TSM. I back up the MSDB databases weekly.
My problem is that should we lose the computer room I would lose my backup history and spend a lot of time trying to figure out where backups were stored. In addition, at the disaster recovery center I would have only two physical servers on which to restore my production databases.
My plan is as follows:
-We have an off-site location (1,500 miles away) connected by a T3. I have created a job that runs whenever a successful backup (log or full) alert fires (the job is still in test). This job loads backup history information into a small database at the remote site.
-In the event of a distaster I have been assured that we would have connectivity to the remote site. I will create a list of the .bak/.trn files to restore and hand it off to an operator.
-I have a script that then recurses the directory of restored files and does a restore with load_history using DMO.
-I can then go through and restore the databases using Enterprise Manager (we are probably talking about 15 databases for the first phase of disaster recovery with the rest to follow, and management often likes to be able to do point-in-time for disaster recovery tests).
My main question is, "Is this a good way to handle disaster recovery? Am I missing a simpler approach?"
I thank you for you advice and input.
-Jerome
March 3, 2004 at 9:57 am
Jerome,
Sounds like a sensible solution, but have you thought of using SQL Server Log Shipping to maintain a warm backup server of your most critical database(s) / server(s) (you mention 15 database above), i don't know your database or network usage so don't know whether the T3 line would be sufficient!?
To get over the issue with only having two backup servers you could install named instances of SQL Server on the two backup servers and Log Ship your transactions from the live systems to the named instances on the off site servers. As the servers wouldn't be live it wouldn't matter if they aren't that beefy, in a disaster scenario you could backup and take offline any databases on instances that you don't require instantly (so as to free up system resources on the backup servers), you could then move the urgently required databases to the two default instances of SQL Server on the two backup servers thus not effecting applications that reference the server name directly (as long as you have updated the server names as necessary), that should keep you going until those purchase req's clear and your flashy new servers arrive.
Alternatively if full Log Shipping is overkill you could just use Log Shipping to copy your msdb and master databases regularly to the offsite servers (again using named instances) then you would just need to create and re-attach your database backups (assuming they havn't been destroyed during the disaster). Log Shipping also allows you to set up alerts, specify the log shipping schedule, generate reports (to keep management happy) and is also good for audit trails etc....
There are never any hard and fast rules when it comes to disaster recovery, you just have to weigh up cost and effort against benefit really. Anyone else got any ideas?
Hope that helps,
Lloyd
March 3, 2004 at 9:59 pm
Lloyd,
Thank you for your suggestion.
I had considered log shipping as a possibility. The problem is that the remote server will be (though is not yet) a production machine for that remote site, and I'm not sure how I feel about using it as a standby server.
I hadn't thought about trying log shipping for just the master and msdb databases though. To be honest, I'm not thrilled at the prospect of setting up a number of named instances, but there may be a way around that. I'll have to think about it.
For the moment I'm going to try to tack together a solution using my current ideas, just to make sure that there is *something* in place.
I will keep looking at log shipping though, and if I come up with anything I feel is elegant I'll post it.
Thanks for your help.
-Jerome
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply