What to consider to choose between database mirroring and clustering to manage high-availibility? what are benefits of each method over the other?
asked
High-Availibility: Database Mirroring or Clustering?
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.
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
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.
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.
Check out this SQL HA/ Load balancing solution from Citrix NetScaler DataStream(TM)
http://www.citrix.com/English/ps2/products/subfeature.asp?contentID=2309522 also some more useful blogs
http://blogs.citrix.com/2011/07/24/mirror-mirror-on-the-wall-whos-the-best-sql-server-of-all/
http://blogs.citrix.com/2011/07/15/db-sharding-with-netscaler-datastream/
