question

anwarchandra avatar image
anwarchandra asked

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?

clusterdatabase-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.

JP de Jong avatar image
JP de Jong answered

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.

10 |1200

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

Kev Riley avatar image
Kev Riley answered

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

10 |1200

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

Steve Jones - Editor avatar image
Steve Jones - Editor answered

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.

10 |1200

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

Lynn Pettis avatar image
Lynn Pettis answered

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.

10 |1200

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

speedster avatar image
speedster answered
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.