August 27, 2012 at 9:57 am
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
August 27, 2012 at 10:13 am
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
August 27, 2012 at 10:20 am
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?
August 27, 2012 at 10:33 am
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
August 27, 2012 at 12:10 pm
Ok. Thank you very much for the clarification. My confusion was on the nodes and instances then.
August 28, 2012 at 4:20 pm
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" 😉
August 29, 2012 at 7:43 am
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?
August 29, 2012 at 7:50 am
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" 😉
August 29, 2012 at 9:17 am
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
August 29, 2012 at 9:58 am
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
August 29, 2012 at 12:16 pm
Correct.
August 29, 2012 at 12:46 pm
I do not have experience of clustering but following posts of HA to gain some knowledge. So please let other expert validate my answer.
Conceptually this looks possible.
Only thing needs to be considered is managing memory in case of failover.
Here is a good article you may want to consider
August 29, 2012 at 2:31 pm
For this configuration ensure your nodes are well specced to handle both instances in the event of a failover
Node 1
Instance 1 active for Application A
Instance 2 passive for Applications B and C
Node 2
Instance 1 passive for Application A
Instance 2 active for Applications B and C
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply