What are the pros and cons of using the various high availability solutions for SQL Server? I know of
- clustering
- database mirroring
- replication
- log shipping
Are there other choices?
asked
Pros and cons of HA solutions
Clustering is the most expensive, requiring special hardware and a shared disk. Failover for clustering is at the Server Level.
Mirroring, (In High Availability (Synchronous)) will also support automatic failover - however this is at the database level and not the server level.
Log Shipping will ship or send the log files to a warm standby server that could be brought online in the event that the primary server failed - though this would be a manual failover (or custom code) and not an automatic failover. Replication is similar to Log shipping, where the data from the publishing database is sent over to the subscriber.
Clustering and Mirroring, give the benefit of automatic failover, but the failover databases are not accessible. Log Shipping and Replication would allow you to scale out and utilize the secondary databases for read only queries or reporting, but don't natively support automatic failover.
Two articles where the comparison is detailed much more than the high level generalization I posted would be:
http://www.sqlservercentral.com/articles/Disaster+Recovery/sqlserver2005highavailability/2421/
(Take note of the OP as well as the author of the above article ....)
And
http://technet.microsoft.com/en-us/library/cc917680.aspx#XSLTsection130121120120
