Mirror or Repl.: failsafe & disaster recovery vs. maintain..

Hello,

We are trying put together a disaster recovery plan for our production SQL
databases including our ERP database and some other dbs.

We have 3 SQL Servers all running SQL 2005 Standard. 2 servers are
production servers and a third server can be utilized for a backup/mirror
server. Each production server will be housing an ERP SQL db for a different
company and additional SQL databases that support those companies.

We are trying to use backup software from EMC called Retrospect. I
personally don't have any experience with this product. Any feedback is
greatly appreciated. If Retrospect doesn't buy us much, I'd like to know now
and use the built in SQL backup and restore utilities or look for another
third party option if need be.

My real question is which option should we go with: Mirroring or Log
Shipping to give us the best shot at having a near real-time copy of our
mission critical DBs and allow us to get back up and running if we were to
have a catastrophic event occur on our SQL Servers? We can live with manual
failover if needed. What is important is that the data be in tact and near
realtime.

We are planning on doing a full backup of all dbs at midnight and
transaction log backups every hour or 30 minutes. What we are are unsure of
is will mirroring and/or log shipping conflict with either of these options
and what is best/easiest to implement & maintain?

From what I have read, log shipping transaction log backups would need to be
used rather than the transaction log backups taken by something like our EMC
Retrospect software. That basically we would need to incorporate the
archiving of the transaction log backups into our disaster recovery routine.

Regarding mirroring...we have SQL 2005 Std. and therefore "High Performance"
mode is not an option. We are worried that the default mode might slow down
our production system waiting on the transactions to commit on the mirrored
secondary. Any insight?

Also, our ERP databases have to come down at midnight and during occasional
other scheduled and unscheduled times for maintenance and such. What does
that downtime do to our mirroring or log shipping implementations? Is is a
pain to manage all that stuff on an ongoing basis?

Another issue: our ERP system can't run against a copy of the ERP SQL db
running on another server so auto failsafe isn't necessary or even useful.

Any insight is greatly appreciated.


--
Josh Blair (hfdev)
HydraForce, Inc.

This is one of the gotcha's with database mirroring and high safety.

The impact of the split right is minimized by having long running
transactions and multiple connections as opposed to a single one.

With short running transactions the effect of the split write and network
hop can double or even quadruple their latency.

This is all a function of your underlying network.

I don't know anything about Retrospect but have used RepliStor and
RecoverPoint (both by EMC). RecoverPoint is great!

Basically the choices between Log Shipping and Database Mirroring boil down
to what your tolerance to data loss is. With Database Mirroring it can be 0
or very small. With LogShipping it will be 1-2 minutes with a practical limit
of 5 minutes.

Database mirroring has no distance limitation assuming you have a high speed
stable network and ideally are using the high performance mode. Database
mirroring can also be difficult to recover from when things go wrong.

Database mirroring can be hard if the amount of logging you do is beyond
what your network can support. Some people can't even mirror a single
database.

Log Shipping tends to be unwieldy the large you get.


"hfdev" wrote:

> Hello,
>
> We are trying put together a disaster recovery plan for our production SQL
> databases including our ERP database and some other dbs.
>
> We have 3 SQL Servers all running SQL 2005 Standard. 2 servers are
> production servers and a third server can be utilized for a backup/mirror
> server. Each production server will be housing an ERP SQL db for a different
> company and additional SQL databases that support those companies.
>
> We are trying to use backup software from EMC called Retrospect. I
> personally don't have any experience with this product. Any feedback is
> greatly appreciated. If Retrospect doesn't buy us much, I'd like to know now
> and use the built in SQL backup and restore utilities or look for another
> third party option if need be.
>
> My real question is which option should we go with: Mirroring or Log
> Shipping to give us the best shot at having a near real-time copy of our
> mission critical DBs and allow us to get back up and running if we were to
> have a catastrophic event occur on our SQL Servers? We can live with manual
> failover if needed. What is important is that the data be in tact and near
> realtime.
>
> We are planning on doing a full backup of all dbs at midnight and
> transaction log backups every hour or 30 minutes. What we are are unsure of
> is will mirroring and/or log shipping conflict with either of these options
> and what is best/easiest to implement & maintain?
>
> From what I have read, log shipping transaction log backups would need to be
> used rather than the transaction log backups taken by something like our EMC
> Retrospect software. That basically we would need to incorporate the
> archiving of the transaction log backups into our disaster recovery routine.
>
> Regarding mirroring...we have SQL 2005 Std. and therefore "High Performance"
> mode is not an option. We are worried that the default mode might slow down
> our production system waiting on the transactions to commit on the mirrored
> secondary. Any insight?
>
> Also, our ERP databases have to come down at midnight and during occasional
> other scheduled and unscheduled times for maintenance and such. What does
> that downtime do to our mirroring or log shipping implementations? Is is a
> pain to manage all that stuff on an ongoing basis?
>
> Another issue: our ERP system can't run against a copy of the ERP SQL db
> running on another server so auto failsafe isn't necessary or even useful.
>
> Any insight is greatly appreciated.
>
>
> --
> Josh Blair (hfdev)
> HydraForce, Inc.