Thursday, August 18, 2016

VMware Host Performance and Configuration Reports

So you are a DBA and you are in a virtual environment - VMware in particular.  You are curious to know the health of the VMware hosts in terms of CPU and RAM, but you really don't know how to get the data you need and you're not certain if the information you are asking for is entirely accurate.  Well, chances are you have access to the VMware databases themselves - if that is the case, you can create these reports based on a blog post from Jonathan Kehayias: "Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information".

I have created five reports that are based on Jonathan's queries and you can download the RDL for the SSRS reports below - enjoy!

Host Configuration
CPU Ready Values (Daily)
CPU Ready Values (Weekly)
Memory Ballooned (Daily)
Memory Ballooned (Weekly)

Hopefully you find these useful,
Allen (@SQLTrooper)

Thursday, January 21, 2016

SSMSBoost - A Productivity Tool

SSMSBoost Tips and Trips    (

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.

          WHEN 200 THEN 'Backup'
          WHEN 300 THEN 'Duplicate'
        END AS TaskType
       ,(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
       ,(TotalDataSizeBytes / 1024.0 / 1024.0 / 1024.0) AS TotalDataSizeGB
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: