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

cluster Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 8:57 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1455284
Posted Tuesday, May 21, 2013 9:17 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 175, Visits: 513
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
Post #1455287
Posted Tuesday, May 21, 2013 9:41 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,776, Visits: 3,222
so when I apply service pack, it actually will do on 4 instances, correct? Thanks much
Post #1455292
Posted Thursday, May 23, 2013 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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.


There's no kill switch on awesome!
Post #1456038
Posted Thursday, May 23, 2013 9:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 21, Visits: 99
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.
Post #1456052
Posted Thursday, May 23, 2013 1:44 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1456206
Posted Friday, May 24, 2013 1:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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


There's no kill switch on awesome!
Post #1456311
Posted Friday, May 24, 2013 9:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1456759
Posted Saturday, May 25, 2013 12:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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.


There's no kill switch on awesome!
Post #1456767
Posted Tuesday, May 28, 2013 3:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,358, Visits: 13,688
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"


  Post Attachments 
install stand alone in cluster error.jpg (6 views, 134.60 KB)
Post #1457531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse