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