SQL/Server Health Checks

Recently I’ve been asked to develop a set of scripts, plans and reports packaged up as a general server health check, but also with a specific focus on ERP servers, by which we’re interpreting as the Application and Database servers (Microsoft SQL in this case).

The thing is, if you Google “Server Healthcheck” or “SQL Performance Analysis” and everything in between, you will find a large array of sites out there specifically designed for these purposes. Therefore, I am now interperting my task as “analyse the tools already available and package up a selection of the best to add value to our customers”.

As thie self proclaimed king of scripting I have already started my work on a selection of scripts to analyse and build reports on various areas of systems, from the OS level all the way through to the DB contents where required. As always I welcome feedback on things I could be using on this project.

To begin with I’m focussing on the SQL analysis, T-SQL is still fairly new to me, so I’d rather reuse what’s already out there for example Brent Ozar’s SQL Health Check (https://www.brentozar.com/archive/2017/10/free-sql-server-health-check/). I will use the intial out of the box analysis, running it across a variety of systems to see where the standard baselines need adjusting (paramaterization and parallelism anyone?) and then build my own rules and descriptions to better benefit our specific needs. Sounds fun right?

Watch this space as I develop the scripts and reports, the eventual end game would be to run 1 executable/script with a set of predefined constants (server names, user credentials etc) and have a close to complete report out the end of it. If you do know of any sites or tools out there that can help me complete this then please do get in touch @jaward916



First things first, #notadeveloper. I cannot stress this enough, I am not trained to write code, neither am I employed to do so. However, I do enjoy writing code, I find a lot of satisfaction in hitting the run button and watching something I wrote come alive. Previously I have posted many tweets and blog entries of my coding adventures over the years. My crowing achievement to date is probably the PHP/MySQL based “Asset Management” system, a glorified inventory list ability to Assign to a person, and add a list of repairs or reinstalls against the items. It automated a part of my job I disliked, and quite frankly that is exactly what I love about code. Almost all of the scripts I have written over the years have had the primary purpose of automating repetitive tasks any sysadmin can do with their eyes closed, mostly this has been silent install scripts and updaters.

Fast forward on a little from my sysadmin days, and to the brave new world (for me) of ERP. My primary day job is planning, coordinating and performing installations of ERP software into all sorts of manufacturing and distribution companies. Some are small, many are large, so the nature of, the deployments can vary slightly. That’s generally the bit I’m good at; sizing and planning the system to meet size and expectations of the end users. What we found over the last 2 years is that whilst deployments vary slightly, there is a bulk of work that is virtually the same every time round, certainly in process if not inputs, however we found that amongst the team; time, accuracy and experience could vary, significantly in some cases. Therefore a colleague of mine, with vastly more years experience in product and process went to the efforts to write an automation tool, a set of PowerShell scripts and XML files used to automate the bulk of the installation process. Roll on a few months and instantly accuracy and time were improving, which in turn was improving everyone’s experience. Gone were the days of random (user) errors and here are the days of productivity and valid errors which have much, much more context!


So let’s get techy on this and roll on a little further in time; following a few changes, ownership of the tool is now with me. And with a potentially different future ahead, it may only be a short term thing (it may also be long term!), so with this in mind, I sought help of people who know what they are doing, exceptionally smart developers in this case. After a couple of remote session the following has occurred:

Task 1 – Get the code secured. We can’t have something this crucial to our process hiding on a random VM with no backups.

Solution –  Git based code repository, in this case Visual Studio Team Services (VSTS)


Task 2 – Get the additional features into the code, but fully tested before deploying.

Solution – Branch off. Currently running with 2 branches, one for immediate fixes/quick additions, and one for next revision which will do far more than just  installing (Shhhh it’s Top Secret)


Task 3 – Get the code tidied up, to some form of best practices etc.

Solution – VSTS Build running PowerShell scripts with Pester and PowerShell Script Analyzer to validate all PowerShell scripts against a set of generally accepted best practice rules.


Task 4 – Packaging. No one wants to manually build a zip file, upload it to a SharePoint site and email out a notification for every small fix that goes in

Solution  – NuGet and Chocolatey via a VSTS Package Feed.


Since this became my problem, three versions of the tooling have been released, packaging only got tested this week so isn’t the primary deployment method yet, but now we have it as a capability there will be many more versions, but that just wont matter as they will always have whatever is the latest in the master branch!


Ok so that all explains my random tweets from evenings and weekends over the last month or so, fortunately I’ve had some incredible guidance from some very skilled and friendly development colleagues. Without those guys, I wouldn’t be anywhere with all this other than a whole load of files and folders on one machine with no backups!


I’d also like to give back to the community a little, so I plan to have some scripts that I write for more generic tasks uploaded to a public facing Git at https://github.com/jaward916 further to that I have below added a list of all the bookmarks I’ve been building up, especially the ones around Tasks 3 and 4, which has been the key functionality I’ve explored and implemented in the last week.


I stress once again, I am not a developer, please do not laugh at my code, or my very basic explanations of the tools and processes, I am learning for fun, but developing to make everyone’s lives a little easier in my world!


Bookmarks for VSTS