A 2 node cluster is there so that one node can take the load in case of failover.But what is the purpose of more than 2 node cluster like SQL Server 2008 enterprise edition supports 16 nodes . Can we load balance the traffic with mode than two nodes in any way.As far I know we can only set preffered nodes only and that is something far from granular load balancing.What advantage we can gain by more than 2 nodes.Any links,ideas will be helpful
asked Jan 24, 2012 at 12:07 PM in Default
I run several multi-instance, multi-node clusters. The big advantage here is utilization of resources from a hardware perspective.
Clusters are really to provide you with High Availability (and in certain circumstances Disaster Recovery however there are limitations prior to SQL 2012 around that). I like to work with N+1 cluster nodes. This allows me to dedicate an individual node to a particular SQL instance and provide a failover node that could take over for that single instance in the event that one of the nodes goes down. Now if two nodes were to fail I would be a in position whereby I would be still up and running however at reduced performance.
A two node cluster with a single instance means that you have 50% of your resources wasted. A five node cluster with four instances means that you are now only wasting 20% of your resources.
For me it makes a lot of sense to go with more nodes/instances in a cluster.
answered Jan 24, 2012 at 01:15 PM
The way that I have heard about it being used is that for more than 2 nodes in a cluster, you can use one node as a failover node for many other nodes. For example, if you have 5 nodes:
1 could fail to 5 2 could fail to 5 3 could fail to 5 and so on...
I think the configuration is really up to the way you want to cycle with them.
I have not done one myself, but I am curious as to how others may be using it.
answered Jan 24, 2012 at 12:18 PM
You could load balance things for one database (I've never tried this and I think it would be a huge pain, and would most likely involve some type of merge replication), however clustering isn't really meant for load balancing in a sense, rather it's a way to provide high availability in the event of a failure. However, You do get some natural load balancing (kind of) because the databases are on different hardware/disks, so you do see some of that.
For example, you have can an active/active/passive node cluster where the two active nodes have various different databases running on them and in the event of one of the failing, it would either fail over to the other active node or the passive node, depending on your business requirements and/or how it's configured.
In my opinion, having more than 2 nodes helps to spread out the risk of failure. If you only have a 2 node cluster, if one fails, you only have one option to fail over to, the passive node (or active if you configure it that way) whereas if you have multiple nodes you have some options. So if you had 2 heavy duty mega-critical databases, you could put one on each of the active nodes and if one node failed over to the passive node, the other node wouldn't be affected.
Of course, more nodes means more cost. More hardware, more disk for the SAN, etc.
Hope this helps!
answered Jan 24, 2012 at 12:29 PM
Case study by Microsoft where company created a 16-node cluster:
answered Jan 24, 2012 at 01:20 PM
Thanks Folks, i got
answered Jan 25, 2012 at 05:50 AM