SQL Server 2005: Clustering FAQ is Active/Active a load balancing solution?

Another FAQ within newsgroups & forums that will SQL Server Clustering supports load balancing?

It is quite common for a newbie to say and look at Clustering configuration such as Active-Active where you have n+1 nodes as a serving instances, the only difference between Active-Passive configuration is having 2(+n) active nodes at a time.

By default and documentations stresses the point that Microsoft  Cluster Services is not a load-balancing product, it is simply a high-availability solution. All you have to go for a hardware based or software based load balancing product to addup to the clustering environment. The term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label.  Though you have independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication and so on. But when it comes to the failover of these instances  for all intents and purposes as 2 seperate instances running on seperate servers at all times with only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server. Reference  about load balancing information are as follows:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

 http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

Few times I have seen couple of environment setup as load balancing (not in real terms) that will have to balance the load (connections) between the two nodes while they are both up, thought I have had discussion and show proofs to the team who setup such a configuration that  referred “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails. This will have a downtime whenever a node fails and spending awful lot of money on Clustering you are not achieving what it supposed to do.

What is usually promoted now a days is the N + 1 configuration. (N active nodes with 1 spare node)  For example, if you had an eight node cluster, you could have seven nodes set to actively provide different services and one node in standby mode should any of the seven nodes fail and this article on SSP talks about such a scalability achievement. The better advantage you would get from Windows/SQL Server clustering, with 2 scenarios. In the first scenario; a 2-node cluster:  in which the 2 servers work either in tandem or to back each other up.  To get good performance you keep both the servers running, and one takes over when one of them takes a nose dive. In the other case you don’t get good performance because only one of your nodes is up at a time.  Another failure at this point will take your entire system down. On the other scenario you can have 4 servers at a time, where performance-wise you are getting 3 servers and 1 failure also with a second failure renders the Cluster useless.

When it comes to the Clustering on a large scale environment SAN involvement is compulsory and in this case not to mention about shared disks/SAN goes down, it takes down every node. This is a weakest link on having public connections via HUB/Switch to the application, that will also cause a downtime to your internal system where you would have to use a single array of shared drives or a SAN. In this regard a few counters on SAN & Clustered related blogs:

SAN with SQL Server: What to look from SQL Server perspective

Is RAID5 better for performance when SQL Server Clustering and SAN is involved?

SQL Server Clustering resources and help

Best practices on deploying SQL Server 2005 on SAN 

Lastly not to mention about costing involved in setting up Clustering with SAN, when you have properly set it up you will achieve High Availability solution with a flavour of Scalability for you SQL Server application databases. The main lead you will get for slow performance is bad practices, table fragmentation and bad code too.