Wednesday, August 26, 2015

Custom System Center Reports for Windows Updates

Chances are if you've used System Center (SCCM) you'll find that the reports are less than adequate for keeping tabs on software updates in your environment.  Because of that, I decided to author a couple of Reporting Services (SSRS) reports to inform me which servers/computers (I'll use "server(s)" the rest of the way) require how many updates, along with the ability to click in and get more details as to which updates those are.  On the summary report you can search for servers containing certain characters - if you have a lot of servers this comes in handy.  You can then click on the server name to get more details.  On the detailed report, I decided to color code the severity and provide a direct link to the Microsoft KB.

Click here to get the RDL for the summary report.  Here is a preview:


Click here to get the RDL for the summary report.  Here is a preview:


Happy patching!

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!