Cluster with more than 2 nodes

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

more ▼

asked Jan 24, 2012 at 12:07 PM in Default

avatar image

211 18 19 25

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

6 answers: sort voted first

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.

more ▼

answered Jan 24, 2012 at 01:15 PM

avatar image

4.9k 4 5

SirSQL that is fine with five node cluster and 4 instances.Is there any scenerio when we need more than two say 4 nodes in a single instance.

Jan 24, 2012 at 01:57 PM inder

If you are only going to run a single instance of SQL Server and want to cluster it I would recommend going with two nodes (except in unusual circumstances).

Jan 24, 2012 at 02:02 PM SirSQL

2 nodes are fine.Is there any scenerio when we need more than two say 4 nodes in a single instance.

Jan 24, 2012 at 02:16 PM inder

There are situations where you may. For example you want to perform local clustering for HA and add a remote node to provide you with DR capabilities.

Jan 24, 2012 at 03:14 PM SirSQL

Thanks SirSQL, This was the answer I was looking for.Thanks a lot.

Jan 25, 2012 at 05:50 AM inder
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 24, 2012 at 12:18 PM

avatar image

Chris shaw
530 3 5 9

You may also have the other nodes offsite as well as part of your DR plan.

Jan 24, 2012 at 12:22 PM Shawn_Melton

@Shawn_Melton: +1 for the DR reference.

Jan 24, 2012 at 12:30 PM JohnM

Still I think there might be some big reason for 16 node support by MS.As Chris told for more than one one instance and participation of a node in more than one nodes for failover ,that's fine.It's a good topology. My concern is why one need to have more tahn 2nodes in a single instance of cluster ?

Jan 24, 2012 at 01:10 PM inder

You wouldn't.

See @SirSQL response for why...

Jan 24, 2012 at 01:55 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered Jan 24, 2012 at 12:29 PM

avatar image

12.3k 3 7 14

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

Case study by Microsoft where company created a 16-node cluster:
"Avanade consolidated its six-node SQL Server cluster and four standalone SQL Server instances into one 16-node virtual SQL Server cluster." http://www.microsoft.com/casestudies/case_study_detail.aspx?casestudyid=4000006429

more ▼

answered Jan 24, 2012 at 01:20 PM

avatar image

6.4k 21 25 34

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

Thanks Folks, i got

more ▼

answered Jan 25, 2012 at 05:50 AM

avatar image

211 18 19 25

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

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: Jan 24, 2012 at 12:07 PM

Seen: 15102 times

Last Updated: Jan 13, 2015 at 05:37 PM

Copyright 2016 Redgate Software. Privacy Policy