Updated: Jul 26
Following the recent acquisition of SentryOne by SolarWinds, I’ve decided to write a few special blog posts dedicated to our favorite SQL server monitoring platform.
Click here if you missed my previous post: Common issues during SQL Sentry version upgrades.
As part of the managed DBA service that Madeira data solutions provides, we make extensive use of the SQL Sentry monitoring and alerting platform. As such, we’ve gathered significant experience in using, managing, and maintaining the platform.
This also includes utilizing the platform to do all kinds of "unorthodox" monitoring, which is not available "out-of-the-box".
Keeping your SQL Server estate up-to-date is an important task for us DBAs. But, unfortunately, something that's not quite as easy or straight-forward to do automatically.
In today's post, we'll create a solution to automatically alert us when a SQL Server monitored by SQL Sentry becomes outdated.
Publicly Available Source of Truth
When setting out to automate this operation for our managed DBA service, we had to look for a publicly available web reference of the latest SQL Server version updates, which could be utilized in automation.
There are several places online that might be used as reference points for newly released SQL Server versions, such as:
Out of these, the sqlserverbuilds.blogspot.com website proved to be the most consistent in its structure, and therefore, the easiest to utilize in automation.
Luckily, a similar solution was already developed in T-SQL a few years ago by Dirceu Resende.
It utilizes OLE Automation stored procedures in order to retrieve the list of the latest SQL versions from sqlserverbuilds.blogspot.com , and compare it against the local SQL instance.
This is an excellent solution, but it has a few shortcomings:
It was meant to be run by a SQL Agent job on each SQL instance and monitor for its specific (local) build version. Not easy to implement and maintain for an environment with lots of SQL Servers. But should be easy to adapt.
In order to retrieve the list of versions from the web, it uses OLE Automation stored procedures, which is a significant security risk. A safer alternative should be used, such as CLR.
Dirceu's publicly available T-SQL script is a bit outdated, and cannot parse the current version of the website.
Modernizing Dirceu's Script
First, instead of comparing the latest version against the local SQL build version, we would need to save the latest SQL build version per each SQL major version in a table (which could be located in the SentryOne database, for example).
Instead of OLE Automation stored procedures, we are to replace it with a signed CLR method.
Make the necessary changes in the script in order to adapt it to the modern version of the SQL Server Builds website.
After we have all the piping ready, then we'd be able to integrate it with the SentryOne monitoring platform and automate it.
Step 1: Create new database objects
This script will create a new table called SQLVersions and a stored procedure called UpdateVersions.