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.
Setup Step-by-Step
Step 1: Create new database objects
This script will create a new table called SQLVersions and a stored procedure called UpdateVersions.
The UpdateVersions stored procedure will try to use the clr_http_request method.
So, if you want to avoid the security risk inherent in enabling OLE Automation procedures, download and install the clr_http_request stored procedure in the same database.
NOTE: If the script doesn't find the CLR procedure, it'll fall back to using the OLE Automation stored procedures.
Next, we'll need to create a scheduled job to periodically run the UpdateVersions stored procedure. Let's say, once a day.
Step 2: Create a new view inside the SentryOne database
This view should simplify for us the task of matching each SQL Server target and its current build, with its major version and its latest released build.
Note that this view also filters the records by hiding from us the SQL targets that are already up-to-date, and those where the latest released version is too old (1 year ago or more) or too recent (up to 1 month ago).
Step 3: Create a new SQL Sentry advisory condition
Alright, so now we have something that periodically checks the latest build per each SQL Server major version, and a view that helps us find the SQL targets that are outdated.
Now we need to create an Advisory Condition in SQL Sentry which would detect for each SQL Server target whether it needs to be updated.
It should be as easy as creating a SentryOne Database Query predicate with the following query:
SELECT MessageText, DaysSinceRelease FROM dbo.SQLBuildVersionCheck
WHERE DeviceID = @ComputerID
Click here to download the Advisory Condition file SQL Server Version Update.condition which you can easily import into your own SentryOne repository.
Step 4: Add Actions to the new Advisory Condition
If we don't add any actions to our newly created condition, it's probably not going to help anyone, right?
Step 5: Configure the Response Ruleset
We probably want to avoid too much spam from our new alert.
We can do that by configuring a Response Ruleset for our newly created Action. For example:
Conclusion
That's it!
You now have a fully automated mechanism to alert you when your SQL Server target(s) become outdated and need to get patched up.
Obviously, you don't really have to be limited to SentryOne. Once you have a table automatically updated with the latest SQL Server build version(s), and you have any kind of solution that's sampling or storing your current SQL Server build versions, then it should be piece of cake to connect the two and automatically generate alerts.
See Also
The DbaTools Powershell module has useful cmdlets Test-DbaBuild and Update-DbaInstance that can help with implementing a similar automated solution.
Are you interested in a few more extra advisory conditions for SQL Sentry?
Check out these advisory conditions that we've submitted to SentryOne's GitHub repository:
Comments