News

All of the SQL Server instances that Pro DBA support for its clients have a Health Check report generated early each morning and emailed to the SQL Server Team’s support inbox. I thought it would be a nice idea to add a bit of functionality to check if there was a new version – e.g. Service Pack, Cumulative Update, Security Patch, etc – available and add an alert to each instance’s report to that effect. I assumed that there would be several public APIs out there that I could tap into but this turned out not to be the case.

There is the excellent Microsoft SQL Server Versions List  resource and versions can be retrieved from that resource by various means, however the site is itself listed as unofficial and the last thing I wanted to do was to have to update all our clients’ Health Checks if the resource ever moved or disappeared. And there seemed to be little else out there other than another API obsolete since about 2021 (although admitedly it wasn’t an exhaustive search). So I decided to try and do it myself.

My first idea was the simplest. The version data would be stored in an Azure SQL Database and one option would have been just to query it directly from client machines, albeit with encrypted credentials. This was straightforward to set up, however it would have meant either opening up the database to public connectivity (not recommended) or setting up a point-to-site VPN (complicated and, as far as I could determine, requiring either Azure AD authentication or certificate authentication and installing a VPN point-to-site client on each of our clients’ servers hosting the report, none of which sounded straightforward for something so basic). So the other option was to create a REST API.

Many years ago I wore a developer’s hat and so was familiar with tools like Visual Studio, however it had been quite a while since I had last used it – VS 2010 I think – so I needed to get to grips with the latest iteration. I then needed to figure out how to create a Web API app and publish it to an Azure Web app, although that turned out to be fairly straightforward and there were plenty of articles out there to help me along. Ultimately then the web app is very simple. It queries the Azure SQL Database mentioned above and returns version data in json format which, in turn, is processed by the PowerShell script that generates the Health Check report. The data consists of SQL Server version, Build Number, File Version, Description, Release Date and a link to the download – pretty much what the excellent Microsoft SQL Server Versions List mentioned above does.

This works nicely for the most part, although there are occasional issues with TLS security (I’m explictly setting TLS to v1.2 in the PowerShell script that underpins the Health Check report and that seems to get round the issue) and with proxy authentication in connecting out to the API (I’ve only come across one such problem so far and the workaround was to retrieve proxy credentials and use those when querying the API). I’m sure I will encounter other issues.

All in all it was a useful exercise, involving lots of ‘moving parts’, however I did think there would certainly have been a number of public interfaces out there, not least one provided by Microsoft, and was rather surprised that that didn’t seem to be the case. Maybe there is something out there and someone can enlighten me.

Gordon Feeney, senior Database Consultant at Pro DBA.