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 123»»»

Multiple instances on a cluster? Expand / Collapse
Author
Message
Posted Monday, July 20, 2009 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 6,601, Visits: 8,902
I'm looking at installing a 2nd instance of sql 2005 on a cluster. Since I haven't done this before, I started researching how, and what to be careful of.

Searching on google boiled down to either don't do it, or have each instance on it's own separate pair of nodes.

Searching thru microsoft.com reveals pretty much the opposite. I pretty much derive from there that the only things to be aware of is that the drives for the sql need to be separate from the other instance, and the sharing of memory (restrict each instance to an allowable portion of the total memory).

With what I've read at microsoft.com, I'm leaning towards setting up an active-active cluster on two nodes, where each of the nodes is running one of the sql instances.

Does anyone have any experience in setting up a multiple-instance of sql 2005 on a cluster? How did you do it? Is there anything else to be aware of in the installation and/or configurations?

Thanks!


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #755994
Posted Monday, July 20, 2009 12:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
I too am looking for some good guidance on this..

CEWII
Post #756075
Posted Monday, July 20, 2009 3:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
Hi Wayne,

In our cluster environment we are currently having around 5 instances running on a single cluster node. You can always have multiple instances on cluster node if you don't have enough harware and other resources. But multiples instances on a single cluster node is not usually recommended. Each SQL Server instance has its own SQL Server resource group which contains dedicated network name, I.P Address, Disk resources and SQL Server services.

When you have multiple instances on a single cluster node, there will be a huge competition among the SQL Server instances for the system resources. In that case you must setup the maximum memory for each SQL Server instance. Usually its recommended to leave 2-3 GB of the total available memory for the O.S and the rest to be shared among the instances. Also, its not advisable to run any other applications on that cluster.

Here is a very good article on the memeory management if you have multiple instances.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299068_mem1,00.html

Another major problem with this multiple instances approach is that suppose if you have 5 instances on node A and 5 instances on node B and if node A fails, then all the 10 instance will be running on a single node in which case you will have a huge and drastic performance hit. So you should always design a cluster with failover scenario in mind. i.e if you have 32 GB of RAM on node A, then it should be shared amoung the 10 cluster instances and the O.S in case of a failover. Clearly, this is a very poor design. I am telling this only to give you a picture of the process.

The installation is pretty much similar to installing multiple instances on a stand alone system. But during the installation, you should have an unique and dedicated Virtual name(Network name), I.P Address and disk resources for each of the SQL Server instances. Also, all these resources will be part of the resource group and at any time only one SQL Server instance can own that resource group i.e those resources in the resource group cannot be shared among multiple instances.

Thank You,

Best Regards,
SQL Buddy.

Post #756199
Posted Tuesday, July 21, 2009 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 6,601, Visits: 8,902
Hi SqlBuddy,
But multiples instances on a single cluster node is not usually recommended.

Do you happen to have any microsoft (preferred, or any other) links / white papers saying this? and why it's not recommended?

As far as supporting all instances on one node... these are all "pre-prod" databases, not heavily hit. I'm only talking about 2 total instances. I don't think that this will really be an issue.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #756473
Posted Tuesday, July 21, 2009 8:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
Hi Wayne,

Here is an article which says that it's not recommended to have more than one instance installed on a single cluster node.

http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx

Since you are saying that you have just 2 instances and that too being pre-prod databases, I stongly believe that you should not have any problems or issues with that implementation. To let you know, we have both Development and Production clusters with multiple instances sitting on each cluster node.

Thank You,

Best Regards,
SQLBuddy.

Post #756662
Posted Wednesday, July 22, 2009 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:01 AM
Points: 914, Visits: 1,431
The Books Online have a lot of information on this topic.

We've been running ACTIVE/ACTIVE clustering for years and even have a cluster with SQL2005 and SQL2000. It's totally supported by Microsoft.

When running an ACTIVE/ACTIVE cluster what you have to always keep in mind during the design is one day both INSTANCES may indeed run on one node because of a failover, so you must design the cluster and configure SQL Server with that in mind. Will you have enough resources to run on one node? You may need to purchase more RAM to meet the needs of both instances.

As mentioned above, you'll want to set MAX_SERVER_MEMORY to half of total memory for each instance, therefore when failover occurs you're not running into memory pressure between the two instances.




Post #757349
Posted Thursday, July 23, 2009 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,369, Visits: 9,896
sqlbuddy123 (7/21/2009)
Hi Wayne,

Here is an article which says that it's not recommended to have more than one instance installed on a single cluster node.

http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx

Since you are saying that you have just 2 instances and that too being pre-prod databases, I stongly believe that you should not have any problems or issues with that implementation. To let you know, we have both Development and Production clusters with multiple instances sitting on each cluster node.

Thank You,

Best Regards,
SQLBuddy.



I don't agree with everything it says in that article, especially since it doesn't make the case for why it recommends what it does. What I do agree with is that you should do plenty of planning.... and if you come to the conclusion that your resources are sufficient to support your desired configuration, then go ahead and implement it.

John
Post #758226
Posted Thursday, July 23, 2009 9:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:18 PM
Points: 285, Visits: 212
We're running 2 instances on each node in a cluster (and I wish we weren't). Between trying to balance the disk space (no SAN either) and the memory, its a PITA. They won't give us enough memory to run things the way they should be, so I'm trying to write some procs that will rebalance memory on the fly in the event of a failover. The main thing I need to figure out is how to get the physical server that the instance is running on. So far, all I can get is the virtual server name, which doesn't tell me what I need. Anybody know how to get the actual physical server name? If I can get that, I'm pretty sure I can utilize more than half the memory on each node.


Post #758342
Posted Thursday, July 23, 2009 9:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:01 AM
Points: 914, Visits: 1,431
Is this a two node cluster? Just to understand, you're running 4 instances, two per node. If that's the case then you probably would have each server instance at 25% memory. You would really need some resources in this case, as you said.

How are you planning to rebalance on they fly? You're going to run "sp_configure" after the failover to set "max_server_memory"?

"select * from sys.dm_os_cluster_nodes" will get you the node names but not if the instance is running on it.



Post #758390
Posted Thursday, July 23, 2009 11:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:18 PM
Points: 285, Visits: 212
Steve (7/23/2009)
Is this a two node cluster? Just to understand, you're running 4 instances, two per node. If that's the case then you probably would have each server instance at 25% memory. You would really need some resources in this case, as you said.

How are you planning to rebalance on they fly? You're going to run "sp_configure" after the failover to set "max_server_memory"?

"select * from sys.dm_os_cluster_nodes" will get you the node names but not if the instance is running on it.


That's the config.

That was my thought on failover anyway. I figured I can set a proc to run on startup that will check where the instance is vs. where it should be and query the other instances as to where they are and rebalance accordingly, probably with a table to tell it what to set itself to.



Post #758448
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse