Wednesday, November 19, 2014

Work Smarter, Not Harder - The UTILITY Instance

If your a SQL Server DBA, chances are you've walked into environments that were pretty disorganized to say the least.  Nobody seems to have a complete list of SQL Server instances, the instances are not configured consistently, database recovery models and growth rates are all over the board, the master database is muddied up with old versions of scripts and other objects - the list is endless.  And it's a nightmare to administer.

I've come up with an organizational approach that has worked very well for me over the years.  The heart of the organizational effort starts by having a centralized administrative instance.  I personally call this instance my "\UTILITY" instance.  It acts as the Central Management Server, is used for Policy Based Management, is the master server (MSX) for common SQL Agent jobs, houses the core DBADB (which every instance I administer has) which in turn is where I keep my favorite administrative scripts, and finally it's where I keep my monitoring and utility databases.  It's important to note that I push out my administrative scripts to all instances using replication.  If a new version comes out, I simply update the central version and reinitialize all subscriber instances so they too receive a fresh, updated copy.

If you have questions and/or feedback on this approach, I would love to hear it!  I'm always looking to work smarter, not harder!

Here is a graphical representation of my \UTILITY instance's responsibilities:


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