DR Server using replication

Hi,

I am planning on setting up disaster recovery configuration. It is planned to use transactional replication from the production server to the DR server. Are there any recommendations for replicating changes back to the production server, once it is back online. I suppose that it would be safest to move the database back the other server over a weekend or during downtime. But I am just wondering whether anyone has experience of other practical solutions.

Many thanks
DavidDid you look into log shipping?|||Replication is a poor choice for Disaster Recovery. The cost of maintaining the replication is much higher than using Log Shipping or a home grown solution similar to Log Shipping.

The first questions that need to be answered by the owner of the budget are:
1. How much data can you afford to lose?
2. How much (business hours) downtime can be tolerated?
3. How much money are you willing to spend on Disaster Recovery?

You gotta put numbers on the cost/benefit or you'll be rolling boulders uphill for a long, long time.

Unfortunately, typical, thoughtless, knee-jerk answers are:
1. none
2. 1 minute
3. $9.95

A better approach may be to put together a menu of options to go along w/ the questions above as well as other relevant questions:
A1. Lose no more than 5 minutes of transactions, recover within 20 minutes at a cost of $40K + $10K per subsequent years.
B2. Lose no more than 60 minutes of transactions, recover within 4 hours at a cost of $20K + $5K per subsequent years.
C3. Lose no more than one day of data and recover within 4 hours at a cost of $1K + $1K per subsequent years.

Ninety percent of failures are caused by humans - e.g. accidentally Delete w/o a Where clause. Those are the types of failures from which you need to focus on recovering. For example, your DR/backup server should intentionally be kept out of sync. by however much time is needed to detect and respond to a human error: 4 hours works well.

Finally, unless you are willing to determine every failure permutation and the steps needed to recover from each, the answer to your first question is: stop production, figure out what is missing and copy the data using the most convenient, familiar approach you have.|||Sybase has a replication server product that does exactly what you need. It can replicate many database vendor's data including MSSQL.

http://www.sybase.com/products/informationmanagement/replicationserver|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks|||Why can't companies understand DR is not the same is High Availability? Too bad you can't cluster your production server and use log shipping to a DR solution.

I'm not very familiar with replication but if you need to get changes back to the original production server shouldn't you use merge replication?|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks

Did you look at the Sybase solution? What you are really looking at is Warm Standby, which replicates (transactionally) all of the changes from the "active" connection to the warm standby connection. The repserver is aware which connection is the "active" and which is the "warm standby" at all times. When you switch them, replication flows the other way automatically.