Thursday, January 21, 2016

SSMSBoost - A Productivity Tool

SSMSBoost Tips and Trips    (http://www.ssmsboost.com)

Today I typed up a little "tips and tricks" document for the various teams I work with and figured heck, why not share it with everyone?  As you can see, I had to obfuscate some of the database/connection information with white boxes.  Hope the tips come in handy!

Tip 1: Locate Object – when you have hundreds or sometimes thousands of tables to comb through, this feature makes it very simple to locate an object with a couple clicks.  Simply hover over the object (normally a table), right-click and click “Locate object in Object Explorer”.  All the menus will expand and send you right to the object.

 


Tip 2: Easily switch connections – I often switch between connections, which natively is a little cumbersome (Right-click > Connection > Change Connection, etc.).  With SSMSBoost, you can define your Preferred Connections.  For each connection, as you can see below you can also choose a default database that it will connect to rather than master by default.














The color coding is nice as well.  I choose to set all production servers to have a red Status Bar (see Tip 1 image).  That way I know when I’m connected to production and when I’m not.  Once you have configured your Preferred Connections, you’ll note the Connections drop-down in the SSMSBoost ribbon will contain them all for quick switching between instances:












Tip 3: Fatal Actions Guard – Let’s face it – we’ve all likely ran a query and forgot to put a where clause, or perhaps you clicked F5 to truncate a table in dev… BUT you were in production.  At least this feature gives you one last chance to change your mind if you really didn’t mean it.














Preview of the popup that will appear upon execution:


















Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever.  You end up saving the query results to Excel and about five minutes later you have the totals, etc.  With SSMSBoost, you can have your totals in seconds.  Below I’m selecting 10 records from a sales related table.  Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE.  All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:




Tip 5: Format SQL – Not every developer has a tSQL formatting tool.  SSMSBoost does come with one if you want to use it.  Your teams could all configure the tool for the same sort of formatting for consistency sake.

 


Tip 6: History – How many times have you wanted to retrieve a query from a few days ago?  Maybe SSMS crashed, or you wanted to revert back to another version.  You can customize the History feature to suit your needs.  In the example below I searched for UPDATE – I know I ran an update a few days ago, but I don’t recall the exact syntax.  Not a problem:






Friday, October 16, 2015

Custom Backup Exec SSRS Report

I'm not a Backup Exec administrator, but recently one of my co-workers wanted a custom SSRS report to track our backups, which also allows him to receive this report on a regular basis via a subscription.  This is what I came up with - perhaps you too will find it useful.

Query:
SELECT  CASE TaskTypeID
          WHEN 200 THEN 'Backup'
          WHEN 300 THEN 'Duplicate'
        END AS TaskType
       ,JobName
       ,ActualStartTime
       ,EndTime
       ,(ElapsedTimeSeconds / 3600.0) AS ElapsedTimeHours
       ,CASE FinalJobStatus
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Success w/ Exception'
          WHEN THEN 'Canceled'
          WHEN 19 THEN 'Success'
          ELSE 'Unknown'
        END AS FinalJobStatus
       ,TargetName
       ,MediaSetName
       ,(TotalDataSizeBytes / 1024.0 / 1024.0 / 1024.0) AS TotalDataSizeGB
       ,TotalNumberOfDirectories
       ,FinalErrorDescription
FROM    BEDB.dbo.vwJobHistorySummary
WHERE   TaskTypeID IN (200, 300)
        AND ActualStartTime > DATEADD(MONTH, -1, GETDATE())
ORDER BY ActualStartTime DESC

RDL: click here.

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.

New! I have added a Severity Level drop-down to allow you to just review servers/workstations that only require critical updates.  I've also added a Type drop-down so you can choose between Servers and Workstations (you will have to get some data from the database for this - review/modify the Dataset as needed).  I've also added a total count of devices and count of those requiring critical updates, along with a percentage - management often inquires on those high-level sorts of statistics.

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