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

Tuesday, October 21, 2014

Consolidate your Reports with Dynamic Data Sources

I love writing SSRS reports that assist me in consuming data across my instances.  In some cases I set up subscriptions to my reports so I don't forget to review the data periodically.  But how many times have you created a really nice report and made a copy of it so you could have it query data from another instance?  I was modifying a report today and since I had three copies (one for three different instances), I set out to find a better solution.

If you write reports, you know that when you create a data source, it is tied to an instance of SQL Server.  If you want your report to be able to query more than one data source, you will want to create what is called a "dynamic data source".  Here is one way you can go about doing that.  Note that this solution is tailored around the fact that I have databases in each of my instances with the same schema to harvest performance related data, for example, but you might have a report that queries a DMV - that would be another use case.

Create Shared Dataset
The first thing I'm going to start with is a blank report.  I'm then going to create a Shared Dataset in my Visual Studio Solution called CMSInstances.  Creating a shared dataset allows you to use it in all the reports in your solution.  I prefixed it with CMS because I'm going to get a list of instances from my Central Management Server (CMS).  If you are not using a CMS, you should be!  But if that were the case you would simply specify the parameter values individually, keeping in mind that if you add/remove instances - you will have to edit every report!










Configure the shared dataset to connect to your CMS instance and paste the following query in the query window:

SELECT  g.[name] + ' - ' + s.[name] AS [SQLAlias]
       ,[server_name] AS [InstanceName]
FROM    [msdb].[dbo].[sysmanagement_shared_server_groups_internal] g
        INNER JOIN [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s ON g.[server_group_id] = s.[server_group_id]
WHERE   g.[server_type] = 0
        AND g.[is_system_object] = 0
ORDER BY [SQLAlias]

Create your Data Source
Now that we have our shared dataset created, we can go to our report and add it as a data source.  In the Report Data window, right-click Data Sources and add a new one.  Call it "DynamicDataSource". Next, in the embedded connections section click the expression button.





Enter the following expression:
="Data Source=" & Parameters!InstanceName.Value

Create your Report's Dataset
Because we created the shared dataset prior, we can use that dataset over and over in any report we author.  To add that shared dataset to this report, simply right-click Datasets, give it a Name and select the shared dataset.


Create your Parameter
Now that you've created the reports dataset to populate your instance pull-down, we can create the Parameter.  Right-click on Parameters and Add Parameter.  For Name type in InstanceName and Prompt just type in Instance.  Next, click on Available Values and configure it as follows:
























You're all done!
You should now be able to Preview the report and your Instance pull-down will be populated with all the instances configured in your CMS.  If you group your servers by environment, you can further refine the query as needed.  Any queries using this data source would obviously have to be drawing their data from similar databases and table names.




Thursday, July 17, 2014

Using Replication to keep your Admin Scripts Current

We all have handy scripts that we want present in our SQL Server database instances.  Examples would include sp_WhoIsActive, sp_Blitz, sp_BlitzCache, Ola's maintenance scripts or custom scripts like Estimated Time to Complete.  But how do you keep all your instances updated when new versions are released?  You certainly don't want to run the updated scripts on each instance one-by-one, especially if you manage quite a few instances.  One way to keep them in sync is by using a Central Management Server (CMS) and running the updated script on multiple instances at once.  However, what if there are some instances that you don't really want or need to deploy the scripts to?  That was my situation, so I opted to use SQL Server Replication to keep my scripts in sync.

I personally have an instance that I call my "UTILITY" instance.  I use this instance as my CMS.  It's also where I store all my monitoring databases like Spotlight, utility databases like ClearTrace and most importantly - my central DBADB.  I have a DBADB in every instance I administer.  It's a place to keep all your DBA related scripts and tables (maintenance logging tables, sp_BlitzCache output, harvested wait stat data, etc.) and you can have peace of mind that nobody else is going to go in there and mess with them.  I then created a publication on my DBADB, selected only the stored procedures that I want to deploy and set up subscriptions to all the target instances.  With just a few clicks of the mouse, I was able to deploy out the latest and greatest version of all my administrative scripts.

These are the ones I personally replicate out - if you have other useful scripts, feel free to share!


Wednesday, June 18, 2014

Reporting on sp_BlitzCache Results

Regularly examining the plan cache in your database instances is a must, as there is a wealth of useful information that helps you determine which statements and/or stored procedures are your biggest performance problems.  Doing such analysis has become much easier with the release of sp_BlitzCache by Brent Ozar Unlimited.  One of the great features of sp_BlitzCache is the fact that you can log the results to a table, allowing for more effective analysis of the results.  I've decided to take it one step further and put together an SSRS report to help dive into the data.  Get the Report RDL.

To harvest the data, I created a scheduled job in SQL Server which is set to run nightly at 7pm.  I keep all of my utility scripts and data in a database called DBADB.  Each instance has its own such database.

EXEC DBADB.dbo.sp_BlitzCache
@top='200',
@results = 'expert',
@output_database_name = 'DBADB',
        @output_schema_name = 'dbo',
@output_table_name = 'BlitzCache'

The report I wrote is based off expert mode results.  There is a plethora of data in the report so it does get fairly wide.  I don't display the values in QueryText or QueryPlan on the report, but in Figure 3 you'll note I provided the syntax to obtain the query plan and/or SQL text in the header.  Copy in the plan_handle or sql_handle, respectively.  Another option is to directly query your BlitzCache table for it.

There are four parameters on the report: Database Name, Sample Time, Query Type and Warnings.  Most column headers allow you to sort so you can zero in on a particular performance metric of concern.  

Figure 1 - Parameters












Figure 2 - Report Data - these are the first set of columns in the report: