Best Hot/Warm Backup Server Replication Strategy (SQL Server 2005)

Question

I have two identical servers with SQL Server 2005 and my application.

Hard requirements:

1. I must be able to update data at either server.
2. I must be able to unplug either server without having to reconfigure anything in the database.
3. When a server is plugged back in, it must automatically sync up with the other server.

Notes:

1. I prefer options that would not add significant load to the primary server if possible.
2. The two servers have a private network for replication, so bandwidth is not an issue.
3. It is ok for the data to be a few minutes out-of-date on either server.

From what I've read my options are:

• Transactional Replication with Updatable Subscriptions (Queued updating)
• Merge Replication

Which configuration best fits my requirements?

Answer 1

Have you considered Log Shipping?

I don't think that can easily be set up so that the Warm-standby can take over automatically, so some manual effort involved in making it the Primary.

And it will only be as good as the most recently received log - but you could set up to ship logs every minute or so.

If you have to have the Standby 100% up to date then you need a solution that syncs each transaction - which will be a Distributed Commit.

But if you are going to ship the Standby by FedEx and could force a process (i.e. to ship the "final" log) before it powered down that should work; or if its just disconnected, FedEx'd, and then comes back "online" the Log Shipping should just resume from where it left off; then when you make it the Primary it will be as "fresh" as the most recent log it has received.

Answer 2

Since I can't comment on the first answer, not being registered, I will comment on it here. Mirroring can work without any intervention if you setup synchronous with a witness. The witness is a third sql server instance that automatically switches principal/mirror when it detects an outage. Also, depending on the amount of data, you could take the mirror out for a few days and when you return it, and the witness detects it, it will automatically beging to re-sync. In addition, depending on your application, you can create a sql connection string that will also automatically detect if the principal is down.

Answer 3

None of the current options allow for having both servers being writeable. Pretty much your only option is going to be merge replication as this allows for updates to both servers.

However merge replication is the hardest to setup and get running. You will need to make sure that the distributor has enough drive space on it to ensure that the distributor won't run out of space the entire time that one of the servers is down.

Log shipping and Mirroring don't allow for updating the secondary server.