Disaster Recovery Planning

I've never seen a disaster recovery plan for SQL Server. Can anyone one
direct me to an example. This would be immensley helpful to understanding
how to put one together. Thanx. -Cqlboy
How long is a piece of string?
DR plans are pretty specific to the company/environment in question.
I've set up an active/active SQL cluster in our DR site and am using log
shipping to keep the DR databases in sync with the production DBs;
combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
for the server names to allow to very quick, transparent redirection of
client connections to the DR servers in the event of a disaster. I also
wrote a stored proc (that resides on our log shipping monitor box) to
automate all the log shipping role changes (150+ databases is a bit too
time consuming to do manually). Works pretty well too (we had a
"disaster" in August this year when our SAN vendor stuffed up an upgrade
on our production SAN - what a crappy weekend that was!)
My only revision to my SQL DR plan/environment would be more RAM & CPU
for the DR servers as I'm consolidating 4 production SQL instances down
to the 2 DR instances on the active/active cluster (plus a few other DBs
that don't live on our production clusters). The cluster nodes were
pretty stressed for a week back in August but it's hard to justify
spending the extra $$$ when the servers are really only used (hopefully)
very infrequently (like one week in a year).
Cheers,
Mike.
Cqlboy wrote:
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Hi
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1 --administaiting
best practices
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Thanx, this was helpful. But I was hoping to have something more explicit
since it appears I'll have to create my DR document from scratch. A template
or example to help guide my thinking and writing would be ideal. I'm not a
technical write, eh?
-Cqlboy
"Mike Hodgson" wrote:

> How long is a piece of string?
> DR plans are pretty specific to the company/environment in question.
> I've set up an active/active SQL cluster in our DR site and am using log
> shipping to keep the DR databases in sync with the production DBs;
> combined with MDAC aliases, non-standard TCP ports for SQL & DNS aliases
> for the server names to allow to very quick, transparent redirection of
> client connections to the DR servers in the event of a disaster. I also
> wrote a stored proc (that resides on our log shipping monitor box) to
> automate all the log shipping role changes (150+ databases is a bit too
> time consuming to do manually). Works pretty well too (we had a
> "disaster" in August this year when our SAN vendor stuffed up an upgrade
> on our production SAN - what a crappy weekend that was!)
> My only revision to my SQL DR plan/environment would be more RAM & CPU
> for the DR servers as I'm consolidating 4 production SQL instances down
> to the 2 DR instances on the active/active cluster (plus a few other DBs
> that don't live on our production clusters). The cluster nodes were
> pretty stressed for a week back in August but it's hard to justify
> spending the extra $$$ when the servers are really only used (hopefully)
> very infrequently (like one week in a year).
> Cheers,
> Mike.
> Cqlboy wrote:
>
|||This isn't a DR plan per say but does cover a lot of the areas you need to
be aware of.
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Operations Guide
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:3ECD364A-14D7-42D5-B8B6-B44D3F6A95A9@.microsoft.com...[vbcol=seagreen]
> Thanx, this was helpful. But I was hoping to have something more explicit
> since it appears I'll have to create my DR document from scratch. A
> template
> or example to help guide my thinking and writing would be ideal. I'm not
> a
> technical write, eh?
> -Cqlboy
>
> "Mike Hodgson" wrote:
|||Requires free registration:
http://www.sqlservercentral.com/colu...eframework.asp
Other example links:
http://www.sql-server-performance.co...r_examples.asp
http://www.disasterrecoverysurvival...eryScript.html
http://sqljunkies.com/HowTo/F30B1E5F...CEAD46C79.scuk
And someone published a book, but I am not finding a reference to it at
the moment. Might check http://www.amazon.com or
http://www.techrepublic.com
Hope this helps,
Michelle
|||Hi Cqlboy,
For DR you have many options, and like in life, how much many so much music
: ) .
Important thing is how fast your server must be online?
If your business cannot wait, implement failover clustering, but this is
most expensive and redundant solution.
Also you have option of standby SQL server whish is connected to working SQL
server in the means of LOG shipping so if you have spare server, go for it.
If your business can wait some time you can use some sort off RAID to
prevent lose of data in case of disk failure and if you cannot afford RAID
you better have good backup strategy, which depends on how often your data
are changed.
So you can have only Full db backups, Full and Differential backups and
Backups off transaction log.
I tried to enumerate what I could remember.
Regards,
Daniel
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
> I've never seen a disaster recovery plan for SQL Server. Can anyone one
> direct me to an example. This would be immensley helpful to understanding
> how to put one together. Thanx. -Cqlboy
|||Most of that is more fault tolerance than DR.
For example, a failover cluster is not a DR solution as both nodes need
to share the disk resources. Plus both nodes need to be pretty close to
each other (restricted by limitations of the IO transport protocols -
FC, SCSI - and also short latency of the heartbeat network between
nodes). If there is a real disaster (eg. a fire in your data
centre/computer room or a plane flies into the building where your
cluster is located) then you lose your production environment AND your
DR environment (and probably your job...if you're still alive). A DR
environment really needs to be shared-nothing (like log shipping to a
remote site or some 3rd party replication/geo-clustering technology, or
even standard MSSQL replication for the more budget conscious); you have
to assume you'll lose the entire production environment.
RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
myriad of other potential hardware issues) and minimal loss at that; 1
disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
5 array die, or the whole drive cage or RAID controlled then you're up
the creek and need to have an outage (potentially significant (how many
people keep a spare drive cage or RAID controller handy?)) to replace
hardware).
The 3 most significant factors to consider in devising your DR strategy are:
1) maximum acceptable service disruption (down time)
2) maximum acceptable data loss (5min? 1hr? 1day?)
3) budget (the all important $$$)
The solution will always be a compromise in those 3 areas.
But this is getting off the topic a bit. The original question was
about documentation for pre-devised DR plans, which is a bit of big
call. While there are some guides (as people have provided in this
thread) to get you thinking about a solution, factors to consider and
how to go about implementing/documenting it, the solution itself will
require mostly your own brain (and probably several specific questions
posted to newsgroups to overcome potential issues that may arise).
Cheers,
Mike
Daniel Joskovski wrote:
> Hi Cqlboy,
> For DR you have many options, and like in life, how much many so much music
> : ) .
> Important thing is how fast your server must be online?
> If your business cannot wait, implement failover clustering, but this is
> most expensive and redundant solution.
> Also you have option of standby SQL server whish is connected to working SQL
> server in the means of LOG shipping so if you have spare server, go for it.
> If your business can wait some time you can use some sort off RAID to
> prevent lose of data in case of disk failure and if you cannot afford RAID
> you better have good backup strategy, which depends on how often your data
> are changed.
> So you can have only Full db backups, Full and Differential backups and
> Backups off transaction log.
> I tried to enumerate what I could remember.
> Regards,
> Daniel
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:8DA906E5-C877-4078-8F89-9A5A1787BE3E@.microsoft.com...
>
>
|||Here is the 'book' I had mentioned:
Disaster Recovery Toolkit for the SQL DBA
Written by SQL Server expert, Brian Knight, this handy, "how-to"
toolkit contains comprehensive first-hand advice and scripts for
SQL Server DBAs that need to build and implement a successful
disaster recovery plan. With his tips and quips, Brian walks the
DBA through real-world scenarios using an easy, step-by-step
approach. And as part of the download, you'll receive four
scripts, which will greatly speed your recovery time!
Download it today, compliments of Lumigent:
http://www.lumigent.com/go/sd19
Michelle
|||Hi Mike,
Thanks for your time, but from my experience, every time when I have
"disaster" it was connected with some hardware problem, and in 60% of cases
was a disk failure, using plain software mirror "saves" my life many times.
So building some anti nuclear shelter, and not having simple mirror this
days seems ridiculous to me. And nobody will blame me if plane flies in
computer room, but somebody can kill me if I don't have fresh copy of his
data. ;-)
Regards,
Daniel
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:evnIwUI6EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Most of that is more fault tolerance than DR.
> For example, a failover cluster is not a DR solution as both nodes need
> to share the disk resources. Plus both nodes need to be pretty close to
> each other (restricted by limitations of the IO transport protocols -
> FC, SCSI - and also short latency of the heartbeat network between
> nodes). If there is a real disaster (eg. a fire in your data
> centre/computer room or a plane flies into the building where your
> cluster is located) then you lose your production environment AND your
> DR environment (and probably your job...if you're still alive). A DR
> environment really needs to be shared-nothing (like log shipping to a
> remote site or some 3rd party replication/geo-clustering technology, or
> even standard MSSQL replication for the more budget conscious); you have
> to assume you'll lose the entire production environment.
> RAID will only protect disk loss (not CPU, RAM, NIC, switches and a
> myriad of other potential hardware issues) and minimal loss at that; 1
> disk in a RAID 5 array dies then fine, hotswap it; if 2 disks in a RAID
> 5 array die, or the whole drive cage or RAID controlled then you're up
> the creek and need to have an outage (potentially significant (how many
> people keep a spare drive cage or RAID controller handy?)) to replace
> hardware).
> The 3 most significant factors to consider in devising your DR strategy
are:[vbcol=seagreen]
> 1) maximum acceptable service disruption (down time)
> 2) maximum acceptable data loss (5min? 1hr? 1day?)
> 3) budget (the all important $$$)
> The solution will always be a compromise in those 3 areas.
> But this is getting off the topic a bit. The original question was
> about documentation for pre-devised DR plans, which is a bit of big
> call. While there are some guides (as people have provided in this
> thread) to get you thinking about a solution, factors to consider and
> how to go about implementing/documenting it, the solution itself will
> require mostly your own brain (and probably several specific questions
> posted to newsgroups to overcome potential issues that may arise).
> Cheers,
> Mike
> Daniel Joskovski wrote:
music[vbcol=seagreen]
SQL[vbcol=seagreen]
it.[vbcol=seagreen]
RAID[vbcol=seagreen]
data[vbcol=seagreen]
understanding[vbcol=seagreen]