Keeping SQL Server instances patched can be a time-consuming task for DBAs. In this article, Alejandro Cobar explains how he created a service in Azure that anyone can use to retrieve the build information for SQL Server.

During my time as a professional DBA, I have been responsible for quite a few SQL Server instances, all with their own set of characteristics, including different versions and patching levels, the topic of this article.

One of the primary duties that DBAs have is making sure that the SQL Server instances are patched and up to date, as Microsoft makes available all the corresponding Service Packs and/or Cumulative Updates. This responsibility, of course, applies for the on-premises deployments of SQL Server. Microsoft manages most database services for you in Azure, however, you will still be responsible for keeping Azure Virtual Machines up to date.

With this said, how can you make sure that your set of SQL Server instances are patched up to the latest available release by Microsoft? One obvious way is to manually check the current releases of SQL Server and compare that list against what you currently have, making a note of the current status of your environment and pointing out any differences. However, doing this can consume a lot of time, especially if you have quite a handful of instances to check.

I found that I wanted to know if my instances were up to date, but, eventually, I didn’t have enough time on my hands to be constantly checking for the available updates. I previously created and tried a solution that consumed and parsed a website to gather the information about the SQL Server build numbers. However, I decided to put that solution to rest because I realized that I don’t want to depend on the availability of an external site to keep things working fine for me.

After failing at many attempts to find a solution to automate this effort, I decided to build a public service that can surely help any SQL Server DBA fulfil this important duty.

Choosing the technology to build the public service

After several hours of thinking, I chose Azure (a solid decision by the way) by combining two of their “serverless” offerings, to help me reduce the overall costs. This article in no way is a deep dive into the technologies picked, so with that out of the way, let me explain why I picked Azure Serverless Functions and Azure SQL Database Serverless.

One of my first options was to spin a Virtual Machine, install a web server, a database, point a custom domain to the public IP assigned to the Virtual machine, and develop the service. However, by going this route, even if there’s no activity going on in the server, you still have to pay a minimum amount for the storage and virtual network assigned to your Virtual Machine.

With the serverless options, you can get a cost-effective and very convenient solution simply by paying for it when your stuff is really used.

Azure SQL Database Serverless

Nowadays, there’s an offering from Microsoft, for your Database-as-a-Service solution, called serverless. A convenient feature from this option is that, if your database hasn’t been used for a continuous amount of time (1hr is the minimum you can pick, up to 7 days), then it will auto-pause itself and, you guessed it, you will only be charged for the storage assigned to your database. Under normal conditions, Microsoft charges for the storage and compute resources used by your Azure SQL Database.

There is one important detail that should be kept in mind, and it is the fact that if your database is in a paused state, and a request tries to hit the database, then it will require some time (usually it’s several seconds) for it to “wake up” and serve the request. Therefore, there might be times where it seems that your service is slow, but it is very likely that it is just the database “waking up”. You can find more information here.

Azure Serverless Functions

Azure functions are an excellent option for quickly developing microservices, without worrying about the underlying infrastructure that powers them to run; hence the addition of the term serverless. It doesn’t mean that it doesn’t require a server to run behind the scenes. There are different service plans and configurations for your functions, but the convenient part for me is that there are free grants covered by month, and so far, I haven’t spent a single dime in Azure functions.

You can find more information here.

Details and usage of the public service

Before detailing the structure and usage of the service, I would like to express one important fact, and it is that, as of the time of this writing, the usage of this public service is entirely free for the end-user. I am personally financing the resources described in Azure (even if it’s a tiny bit currently) and will continue to do so for the foreseeable future unless something prevents me from doing so.

To consume the service, you have to issue an HTTP request, either through a web browser or programmatically through a script, in order to get the json response with the data. As of this writing, there is no restriction upon who can consume this service; however, this can eventually change if any maliciousness is detected, such as trying to bring the service down.

NOTE: You have to be 100% sure that the machine from where you trigger the request has internet access. It might be an obvious thing, but I have seen cases where the service seems to be failing, and it is just that extremely simple detail.

#database administration #homepage #monitor #database