High-Availibility: Database Mirroring or Clustering?

What to consider to choose between database mirroring and clustering to manage high-availibility? what are benefits of each method over the other?

more ▼

asked Oct 19, 2009 at 06:17 AM in Default

anwarchandra gravatar image

25 2 2 3

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

6 answers: sort newest

Both techniques are not intended for loadbalancing but for high availability.

With mirroring you can create a second copy of a database on another server. You cannot access this database directly, you have to create a (read only) snapshot of this mirror. Pro: easy to set up Con: resource intensive, setup per database

If you're going for clustering then you have to have Cluster (identical at least) hardware. You need to have knowledge about MS Clustering, be a pro on Windows and SQL. Then you can load balance between two different databases. DB1 is active on Node1, DB2 is active on Node2. Pro: failover for a server, not per database Con: Difficult to setup/manage.

more ▼

answered Oct 19, 2009 at 07:11 AM

JP de Jong gravatar image

JP de Jong

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

answered Jul 28, 2011 at 10:08 AM

speedster gravatar image


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

I sort of agree with JP de Jong. The only way you could load balance the two database on separate nodes would be to install two instances of SQL Server on both nodes and have each of the databases on separate instances. You'd also want to be sure that both nodes could support both databases should one node were to crash.

more ▼

answered Feb 23, 2010 at 08:07 PM

Lynn Pettis gravatar image

Lynn Pettis
275 3

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

If you are concerned with load balancing, one of the main techniques is to replicate data, primarily read data, to other servers. Then you can read from multiple servers, write to one.

If you need to write often, you can potentially separate out sets of data to different servers and have the application determine which one to send the update to.

more ▼

answered Oct 19, 2009 at 10:45 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

thanks Steve, I'm also concerned with load balancing. but I have changed this question to discuss high-availibility (I'm still getting used with both terms: high-availibility, load-balancing ).

So I created another question about replication here: http://ask.sqlservercentral.com/questions/439/replication-changing-the-primary-server-automatically-if-fail
Oct 19, 2009 at 11:19 AM anwarchandra
(comments are locked)
10|1200 characters needed characters left

Agree with JP de Jong - these are not solutions for scalability. Can you change your question to indicate what you really want to know. Do you want a comparsion of mirroring vs clustering for HA, or do you want to know how to load-balance a database?

This MSDN link discusses scaling out SQL2005, as does this brief article on SSC

more ▼

answered Oct 19, 2009 at 09:16 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

thanks. I have changed the question to High-availibility.
Oct 19, 2009 at 10:51 AM anwarchandra
(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



Answers and Comments

SQL Server Central

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



asked: Oct 19, 2009 at 06:17 AM

Seen: 6034 times

Last Updated: Oct 19, 2009 at 10:45 AM