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

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.