I need to replicate data from primary server A to another server B. Write data to A and read from both servers.
So If A fail, how can I automatically use B as primary server (to write and distribute data) ? and If server A up again, I need to synchronize the data with server B and use A as primary again.
What's the best practice to do this?
There are a few ways to do this and they all have pros and cons.
In SQL Server 2005 and above, database mirroring is a good solution. It is easy to set up and if you use the SNAC client, it will automatically redirect clients to the second server. You can read more about it in Books Online: Database Mirroring Overview
Clustering will help you deal with hardware failures on one server by moving things to another one, but it requires shared storage and doesn't necessarily protect from disk issues. Clustering Overview.
Log Shipping works similar to mirroring, and works with all versions. However it doesnt' automatically fail over. Log Shipping Overview
Replication can work, but not automatic failover.
You could potentially use DNS to fail over clients, but the delays would depend on your TTL. If you have control of the application, you can set up your own failover. If the first server is not alive, then have clients connect to a second server.
answered Oct 19 '09 at 11:26 AM
Steve Jones - Editor ♦♦