Tuesday, November 18, 2014

Dynamically Adjust SQL Server Failover Cluster Instance Memory

I recently installed a third FCI (Failover Cluster Instance) on a three-node WSFC (Windows Server Failover Cluster), meaning each FCI will have it's own node - assuming all nodes are online.  It's worth noting this is a non-production cluster - I would normally have an N+1 configuration in production, meaning I have a spare node at all times and typically would not have to worry about running more than one FCI on a single node.  But in this case it is quite possible to be running two instances on a single node, or the rare case where all three instances are on a single node.  When that happens, you'll want to adjust the memory in each instance so they can co-exist on the same node while you address which ever node is offline (patching, other failure, etc.).

All that said, I found a blog post authored by Aaron Bertrand (b|t) where he dynamically adjusted memory for two instances that have different hardware, whereas in my case I have three identical virtual machines.  Therefore I wanted to take some of his existing logic and tailor it to my situation.  While I'm pretty certain I could have made it more efficient, the goal was to keep it very easy to read and maintain moving forward.

Some keys to remember: you will have to create linked servers to each instance and you will make this procedure a startup procedure.  Also worth noting is that each of my nodes have 32GB of RAM with 28GB assigned to SQL Server.


EXEC [master].dbo.sp_procoption 
   'dbo.BalanceMemory', 
   'startup', 
   'true'

USE [master]
GO

CREATE PROCEDURE [dbo].[BalanceMemory]
AS
    BEGIN
        SET NOCOUNT ON;
   
        DECLARE @ThisNode NVARCHAR(255)
           ,@ThisInstance NVARCHAR(255)
           ,@OtherNode2 NVARCHAR(255)
           ,@OtherNode3 NVARCHAR(255)
           ,@command NVARCHAR(MAX)
           ,@run NVARCHAR(MAX)
           ,@LoneMemory VARCHAR(5) = 28672
           ,@SharedMemory2 VARCHAR(5) = 14336
           ,@SharedMemory3 VARCHAR(5) = 9216
           
        SELECT  @ThisNode = CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
               ,@ThisInstance = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
               ,@command = 'USE [master];
                                      EXEC sp_configure ''show advanced options'', 1;
                                      RECONFIGURE WITH OVERRIDE;
                                      EXEC sp_configure ''max server memory (MB)'', $;
                                      RECONFIGURE WITH OVERRIDE;
                                      EXEC sp_configure ''show advanced options'', 0;
                                      RECONFIGURE WITH OVERRIDE;';

        IF @ThisInstance = 'SQL01'
            BEGIN
                EXEC [SERVER02\SQL02].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))', N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
                EXEC [SERVER\SQL03].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))', N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

                IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
                    BEGIN -- ALL THREE ON SAME NODE!
                        SET @run = REPLACE(@command, '$', @SharedMemory3);
                        EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                        EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                    END
                ELSE
                    IF @ThisNode = @OtherNode2
                        BEGIN -- TWO ON SAME NODE
                            SET @run = REPLACE(@command, '$', @SharedMemory2);
                         EXEC [master].dbo.sp_executesql @run;
                            EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;

                            SET @run = REPLACE(@command, '$', @LoneMemory);
                            EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                        END
                    ELSE
                        IF @ThisNode = @OtherNode3
                            BEGIN -- TWO ON SAME NODE
                                SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;

SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                            END
                        ELSE
                            BEGIN -- EACH ON OWN NODE
                                SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                                EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                            END
            END

        IF @ThisInstance = 'SQL02'
            BEGIN
                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
                    N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
                EXEC [SERVER03\SQL03].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
                    N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

                IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
                    BEGIN -- ALL THREE ON SAME NODE!
                        SET @run = REPLACE(@command, '$', @SharedMemory3);
                        EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                        EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                    END
                ELSE
                    IF @ThisNode = @OtherNode2
                        BEGIN -- TWO ON SAME NODE
                            SET @run = REPLACE(@command, '$', @SharedMemory2);
                         EXEC [master].dbo.sp_executesql @run;
                            EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;

                            SET @run = REPLACE(@command, '$', @LoneMemory);
                            EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                        END
                    ELSE
                        IF @ThisNode = @OtherNode3
                            BEGIN -- TWO ON SAME NODE
                                SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;

                                SET @run = REPLACE(@command, '$', @LoneMemory);
                                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                            END
                        ELSE
                            BEGIN -- EACH ON OWN NODE
                                SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                                EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
                            END
            END

        IF @ThisInstance = 'SQL03'
            BEGIN
                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
                    N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
                EXEC [SERVER02\SQL02].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
                    N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

                IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
                    BEGIN -- ALL THREE ON SAME NODE!
                        SET @run = REPLACE(@command, '$', @SharedMemory3);
                        EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                        EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                    END
                ELSE
                    IF @ThisNode = @OtherNode2
                        BEGIN -- TWO ON SAME NODE
                            SET @run = REPLACE(@command, '$', @SharedMemory2);
                         EXEC [master].dbo.sp_executesql @run;
                            EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;

                            SET @run = REPLACE(@command, '$', @LoneMemory);
                            EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                        END
                    ELSE
                        IF @ThisNode = @OtherNode3
                            BEGIN -- TWO ON SAME NODE
                                SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;

                                SET @run = REPLACE(@command, '$', @LoneMemory);
                                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                            END
                        ELSE
                            BEGIN -- EACH ON OWN NODE
                                SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
                                EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
                                EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
                            END
            END
    END