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.
Here is the structure of the URL:
As you can see, there are two sections of the URL within curly brackets {}. The first will tell the service about the information that the user is actually targeting:
{ version:regex(2019 | 2017 | 2016 | 2014 | 2012 | 2008R2 | 2008 | all) }
In here you specify, as a parameter, the particular SQL Server version for which you wish to know the released/available build numbers. If you specify all, then all the build numbers I have collected in the database are going to be returned. I have only populated the database with build numbers starting from SQL Server 2008. I know that there are many systems out there still running SQL Server 2005 and below, but I just thought that SQL Server 2008 would be a good starting point; perhaps in a future revision/release of this project, I might add even older versions.
The next part looks like this:
{ kind:regex(first | latest | all) }
In here you specify, as a parameter, how granular you want the information to be returned by the service.
First: tells the service to return only the very first build number for the specified SQL Server version.
Latest: tells the service to return only the latest build number for the specified SQL Server version.
All: tells the service to return all the build numbers found for the specified SQL Server version.
Output Examples:
Here are some examples of the call to the service and the results. Note, as I stated earlier, if you experience either a blank json object as a response or general slowness overall, it means that the database was in a paused state and it is “waking up”.
Retrieving the first build number for SQL Server 2019.
URL: http://www.sqlserverbuilds.info/api/builds/2019/first
Result: [{ "sp" : "RTM", "build_number" : "15.0.2000.5", "release_date" : "2019-11-04" }]
Retrieving the latest build number for SQL Server 2019.
URL: http://www.sqlserverbuilds.info/api/builds/2019/latest
Result: [{ "sp" : "RTM","cu" : "CU4", "build_number" : "15.0.4033.1", "release_date" : "2020-03-31" }]
Retrieving all the build numbers for SQL Server 2019.
URL: http://www.sqlserverbuilds.info/api/builds/2019/all
Result: [
{ "sp" : "RTM", "cu" : "CU4", "build_number" : "15.0.4033.1", "release_date" : "2020-03-31" },
{ "sp" : "RTM", "cu" : "CU3", "build_number" : "15.0.4023.6", "release_date" : "2020-03-12" },
{ "sp" : "RTM", "cu":"CU2", "build_number" : "15.0.4013.40", "release_date" : "2020-02-13" },
{ "sp" : "RTM", "cu" : "CU1", "build_number" : "15.0.4003.23", "release_date" : "2020-01-07" },
{ "sp" : "RTM", "extra" : "GDR", "build_number" : "15.0.2070.41", "release_date" : "2019-11-04" },
{ "sp" : "RTM", "build_number" : "15.0.2000.5", "release_date" : "2019-11-04" }
]
Retrieving the first build number of all the SQL Server versions stored in the database.
URL: http://www.sqlserverbuilds.info/api/builds/all/first
Result: [
{ "sp" : "RTM", "build_number" : "15.0.2000.5", "release_date" : "2019-11-04" },
{ "sp" : "RTM", "build_number" : "14.0.1000.169", "release_date" : "2017-10-02" },
{ "sp" : "RTM", "build_number" : "13.0.1601.5", "release_date" : "2016-06-01" },
{ "sp" : "RTM", "build_number" : "12.0.2000.8", "release_date" : "2014-04-01" },
{ "sp" : "RTM", "build_number" : "11.0.2100.60", "release_date" : "2012-03-06" },
{ "sp" : "RTM", "build_number" : "10.50.1600.1", "release_date" : "2010-04-21" },
{ "sp" : "RTM", "build_number" : "10.0.1600.22", "release_date" : "2008-08-07" }
]
Retrieving the latest build number of all the SQL Server versions stored in the database.
URL: http://www.sqlserverbuilds.info/api/builds/all/first
Result: [
{ "sp" : "RTM", "cu" : "CU4", "build_number" : "15.0.4033.1", "release_date" : "2020-03-31" },
{ "sp" : "RTM", "cu" : "CU20", "build_number" : "14.0.3294.2", "release_date" : "2020-04-07" },
{ "sp" : "SP2", "cu" : "CU12", "build_number" : "13.0.5698.0", "release_date" : "2020-02-25" },
{ "sp" : "SP3", "cu" : "CU4", "extra" : "CVE", "build_number" : "12.0.6372.1", "release_date" : "2020-02-11" },
{ "sp" : "SP4", "extra" : "CVE", "build_number" : "11.0.7493.4", "release_date" : "2020-02-11" },
{ "sp" : "SP3", "extra" : "GDR", "build_number" : "10.50.6560.0", "release_date" : "2018-01-06" },
{ "sp" : "SP4", "extra" : "GDR", "build_number" : "10.0.6556.0", "release_date" : "2018-01-06" }
]
Structure of the JSON response
Once you get back the JSON response, you’ll need to interpret the information:
[
{
"sp" : "RTM",
"cu" : "CU4",
"build_number" : "15.0.4033.1",
"release_date" : "2020-03-31"
}
]
sp: The Service Pack level of the build number
- From SQL Server 2017 and up, this will always have “RTM” as Microsoft shifted to Cumulative only releases.
cu: The Cumulative Update level of the build number.
- When this field doesn’t come in a particular response, it means that the build number is in its base RTM/SP level without its first Cumulative Update.
build_number: The actual build number of the specific release.
release_date: The date when Microsoft release the specific build number to the public.
- Sometimes, there are rare cases where Microsoft pulls a particular build number from public availability (due to bugs, errors reported). When I find cases like these, I usually pull them from the database as well.
extra: When this field appears in a particular response object, it means that the build number is a special case release, either a General Distribution Release, a Hotfix, or an On-Demand update.
Bonus script to interact with the public service
Since the spirit of this public service is to allow the fellow DBAs to programmatically consume the service, let me leave you a PowerShell script that you can use as a “stepping stone” for you own particular use case.
Something that has been very helpful to me (and it might be to you as well) is the use of this service to store the build numbers information in a central repository that I can keep up-to-date and use it to determine if my list of instances are up-to-date. Of course, you would have to craft that solution and apply some sort of automation to it.
Code
$response = Invoke-WebRequest -URI http://www.sqlserverbuilds.info/api/builds/2019/first -UseBasicParsing
$json = ConvertFrom-Json $([String]::new($response.Content))
#This means that an option that targets multiple build numbers was sent
if($json.length -gt 1){
foreach($item in $json){
$item
}
}
else{
$json
}
Output Examples
Fetching one build number for a particular SQL Server version.
Fetching all the build numbers from a particular SQL Server version.
Fetching all the build numbers stored in the database.
Conclusion
I really hope that this personal initiative can be valuable to any SQL Server DBA out there facing the same situation that I once faced. Keep in mind that there is a chance that you might find errors while attempting to consume the service, and it wouldn’t be that surprising as the version I’m presenting within this article is v1.0.
I personally will be updating the database with every new release that Microsoft makes public, and any ideas, comments, suggestions, complaints will always be welcome in favor of improving this service in any possible way, so feel free to drop a comment and I will try my best to address it.
The post Using a Public Web Service to Consume SQL Server Build Numbers appeared first on Simple Talk.
from Simple Talk https://ift.tt/3e5ktOB
via
No comments:
Post a Comment