question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

Pros and cons of HA solutions

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?

replicationclusterlog-shippingdatabase-mirroringhigh-availability
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jay Bonk avatar image
Jay Bonk answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.