Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question on Multi-Instance Clustering Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 9:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 27, 2013 2:06 PM
Points: 16, 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
Post #1350461
Posted Monday, August 27, 2012 10:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1350469
Posted Monday, August 27, 2012 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 27, 2013 2:06 PM
Points: 16, 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?
Post #1350472
Posted Monday, August 27, 2012 10:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1350480
Posted Monday, August 27, 2012 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 27, 2013 2:06 PM
Points: 16, Visits: 128
Ok. Thank you very much for the clarification. My confusion was on the nodes and instances then.

Post #1350516
Posted Tuesday, August 28, 2012 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 6,518, Visits: 14,039
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"
Post #1351316
Posted Wednesday, August 29, 2012 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 27, 2013 2:06 PM
Points: 16, 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?
Post #1351622
Posted Wednesday, August 29, 2012 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 6,518, Visits: 14,039
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"
Post #1351631
Posted Wednesday, August 29, 2012 9:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1351700
Posted Wednesday, August 29, 2012 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:12 AM
Points: 114, Visits: 997
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
Post #1351741
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse