Disaster Recovery Site Setup

All,
We are planning to setup disaster recovery in Vegas, actual one is in
SanFrancisco.
We have to synchronize the databases from SanFrancisco to Vegas.Ours is
24 X 7 ecommerce site.
Requirement for Disaster recovery is automatic failover and fail back
from one site to another site with minimum manual intervention.
Please advise if anybody has setup the disaster recovery.
Is Transactional Replication is good for two way replication?
Log shipping is good for just one way synchronization, and our log backups
will be 30 gb per day.
Has anybody has used wansync or goldengate softwares for the disaster
recovery setup?
Thanks.Hi,
SQL Server 2005 - Database Mirroring is the most suggested mechanism for
you. Database mirroring with a
Witness instance supported will do a automatic failover if the Primary
instance is down.
See the below URL on the article on setup of database mirroring.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Thanks
Hari
SQL Server MVP
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23rbl0R7wGHA.1484@.TK2MSFTNGP04.phx.gbl...
> All,
> We are planning to setup disaster recovery in Vegas, actual one is in
> SanFrancisco.
> We have to synchronize the databases from SanFrancisco to Vegas.Ours
> is
> 24 X 7 ecommerce site.
> Requirement for Disaster recovery is automatic failover and fail back
> from one site to another site with minimum manual intervention.
> Please advise if anybody has setup the disaster recovery.
> Is Transactional Replication is good for two way replication?
> Log shipping is good for just one way synchronization, and our log backups
> will be 30 gb per day.
> Has anybody has used wansync or goldengate softwares for the disaster
> recovery setup?
>
> Thanks.
>|||You mention that you are a 24 x 7 ecommerce site. Besides the
databases, what are you doing to protect your web servers? If you host
your web servers in SanFrancisco and you lose the whole site, do you
have an automated means of redirecting the web traffic to the DR site
in Vegas?
In regards to your database solution, SQL Server 2005 - Database
Mirroring is fine for some things, however when running in the
automatic failover mode, you must use syncronous replication.
Syncronous replication across a WAN is going to kill the performance of
your primary database because it must wait for the write to commit on
the secondary server before it can commit on the primary server.
The other issue with SQL Server 2005 replication with automatic
failover is the placement of the witness server. This setup only truly
works if you have the witness server in a third location with seperate
communication paths to the primary and secondary data center.
Otherwise, with the witness server in the primary data center you will
not failover if you have a site loss (witness server is also gone).
With the witness server in the secondary data center you will have
false failovers if the WAN link goes down, because the witness server
and the secondary server will think the primary is unavailable.
If you go with SQL Server 2005 Database Mirroring, then I would forget
about the automatic failover mode and go with asyncronous replication
and a manual failover. Your other option is to go with a third party
solution such as LifeKeeper for SQL Server or one of the other ones you
mentioned.
David A. Bermingham, MCSE, MCSA:Messaging
Systems Engineer
www.steeleye.com
Hari Prasad wrote:
> Hi,
> SQL Server 2005 - Database Mirroring is the most suggested mechanism for
> you. Database mirroring with a
> Witness instance supported will do a automatic failover if the Primary
> instance is down.
> See the below URL on the article on setup of database mirroring.
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> Thanks
> Hari
> SQL Server MVP
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:%23rbl0R7wGHA.1484@.TK2MSFTNGP04.phx.gbl...
> > All,
> > We are planning to setup disaster recovery in Vegas, actual one is in
> > SanFrancisco.
> > We have to synchronize the databases from SanFrancisco to Vegas.Ours
> > is
> > 24 X 7 ecommerce site.
> > Requirement for Disaster recovery is automatic failover and fail back
> > from one site to another site with minimum manual intervention.
> >
> > Please advise if anybody has setup the disaster recovery.
> >
> > Is Transactional Replication is good for two way replication?
> > Log shipping is good for just one way synchronization, and our log backups
> > will be 30 gb per day.
> >
> > Has anybody has used wansync or goldengate softwares for the disaster
> > recovery setup?
> >
> >
> > Thanks.
> >
> >

Disaster Recovery Site Setup

All,
We are planning to setup disaster recovery in Vegas, actual one is in
SanFrancisco.
We have to synchronize the databases from SanFrancisco to Vegas.Ours is
24 X 7 ecommerce site.
Requirement for Disaster recovery is automatic failover and fail back
from one site to another site with minimum manual intervention.
Please advise if anybody has setup the disaster recovery.
Is Transactional Replication is good for two way replication?
Log shipping is good for just one way synchronization, and our log backups
will be 30 gb per day.
Has anybody has used wansync or goldengate softwares for the disaster
recovery setup?
Thanks.Hi,
SQL Server 2005 - Database Mirroring is the most suggested mechanism for
you. Database mirroring with a
Witness instance supported will do a automatic failover if the Primary
instance is down.
See the below URL on the article on setup of database mirroring.
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
Thanks
Hari
SQL Server MVP
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23rbl0R7wGHA.1484@.TK2MSFTNGP04.phx.gbl...
> All,
> We are planning to setup disaster recovery in Vegas, actual one is in
> SanFrancisco.
> We have to synchronize the databases from SanFrancisco to Vegas.Ours
> is
> 24 X 7 ecommerce site.
> Requirement for Disaster recovery is automatic failover and fail back
> from one site to another site with minimum manual intervention.
> Please advise if anybody has setup the disaster recovery.
> Is Transactional Replication is good for two way replication?
> Log shipping is good for just one way synchronization, and our log backups
> will be 30 gb per day.
> Has anybody has used wansync or goldengate softwares for the disaster
> recovery setup?
>
> Thanks.
>|||You mention that you are a 24 x 7 ecommerce site. Besides the
databases, what are you doing to protect your web servers? If you host
your web servers in SanFrancisco and you lose the whole site, do you
have an automated means of redirecting the web traffic to the DR site
in Vegas?
In regards to your database solution, SQL Server 2005 - Database
Mirroring is fine for some things, however when running in the
automatic failover mode, you must use syncronous replication.
Syncronous replication across a WAN is going to kill the performance of
your primary database because it must wait for the write to commit on
the secondary server before it can commit on the primary server.
The other issue with SQL Server 2005 replication with automatic
failover is the placement of the witness server. This setup only truly
works if you have the witness server in a third location with seperate
communication paths to the primary and secondary data center.
Otherwise, with the witness server in the primary data center you will
not failover if you have a site loss (witness server is also gone).
With the witness server in the secondary data center you will have
false failovers if the WAN link goes down, because the witness server
and the secondary server will think the primary is unavailable.
If you go with SQL Server 2005 Database Mirroring, then I would forget
about the automatic failover mode and go with asyncronous replication
and a manual failover. Your other option is to go with a third party
solution such as LifeKeeper for SQL Server or one of the other ones you
mentioned.
David A. Bermingham, MCSE, MCSA:Messaging
Systems Engineer
www.steeleye.com
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> SQL Server 2005 - Database Mirroring is the most suggested mechanism for
> you. Database mirroring with a
> Witness instance supported will do a automatic failover if the Primary
> instance is down.
> See the below URL on the article on setup of database mirroring.
> http://www.microsoft.com/technet/pr...5/dbmirror.mspx
> Thanks
> Hari
> SQL Server MVP
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:%23rbl0R7wGHA.1484@.TK2MSFTNGP04.phx.gbl...

Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continue
to be live/available. The mgr believes "there should be a way using named SQL
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only have
> access to its own SQL disks/data through SSP but this could be turned off if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would continue
> to be live/available. The mgr believes "there should be a way using named SQL
> instances and DNS aliases (or local hosts files on the servers) for this to
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
> > I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> > 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> > data and logs are on RAID arrays in the storage array. The servers only have
> > access to its own SQL disks/data through SSP but this could be turned off if
> > necessary.
> > My mgr wants me to come up with adisaster recoverysolution where - if one
> > server fails - then "the remaining SQL server can access the access the
> > databases that reside on the shared array" and those databases would continue
> > to be live/available. The mgr believes "there should be a way using named SQL
> > instances and DNS aliases (or local hosts files on the servers) for this to
> > be possible".
> > To me it seems like he is looking for cluster functionality without a
> > cluster and I can't get my mind around his proposed solution.
> > Does anyone see a way to provide the functionality the mgr is seeking?
> > Thanks
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeeperworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continue
to be live/available. The mgr believes "there should be a way using named SQL
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks
"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)

> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only have
> access to its own SQL disks/data through SSP but this could be turned off if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would continue
> to be live/available. The mgr believes "there should be a way using named SQL
> instances and DNS aliases (or local hosts files on the servers) for this to
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com
|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
>
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeeperworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

Disaster recovery planning question

I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP MSA
500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
data and logs are on RAID arrays in the storage array. The servers only have
access to its own SQL disks/data through SSP but this could be turned off if
necessary.
My mgr wants me to come up with a disaster recovery solution where - if one
server fails - then "the remaining SQL server can access the access the
databases that reside on the shared array" and those databases would continu
e
to be live/available. The mgr believes "there should be a way using named SQ
L
instances and DNS aliases (or local hosts files on the servers) for this to
be possible".
To me it seems like he is looking for cluster functionality without a
cluster and I can't get my mind around his proposed solution.
Does anyone see a way to provide the functionality the mgr is seeking?
Thanks"pdx" <pdx@.discussions.microsoft.com> wrote in message
news:17E5CAFA-B1E0-4468-8CB8-7D320A46407C@.microsoft.com...
>I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP
>MSA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only
> have
> access to its own SQL disks/data through SSP but this could be turned off
> if
> necessary.
> My mgr wants me to come up with a disaster recovery solution where - if
> one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would
> continue
> to be live/available. The mgr believes "there should be a way using named
> SQL
> instances and DNS aliases (or local hosts files on the servers) for this
> to
> be possible".
If he wants automatic yeah, you probably need clustering, or really good
scripting.
BUT, what you can do manually is simply "remap" those LUNS from Server A to
Server B.
Then attach the databases.
HOWEVER, there's some caveats. If Server A fails, the databases may not be
attachable because they weren't shutdown cleanly. But, it MIGHT work.. in
which case your recovery time is minutes, not longer (however long a RESTORE
from backup would take.)
As for Instances/DNS aliases.. possible. Different ways of doing that.
Note with multiple instances, you may hit licensing issues.
So in sum, with some planning it's certainly possible.
I've done this in non-disaster circumstances (i.e. clean database shutdown,
etc.)

> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
> I have two SQL 2000 Std servers (running on W2k3 Std) connected to an HP M
SA
> 500 G2 storage array. Each SQL server runs distinct/different DBs. The SQL
> data and logs are on RAID arrays in the storage array. The servers only ha
ve
> access to its own SQL disks/data through SSP but this could be turned off
if
> necessary.
> My mgr wants me to come up with adisaster recoverysolution where - if one
> server fails - then "the remaining SQL server can access the access the
> databases that reside on the shared array" and those databases would conti
nue
> to be live/available. The mgr believes "there should be a way using named
SQL
> instances and DNS aliases (or local hosts files on the servers) for this t
o
> be possible".
> To me it seems like he is looking for cluster functionality without a
> cluster and I can't get my mind around his proposed solution.
> Does anyone see a way to provide the functionality the mgr is seeking?
> Thanks
This is definately a cluster issue as without some kind of IO fencing,
both servers can not access the shared LUN at the same time. MSCS
would do the trick here, however, since you are running SQL 2000
Standard Edition, there is no support for clustering. You can solve
this problem via a third party clustering solution, such as the one
from SteelEye Technology (my employer) called LifeKeeper Protection
Suite for Exchange. Using LifeKeeper, the way we would configure your
solution is to install a second instance of SQL on each of the servers
which would be the backup for the other server's active instance.
LifeKeeper works with either shared storage (as in your case) or
replicated storage, so your current hardware would be sufficient.
Here is a link with some more information.
http://www.steeleye.com/pdf/literat..._sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com|||On Apr 12, 1:56 pm, "daveberm" <david.berming...@.steeleye.com> wrote:
> On Apr 11, 7:42 pm, pdx <p...@.discussions.microsoft.com> wrote:
>
>
>
>
> This is definately a cluster issue as without some kind of IO fencing,
> both servers can not access the shared LUN at the same time. MSCS
> would do the trick here, however, since you are running SQL 2000
> Standard Edition, there is no support for clustering. You can solve
> this problem via a third party clustering solution, such as the one
> fromSteelEyeTechnology (my employer) calledLifeKeeperProtection
> Suite for Exchange. UsingLifeKeeper, the way we would configure your
> solution is to install a second instance of SQL on each of the servers
> which would be the backup for the other server's active instance.LifeKeepe
rworks with either shared storage (as in your case) or
> replicated storage, so your current hardware would be sufficient.
> Here is a link with some more information.
> http://www.steeleye.com/pdf/literat..._sql_server.pdf
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Managementwww.steeleye.com- Hide quoted text -
> - Show quoted text -
Sorry, LifeKeeper for SQL (not Exchange) is the product you need.

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
> >>
> >>
> >>
> >
> >

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]

Disadvantages of installing another DLL on Sql Server Box

Hi All,

We are planning to install a Visual Basic component on one of our Sql Server Box which is also the Production Box.This Component will get started at some scheduled time,do some extract,generate an output XML file and then die.This process is a non-stop process and will be running everyday.Now my question here is that what are the disadvantages/problems of installing another Component on the sql server Box(under Winnt/System32)?Won't there be any Memory problem on the Sql Server,Performance Degradation,restart of the Server and all?

Thanks!You wont have any problems with memory etc. I do similar things all the time the best solution i have found is

1) Create an ActiveX DLL with all the stuff you need.
2) Host it under MTS or COM+ (which ever makes no difference)
3) Build an NT service that calls starts the DLL.

Using this you can stop start pause or can the dll at any time also its a lot safer being managed by MTS/COM+. If you want the code for the NT service email me. (it inlcuded full details for scheduling etc) We have had this in production for 2 years and it hasnt died once.

Hope it helps.