Multiple instances on a cluster?

  • 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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I too am looking for some good guidance on this..

    CEWII

  • 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.

  • 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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • Look up the SERVERPROPERTY function in Books Online. One of the options tells you which physical node you're running on.

    John

  • If anybody's interested, I figured out how to rebalance the memory on a cluster. I can use almost all the memory on each node, and still handle a failover. I've got a master table that tells each instance where it should be running and how much memory it can use. When a failover occurs, each instance checks the table, and if any instance is not running on its "home" node, all instances will rebalance themselves so that a single node can run all of them. Once all instances are back on their home node, they will all go back to full memory utilization.

  • I'm curious. Please post.

  • Hi Mdevore,

    We are eager to listen!

    Whenever you share your knowledge with others, your knowledge will increase by manifold.

    Thats really a great virtue! Keep it up!

    Yours

    SQLBuddy

  • This takes one table in master, two stored procedures, and one job that is set to execute on startup and calls the first stored procedure ([master].[dbo].[auto_MemoryReconfigure]). I set the default memory to use all but 3 gb on each node. That allows enough for the OS, and for a failover to start. Once the first failover is detected, the memory on all instances gets rebalanced to use in total only what each server has. You will also need linked servers set up on each instance in the cluster to all other instances on the cluster. This written for a 2-node cluster, but could be used as a framework for more than that. If you've got a memory-constrained cluster like I do, it REALLY helps out.

    Table Definition:

    CREATE TABLE [dbo].[ClusterMemory](

    [Instance] [varchar](50) NOT NULL,

    [DefaultNode] [varchar](50) NOT NULL,

    [DefaultMemory] [int] NOT NULL,

    [FailoverMemory] [int] NOT NULL,

    [Failed] [bit] NOT NULL,

    CONSTRAINT [PK_ClusterMemory] PRIMARY KEY CLUSTERED

    (

    [Instance] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    ALTER TABLE [dbo].[ClusterMemory] ADD CONSTRAINT [DF_ClusterMemory_Failed] DEFAULT ((0)) FOR [Failed]

    This procedure should be automatically executed at startup, either automatically (I couldn't get it to work right) or by a job that executes at Agent startup.

    ALTER proc [dbo].[auto_MemoryReconfigure]

    as

    SET ANSI_NULLS ON

    SET ANSI_WARNINGS ON

    /****************************************************************************/

    /* Procedure to reconfigure memory on a clustered instance. */

    /* Procedure will automatically execute at startup by a job. */

    /* Expects: No Parameters */

    /* Requires table master.dbo.ClusterMemory */

    /* Calls stored procedure master.dbo.MemoryReconfigure */

    /* */

    /* Written by: Murray DeVore */

    /* Date Written:07/29/2009 */

    /* Revision Log */

    /* Revised By Date Comments */

    /* ------------- ---------- --------------------------------------------*/

    /****************************************************************************/

    declare @InstanceName as nvarchar(128)

    declare @ServerName as nvarchar(128)

    declare @DefaultNode as nvarchar(128)

    declare @MemoryToUse as int

    declare @MemoryInUse as int

    set @InstanceName = (select @@SERVERNAME)

    set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))

    set @DefaultNode = (select DefaultNode from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

    set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')

    /*Check to see if instance is running where it is supposed to and set memory accordingly*/

    IF @DefaultNode = @ServerName

    BEGIN

    set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

    UPDATE [].master.dbo.ClusterMemory SET Failed = 'False' WHERE Instance = @InstanceName

    print 'Default Node'

    END

    ELSE

    BEGIN

    set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

    UPDATE [].master.dbo.ClusterMemory SET Failed = 'True' WHERE Instance = @InstanceName

    print 'Fail Node'

    END

    IF @MemoryInUse @MemoryToUse

    BEGIN

    exec sp_configure 'max server memory (MB)',@MemoryToUse

    RECONFIGURE

    print @InstanceName+' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

    END

    ELSE print @InstanceName+' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

    /*Send email/page alert */

    declare @sub as varchar(100)

    declare @msg as varchar(1000)

    declare @to as varchar(100)

    set @msg = 'Instance '+@InstanceName+' has restarted and is now running on node '+@ServerName+'. Its default node is '+@DefaultNode+'.'

    SET @to = (select pager_address from msdb..sysoperators where name = 'SQL Server DBA')

    --set @to = (select email_address from msdb..sysoperators where name = 'SQL Server DBA')

    set @sub = 'WARNING: Instance '+@InstanceName+' has restarted.'

    exec msdb.dbo.sp_send_dbmail @recipients = @to

    , @subject = @sub

    , @body = @msg

    /*Tell the other instances on the cluster to check themselves*/

    DECLARE @sql varchar(1000)

    DECLARE curInstance CURSOR

    READ_ONLY

    FOR select 'EXEC '+QUOTENAME(Instance)+'.master.dbo.MemoryReconfigure' from [].master.dbo.ClusterMemory where Instance @InstanceName

    OPEN curInstance

    FETCH NEXT FROM curInstance INTO @sql

    WHILE (@@fetch_status -1)

    BEGIN

    IF (@@fetch_status -2)

    BEGIN

    exec (@sql)

    END

    FETCH NEXT FROM curInstance INTO @sql

    END

    CLOSE curInstance

    DEALLOCATE curInstance

    This stored proc is called by the first one, only on each of the other instances on the cluster.

    ALTER proc [dbo].[MemoryReconfigure]

    as

    SET ANSI_NULLS ON

    SET ANSI_WARNINGS ON

    /****************************************************************************/

    /* Procedure to reconfigure memory on a clustered instance. */

    /* Procedure is called remotely by auto_MemoryReconfigure */

    /* Expects: No Parameters */

    /* Requires table master.dbo.ClusterMemory */

    /* */

    /* Written by: Murray DeVore */

    /* Date Written:07/29/2009 */

    /* Revision Log */

    /* Revised By Date Comments */

    /* ------------- ---------- --------------------------------------------*/

    /****************************************************************************/

    declare @InstanceName as nvarchar(128)

    declare @ServerName as nvarchar(128)

    declare @DefaultNode as nvarchar(128)

    declare @MemoryToUse as int

    declare @MemoryInUse as int

    declare @FailedCount as int

    set @InstanceName = (select @@SERVERNAME)

    set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))

    set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')

    set @FailedCount = (select count(Failed) from [].master.dbo.ClusterMemory WHERE Failed = 'True')

    IF @FailedCount = 0

    /* Make sure memory is set to default allocation */

    BEGIN

    set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

    END

    ELSE

    /* Failover condition, reduced memory allocation */

    BEGIN

    set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)

    END

    IF @MemoryInUse @MemoryToUse

    BEGIN

    exec sp_configure 'max server memory (MB)',@MemoryToUse

    RECONFIGURE

    print @InstanceName+ ' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

    END

    ELSE print @InstanceName+ ' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply