May 21, 2013 at 8:57 pm
Can someone help me understand cluster?
we have two nodes for the cluster. each cluster we installed two sql instances, one is prod, one is qa.
Does it mean we have total 4 instances installed, or only two instances?
Does the databases located in both of the 4 instances?
Thank
May 21, 2013 at 9:17 pm
Hi,
I think that you have logical 2instance, but physical 4 - because there are mirrored and in cluster, so you can use just one side - but this is according to me
May 21, 2013 at 9:41 pm
so when I apply service pack, it actually will do on 4 instances, correct? Thanks much
May 23, 2013 at 8:41 am
Did you physically install 4 instances (all with different names)? or did you install an instance on node A then add the instance on Node B (for both instances) which = 2 instances.
A cluster isn't a mirror and it cannot load balanced.
The SQL Services for the instances can only be in a "started" state on 1 node at a time (per instance). If you FAILOVER the instance to the second node then the services are shut down on the hosting node and started on the other node.
When you patch you will be patching 2 instances only assuming you installed 2 physical instances and added them to the second node.
May 23, 2013 at 9:23 am
In a cluster you do a normal SQL install on one node and do a "Add node" on all the other respective nodes. So, you have 2 nodes, 2 SQL instances. SQL Instance can only reside on ONE node at any given time. If your PRD instance is on Node1 that means all your database files related to that instance is on Node1. If a failover (move from Node1 to Node2) happens that clustered disk which has the database files for PRD will also failover to Node2 and now your PRD instance will be on Node2.
May 23, 2013 at 1:44 pm
Loundy (5/23/2013)
Did you physically install 4 instances (all with different names)? or did you install an instance on node A then add the instance on Node B (for both instances) which = 2 instances.A cluster isn't a mirror and it cannot load balanced.
The SQL Services for the instances can only be in a "started" state on 1 node at a time (per instance). If you FAILOVER the instance to the second node then the services are shut down on the hosting node and started on the other node.
When you patch you will be patching 2 instances only assuming you installed 2 physical instances and added them to the second node.
Actually that is a good question. I would like to know that too, the person who installed the cluster has gone, so I cannot ask. Can I see any way from the server?
Here is what I see:
I login into server node1 which I am pretty sure it is the active node, open SQL server configuration manager, I see there are two SQL instances running Prod_MSSQLServer, QA_MSSQLServer,
Then I remote login to node2, which I think it is the passive mode, in SQL configuration manager, I see two instances there, but both stopped, the instances name are exactly the same as node1 instances, Prod_MSSQLServer, QA_MSSQLServer. but they are stopped.
Now I am very confused there are 4 physical instances or 2 two instances?
Or like above answer tony28 said, it has 4 physical instances, but 2 logical instances?
Thanks
May 24, 2013 at 1:23 am
sqlfriends (5/23/2013)
Loundy (5/23/2013)
Did you physically install 4 instances (all with different names)? or did you install an instance on node A then add the instance on Node B (for both instances) which = 2 instances.A cluster isn't a mirror and it cannot load balanced.
The SQL Services for the instances can only be in a "started" state on 1 node at a time (per instance). If you FAILOVER the instance to the second node then the services are shut down on the hosting node and started on the other node.
When you patch you will be patching 2 instances only assuming you installed 2 physical instances and added them to the second node.
Actually that is a good question. I would like to know that too, the person who installed the cluster has gone, so I cannot ask. Can I see any way from the server?
Here is what I see:
I login into server node1 which I am pretty sure it is the active node, open SQL server configuration manager, I see there are two SQL instances running Prod_MSSQLServer, QA_MSSQLServer,
Then I remote login to node2, which I think it is the passive mode, in SQL configuration manager, I see two instances there, but both stopped, the instances name are exactly the same as node1 instances, Prod_MSSQLServer, QA_MSSQLServer. but they are stopped.
Now I am very confused there are 4 physical instances or 2 two instances?
Or like above answer tony28 said, it has 4 physical instances, but 2 logical instances?
Thanks
Unfortunately in this case tony28 has given you some very bad advice.
From the info you have given it sounds like you have the following
- an Active / Passive failover cluster (A/P)
- 2 SQL Instances both hosted on node1
As you haven't mentioned the OS version I'm going to assume 2008r2. If you open up "Failover Cluster Manager" (from Administrative Tools), hit the + next to the cluster name you will be presented with "services and applications", nodes, storage, networks. Expand services and applications and here you will see your 2 SQL instances. The same principals apply to windows 2003 but I can't quite remember if the options are slightly different. If you are prompted to log in then enter your cluster name.
If you click on a SQL instance under "Services and Applications" you can see the "Current Owner". This is the node of the cluster which is currently hosting your instance. You will also see the server name and IP, storage, and the actual SQL Engine Service and Agent Service.
The point of a cluster is for HIGH AVAILABILITY. In a nutshell without going into great detail this means that if one of your servers goes down then the services on the second node spring into life and start hosting the SQL Instances. When the first node goes down the shared storage (which you will see in the storage option) will be moved to the second node also, this storage contains the SQL data files / log files etc (you can have multiple disks). The SQL Services are not moved during a failover. They are spun down on the node which has been taken offline and spun up on the node which Is about to host the failover. This is why you don't see the services for the specific instance started on both nodes at the same time.
If you are now responsible for this SQL Cluster I would highly recommend reading up on failover clustering and failover cluster instances as it is vital that you understand how this works.
Hope this helps.
Chris
May 24, 2013 at 9:09 pm
I will.
Yes, I think what we have created two instances on node 1, one is for pro, th other is for qa, then we added the same two instances on node 2.
Does that mean when install the second node, He only added the node, he didn't install the two instances again with same names on node 2.
Also when applying service pack, do I need to run on both nodes even the passive one ?
This is a SQL server 2008 cluster.
Thanks
May 25, 2013 at 12:55 am
sqlfriends (5/24/2013)
I will.Yes, I think what we have created two instances on node 1, one is for pro, th other is for qa, then we added the same two instances on node 2.
Does that mean when install the second node, He only added the node, he didn't install the two instances again with same names on node 2.
Also when applying service pack, do I need to run on both nodes even the passive one ?
This is a SQL server 2008 cluster.
Thanks
Yes, when installing a cluster failover instance you install the instance on the first node, then you add it to the second node.
And yes, you have to patch both nodes. Do the passive first, then fail over your instances then patch the new passive node (this is assuming sql 2008). I believe the patching process is different for 2005 (i.e, you might have to patch on the actual active instance). - Check that first though.
May 28, 2013 at 3:18 pm
sqlfriends (5/21/2013)
Can someone help me understand cluster?we have two nodes for the cluster. each cluster we installed two sql instances, one is prod, one is qa.
Does it mean we have total 4 instances installed, or only two instances?
Does the databases located in both of the 4 instances?
Thank
To end up with 4 instances you would need to have 4 lots of resources for the following
disk storage
virtual network name
virtual IP address
clustered sql server service
clustered sql server agent service
You cannot install a clustered and a non clustered instance with the same name into a Windows cluster. So if you had the following
NODEA
NODEB
You have created the windows cluster
SQLCLUSTER01
you install the following clustered instances on NODEA
SQLCLUST01\INST1
SQLCLUST02\INST2
You cannot, on NODEB, create the following stand alone instances
NODEB\INST1
NODEB\INST2
Although you have only run the clustered instance installer on one node, when attempting to install a standalone instance on a node in the same cluster you will receive an error messagebox informing you the instance name is already in use and clustered on node x.
See attached image
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply