x

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?

more ▼

asked Oct 16, 2009 at 08:49 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Oct 16, 2009 at 11:13 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x306
x93
x64
x57
x20

asked: Oct 16, 2009 at 08:49 PM

Seen: 3288 times

Last Updated: Oct 17, 2009 at 07:27 AM