SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question on Multi-Instance Clustering


Question on Multi-Instance Clustering

Author
Message
nroberson
nroberson
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 128
My shop is about to set up a multi-instance (two node) cluster. I have not yet had the opportunity to work with Clustered SQL Instances yet, so I'm looking forward to it. We currently have Log Shipping and Replication for our other means of HA.

The question I have, which I haven't been able to really track down a solid answer from white papers or other forums is in regards to the activity/connections with working with the active nodes of a cluster.

With a two node (or however many nodes) cluster, is it only ONE instance that accepts client connections/statements, or will the Cluster Group use all online instances to process requests? My understanding of clustering would lead me to believe that only one instance of the cluster is accepting the requests, and the other instances are just available in case the current "active" instance goes down or stops responding.

The confusion comes from mgmt feeling that having multiple instances means you'll get a bit of a shared load of the application requests. My understanding is that SQL Server Clustering cannot provide a load balance, only a HA solution.

So long story short: Does only one instance in a multi-instance cluster do all the work for the client/application requests, or will some connections go off to the other nodes?

Thank you
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235024 Visits: 46376
Clustering is NOT load balancing.

Any instance on a cluster is active on one node only. It will be stopped on all other nodes. A multi-instance cluster is a cluster with multiple seperate instances each serving their own databases to their own applications.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nroberson
nroberson
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 128
I know that Clustering is not a load balancing solution. Unforunately, mgmt doesn't see it that way and I think I've convinced them it's not.

I will describe our set up to help clarify my question.

We will have two or three applications that will hit this production server (these are our most critical apps/systems).

So if we have a two node multi-instance (what used to be called Active/Active) Cluster; you're saying that on one instance we should have the databases for Application A. On the second node, we should have the databases for Application B / Application C.

Application A will use node 1, and Applications B & C will use node 2. If node 1 goes down, Application A will then switch over to use Node 2 until the problem is resolved and Node 1 is back online.

If you have all your databases on one node, can you only have Active/Passive Clustering?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235024 Visits: 46376
Databases don't live on nodes. They re on shared storage. The instances run on particular nodes.

Don't use the term active/passive. It's misleading and out of favour. What you are describing there is a mult-instance cluster, 2 nodes and 3 instances.
In that config, 2 instances on one node and one on the other or all three on one node. If a node fails, any instances active on it will fail over to the other one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nroberson
nroberson
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 128
Ok. Thank you very much for the clarification. My confusion was on the nodes and instances then.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56815 Visits: 17747
nroberson (8/27/2012)
Unforunately, mgmt doesn't see it that way and I think I've convinced them it's not.

Doesnt matter what they believe, point of fact sql server does not provide load balancing through clustering. It can through merge replication but its frought with issues and still doesnt represent true load distribution, maybe they're thinking of Oracle RAC


nroberson (8/27/2012)

So if we have a two node multi-instance (what used to be called Active/Active) Cluster; you're saying that on one instance we should have the databases for Application A. On the second node, we should have the databases for Application B / Application C.

You refer to an instance, the first instance, but then you refer to a node instead of a second instance. You must think t the instance level, clustering is the Windows OS technology that sits underneath.


nroberson (8/27/2012)

Application A will use node 1, and Applications B & C will use node 2. If node 1 goes down, Application A will then switch over to use Node 2 until the problem is resolved and Node 1 is back online.

If you have all your databases on one node, can you only have Active/Passive Clustering?

Again think at the instance level. Is there any reason why you are using 2 instances, different security requirements, etc?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
nroberson
nroberson
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 128
No. My issue has been I've been thinking (and my group too) from a O/S level, and not instance level. It makes a lot more sense when I get my mind off of the Windows Clustering.

I think where my manager is getting the load balancing is that you can have two nodes, and a instance on both with each of it's respected apps hitting each instance. So Application 1 is hitting instance on node A, and application 2 is hitting instance on node B. My thought is it wouldn't make a difference with it being shared disk.

Management wants two node cluster that will automatically failover with as little to no downtime as possible. So by having just one instance on Node A, something goes wrong on it, instance fails over to Node B.

Does Node B need to have an active database instance running on it for it to be online? Or can it be running without an instance until a failure?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56815 Visits: 17747
nroberson (8/29/2012)
Does Node B need to have an active database instance running on it for it to be online?
Or can it be running without an instance until a failure?

The node just needs to be powered on and part of the Windows cluster and obviously have the sql instance software installed to be able to failover to the partner node.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235024 Visits: 46376
nroberson (8/29/2012)
Does Node B need to have an active database instance running on it for it to be online? Or can it be running without an instance until a failure?


It can't have instance 1 active on it until failover.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Daxesh Patel
Daxesh Patel
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 1047
I am just trying to understand this post, are you looking for something like this?

Node1______________________Node2_____________________________
Instance1(active)____________Instance1(failover)____---This is for Application A
Instance2(failover)___________Instance2(Active)_____---This is for Application B & C
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search