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. -CqlboyHow 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_administration_best_practices.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:
> > 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
>|||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/prodtechnol/sql/2000/maintain/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...
> 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:
>> > 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|||Requires free registration:
http://www.sqlservercentral.com/columnists/sjones/incidentresponsetheframework.asp
Other example links:
http://www.sql-server-performance.com/disaster_recover_examples.asp
http://www.disasterrecoverysurvival.com/SQLDisasterRecoveryScript.html
http://sqljunkies.com/HowTo/F30B1E5F-F50F-40A8-96F2-476CEAD46C79.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...
>>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
>
>|||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:
> 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...
> >
> >>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 Mike,
Thanks for your info about http://www.lumigent.com/go/sd19/index.asp. In
that Brain has mentioned a way using (sp_help_revlogin procedure) to
transfer logins to different server with thier original SID, default database
and server role settings but not the database permission setting. Do you have
any idea how to transfer DB permission?
Regards
Shriniwas
"Daniel Joskovski" wrote:
> 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:
> > 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...
> > >
> > >>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
> > >
> > >
> > >
>
>|||I haven't read the book that Michelle mentioned. However, object
permissions are contained within a database and so are transferred with
the DB. The logins can be transferred with DTS packages. Once you have
the logins and the DB(s) on the secondary server you can simply use
sp_change_users_login (or some derivative of it) to marry up the users
within the DB to the logins in the master DB.
Cheers,
Mike.
Shriniwas Tiwary wrote:
> Hi Mike,
> Thanks for your info about http://www.lumigent.com/go/sd19/index.asp. In
> that Brain has mentioned a way using (sp_help_revlogin procedure) to
> transfer logins to different server with thier original SID, default database
> and server role settings but not the database permission setting. Do you have
> any idea how to transfer DB permission?
> Regards
> Shriniwas
>
> "Daniel Joskovski" wrote:
>
>>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:
>>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...
>>
>>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
>>
>>|||Daniel,
I wouldn't classify a single disk failure as a disaster. A bummer,
perhaps, but not a disaster. If professional DBAs don't have AT LEAST
software RAID 1 or above on their data & log disks then they ought to be
ashamed of themselves.
I wasn't talking about building an anti nuclear shelter or anything like
it. I was merely saying that to cater for a properly disaster (such as
a computer room fire or even just your primary server blowing up) you
need separate physical DR hardware and it has to be separated
geographically from your production hardware. It doesn't have to be
enormous & expensive - you can do it with a cheap standalone SQL server
with local HDD using plain old ordinary SQL replication (depending on
the data volume & load), but it would largely defeat the purpose of
having the redundant server if it was sitting right next to your
production server.
Cheers,
Mike.
Daniel Joskovski wrote:
> 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:
>>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...
>>
>>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 Mike,
Thank you for spending your time for answering my posts. I get your point,
but my intention was to point that some usually not disaster situation can
turn into real disaster, so in my opinion, good DR plan, needs to have more
than one DR strategy that will depends on type of disaster. I don't want to
argue with you, but what is sufficient geographical dislocation ? (if you
have recent natural disasters in mind) and also I think that geographical
dislocation of regular backups must be organized in more then one level. If
you have backup in town 200 miles away is good in the kind of natural
disaster, but having backups in location in same neighborhood is sufficient
in case of robbery (somebody can simply still all your computers from one or
more location).
Regards,
Daniel
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:OW#wagT#EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Daniel,
> I wouldn't classify a single disk failure as a disaster. A bummer,
> perhaps, but not a disaster. If professional DBAs don't have AT LEAST
> software RAID 1 or above on their data & log disks then they ought to be
> ashamed of themselves.
> I wasn't talking about building an anti nuclear shelter or anything like
> it. I was merely saying that to cater for a properly disaster (such as
> a computer room fire or even just your primary server blowing up) you
> need separate physical DR hardware and it has to be separated
> geographically from your production hardware. It doesn't have to be
> enormous & expensive - you can do it with a cheap standalone SQL server
> with local HDD using plain old ordinary SQL replication (depending on
> the data volume & load), but it would largely defeat the purpose of
> having the redundant server if it was sitting right next to your
> production server.
> Cheers,
> Mike.
> Daniel Joskovski wrote:
> > 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:
> >
> >>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...
> >>
> >>
> >>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
> >>
> >>
> >>
> >
> >