none
Memory Allocation for Multi-instance SQL DB

    Question

  • Hi All,

    I have installed a 2-node multi-instance (active-active) SQL failover cluster.

    Here is the setup details:

    1. Server1 (Node1) physical memory of is 64GB. Running Web_Instance01.

    2. Server1 (Node2) physical memory of is 64GB. Running Web_Instance02.

    I allocated 58GB maximum memory for each instance and the rest (6GB) is for Operating System.

    Question:

    If a failover happens and the 2 instances runs on the same server (example: Node1), does it mean that we require 116GB RAM on the Node1?

    Anyone have setup similar environment? Do I need to manually configure each instance to allocate RAM after the failover?

    Appreciate if anyone can point me to any links or resources regarding this.

    Thanks in advance.

    Regards,

    Ivan

    Tuesday, May 14, 2013 6:51 AM

Answers

All replies

  • Hi,

    We have a similar setup, albeit with 384GB RAM in each node.  We didn't want to "waste" 200GB because both instances might be on one node at some point (hasn't happened in a year).  Aaron Bertrand has a script that can be used to re-balance memory (or other resources) when the cluster fails over.  http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/18/managing-active-active-cluster-failovers-with-different-hardware.aspx

    I wrote something bespoke that checks what drives are currently visible on the node (given that D: should always be on 1 node, and H: should always be on the other), and when they're on the same node, set MAXSERVERMEMORY appropriately on both instances.

    Also, see http://blogs.msdn.com/b/pamitt/archive/2010/11/06/sql-server-clustering-best-practices.aspx



    Thanks, Andrew

    Tuesday, May 14, 2013 10:57 AM
  • Hi Andrew,

    Thanks for the reply.

    Great. I'll check on the link and try to test the script on my test environment.

    Did you in any way blog the script you have created that sets the max memory once it detects that the instance are on the same node? :)

    Any cons if I don't set some kind of script to re-balance the memory when cluster fails other than you need to do it manually and you cannot auto configure in a way the max memory setting if it say failure happens at 3AM?

    Thanks again.

    Regards,

    Ivan


    Wednesday, May 15, 2013 9:44 AM
  • Hi Ivan,

    You might get memory related problems if you have 2 instances with a combined max memory setting greater than that available in the machine.

    I don't have a blog, but you can have a copy of the script I wrote.  It's not universal, i.e. it was written specifically for our environment, but it shouldn't be too difficult to make it work for you (given that you only have 2 instances/2 nodes)... in fact, you probably just have to change the drive letters over and put in linked server details.  You might also want to play with how much RAM is left over after failover - change the bit:

     / 1024 / 1024) / 2) * 0.9

    Once you have it set up, just create a SQL Server Agent job that runs at startup to execute this procedure.

    USE [master]
    GO
    
    /****** Object:  StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster]    Script Date: 05/13/2013 16:12:36 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Rebalance_RAM_in_Cluster]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
    GO
    
    USE [master]
    GO
    
    /****** Object:  StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster]    Script Date: 05/13/2013 16:12:36 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
    AS
    
    --WAITFOR DELAY '00:00:15'
    
    DECLARE @Command VARCHAR(2000)
    DECLARE @RAM INT
    DECLARE @RAM_event NVARCHAR(10)
    DECLARE @Link VARCHAR(50)
    DECLARE @RemSQL VARCHAR(500)
    
    /*
    AUTHOR:		ANDREW BAINBRIDGE - MS SQL DBA
    DATE:		13/05/2013
    VERSION:	2.0
    
    If there is a cluster failover that results in both SQL Server instances running on the same node, this script will
    automatically rebalance the amount of RAM allocated to each instance.  This is to prevent the combined RAM allocated to 
    SQL Server overwhelming the node.
    
    If the D: drive and the H: drive are visible to the same host, that means that both
    instances are running on the same node.  In this event, the amount of RAM allocated to each of the SQL Servers
    will be 90% of half the amount of total RAM in the server.  E.g. (384GB / 2) * 0.9
    
    If only the D: drive or H: drive is visible, then 90% of the total amount of RAM available on the server is allocated
    to the SQL Server instance.
    
    This stored procedure will also set the max server memory of the other SQL Server instance in the cluster.  As this needs
    to be run across the linked server, and the sp_procoption startup procedure is owned by SA (therefore can't use windows 
    authentication), the stored procedure will be run on SQL Server Agent startup, via a job.
    
    
    */
    
    SET NOCOUNT ON;
    
    BEGIN
    
    	IF (SELECT @@SERVERNAME) = 'MYSERVER\INSTANCE'
    		SET @Link = 'LINKED_SERVER_TO_OTHER_NODE'  
    			ELSE
    		SET @Link = 'LINKED_SERVER_TO_MYSERVER\INSTANCE'
    
    	
    	SET @Command = 'USE [master];
    						EXEC sp_configure ''show advanced options'', 1;
    						RECONFIGURE WITH OVERRIDE;
    						EXEC sp_configure ''max server memory (MB)'', $;
    						RECONFIGURE WITH OVERRIDE;'
    
    	IF OBJECT_ID('tempdb..#fd') IS NOT NULL
    		DROP TABLE #fd
    	CREATE TABLE #fd(drive CHAR(2), MBfree INT)
    	INSERT INTO #fd EXEC xp_fixeddrives
    
    	IF (SELECT COUNT(drive) FROM #fd WHERE drive IN ('D', 'H')) > 1
    		BEGIN
    			SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024) / 2) * 0.9) AS RAM_in_MB 
    						FROM master.sys.dm_os_sys_info)
    			SET @Command = REPLACE(@Command, '$', @RAM)
    			SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
    			RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
    			EXEC (@Command)
    			SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
    			EXEC (@RemSQL)
    		END
    		
    	ELSE
    		BEGIN
    			SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024)) * 0.9) AS RAM_in_MB 
    						FROM master.sys.dm_os_sys_info)
    			SET @Command = REPLACE(@Command, '$', @RAM)
    			SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
    			RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
    			EXEC(@Command)
    			SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
    			EXEC (@RemSQL)
    	END
    END
    
    GO



    Thanks, Andrew



    Wednesday, May 15, 2013 9:59 AM