Problem
As proactive SQL Server DBAs, we can all agree that an important topic of discussion is patching SQL Server instances under our support. We need to make sure that they contain the latest bug fixes and enhancements released by Microsoft. A normal course of action would be to identify a reliable source of information that you can use to verify the list of SQL Server releases, either for all versions or a particular one that you might be interested in; that way, you can confirm if a new release has been recently made available so you can plan its deployment at a future point in time (along with the proper testing in a sandbox environment, if possible).
A downside is that you don’t always have spare time (or sometimes you forget) to check to see if your SQL Server instances are up to date, as well as checking what new versions and updates exist for the instances you support.
Solution
In this tip I will present a PowerShell script that parses the content of a popular website that contains the list of SQL Server Build Numbers, for the myriad of SQL Server versions released by Microsoft. The solution presented within this article can help you craft a custom solution that can be used to your advantage to keep track of every new SQL Server release that Microsoft makes available to the public, in an automatic fashion. In a future article, I will demonstrate how this can be achieved by implementing it as a module for my monitoring series using PowerShell, so stay tuned.
Idea to Create the PowerShell Script
In my case, I have been a fan for a long time of the site https://sqlserverbuilds.blogspot.com and have used it regularly to see if there has been anything new lately with a particular SQL Server version. As you can see in the screenshots below, the information presented for a particular SQL Server version is pretty straight forward, and it even includes the links that take you to Microsoft’s official publications for each individual build number.
Screenshot sample for SQL Server 2019:
Screenshot sample for SQL Server 2017:
After looking at the content over and over, I noticed that the structure of the HTML tables for each SQL Server version is exactly the same, and I just couldn’t help myself from thinking that there should be a way to craft a script that can programmatically access this information So I did, and hopefully I can go through it and explain it as best as I can, without getting very technical in the process, so let’s begin.
Structure of the PowerShell script
At a high level, this is what the script does:
- It performs a web request to the URL https://sqlserverbuilds.blogspot.com/.
- It saves the HTML page in the same path that the script is currently located.
- Using several patterns, the respective HTML tags are parsed to extract only the information that is relevant. This might vary from use case to use case, but after grasping the overall structure of the script, it could be adapted relatively easy.
The PowerShell script accepts one parameter called “sqlVersion” that, by default, is set to 2019. However, you can specify any of the following to make it work: 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and ALL. If you invoke it with “ALL” then, you guessed it, the information for all the SQL Server versions from 2008 to 2019 will be parsed and presented.
Note: I intentionally have set the lower end to SQL Server 2008 because to me, that version is old enough right now and anything running a SQL Server version prior to that should be upgraded as soon as possible. I know that there are legacy systems out there that simply won’t cooperate. However, if your particular case requires the inclusion of older versions, then feel free to adapt it to suit your requirements.
Let me elaborate on each of the three points stated in the high-level description, to get a sense of what’s going on in each of the steps:
To issue a web request I use the Invoke-WebRequest cmdlet that simply gets content from a web page on the internet. After grabbing the data from the web page, I send the output to a file called “page.html” within the same path that the PowerShell script is located (this can be changed if you like, just make sure to correctly specify the paths you want and you should be good to go).
Here’s the official documentation of the Invoke-WebRequest cmdlet, if you’d like to dig a bit deeper.
During the execution of the script, this is what you should be seeing from your end to confirm that page.html file was correctly saved.
*The .html file will be saved in the exact same location where the PowerShell script file is located.
To process the saved .html file, the scripting object interface “IHTMLDocument2 interface” is used, which will allow us to parse through the content in an easy way.
Here’s the resource link if you’d like to dig deeper.
One important thing to mention is that the IHTMLDocument2 interface works only if you have Microsoft Office installed. From what I have researched, it is because of a DLL called mshtml.dll, which I personally find weird. Also, it doesn’t have to be a fully licensed installation of Office, a trial version can pull off the trick as well.
In order to perform lookups, I store inside an array the contents of all the <td> tags using the command “$html.all.tags("td") | % innerText”. After all, it is an HTML table what we are chasing so this makes it quite easy to digest.
This is a screenshot of what the output looks like after retrieving the inner text of all the elements enclosed with <td>:
As you can see, with this output, the information is pretty much there. You have the release date, the build number and the description of the update itself (if it’s a Cumulative Update, if it’s a Service Pack, if it’s an On-demand hotfix, etc.).
To make this process a bit easier to the eye, I have built a function called “SQL-BuildNumbers” that allows the gathering of the information for a particular SQL Server version, and displays it to the end user. The benefit of having it as a function is that it can be re-used and applied for any given SQL Server version, or to all of them.
The function works by feeding it with a set of strings patterns that could potentially lead to a table row that contains the information of the build numbers. Examples of such patterns can be:
- “Cumulative update.*for SQL Server 2019”
- “Security update.*for SQL Server 2019”
- “On-demand hotfix update package.*for SQL Server 2019”
- “Security update for the Remote Code Execution vulnerability in SQL Server 2019”
- “SQL Server 2019 RTM”
Note: The * means anything between the strings.
Here’s an example of the output you get after calling it with the 2019 value as a parameter:
As you can see, the records perfectly match the screenshot I showed above from the website itself, with a very important detail: if you take a closer look, you will notice that there’s no CU2 or CU7 in the output and it is because those have been withdrawn by Microsoft (something that the script takes into account and decides not to show).
Here’s the output if the script is invoked with 2017 as a parameter:
One thing I’d like to get out of the way is the fact that I personally am not a fan of the Community Technology Preview releases, as they are Betas and could be quite buggy or unstable. But if you want to add those to the mix, then just make sure to add the respective string patterns to the script so that they are taken into account.
Complete PowerShell Script
param(
$sqlVersion = "2019"
)
Import-Module SQLPS
#Function for the logic of SQL Server Versions < 2017 (due to the removal of the Service Packs)
function SQL-BuildNumbers([string[]]$Array,[string]$sqlVersion){
for($l = 0; $l -lt $fileArray.Count; $l++){
for($i = 0; $i -lt $Array.Count; $i++){
$cu = $extra = $buildNumber = $releaseDate = "NULL"
$sp = "RTM"
if($Array[$i].toString() -eq $fileArray[$l]){
if($fileArray[$l].Contains("GDR") -or $fileArray[$l].Contains("Security update") -or $fileArray[$l].Contains("security update") -or $fileArray[$l].Contains("TLS 1.2") -or $fileArray[$l].Contains("QFE")){
$extra = "GDR"
}
if($fileArray[$l].Contains("On-demand") -or $fileArray[$l].Contains("on-demand")){
$extra = "ON"
}
if($fileArray[$l].Contains("Customer Technology Preview") -or $fileArray[$l].Contains("Community Technology Preview") -or $fileArray[$l].Contains("CTP")){
$extra = "CTP"
}
if($fileArray[$l].Contains("Service Pack 1") -or $fileArray[$l].Contains("SP1")){
$sp = "SP1"
}
if($fileArray[$l].Contains("Service Pack 2") -or $fileArray[$l].Contains("SP2")){
$sp = "SP2"
}
if($fileArray[$l].Contains("Service Pack 3") -or $fileArray[$l].Contains("SP3")){
$sp = "SP3"
}
if($fileArray[$l].Contains("Service Pack 4") -or $fileArray[$l].Contains("SP4")){
$sp = "SP4"
}
if($sp -eq ""){
$sp = "RTM"
}
if($fileArray[$l].Contains("Cumulative update")){
$cu = [regex]::Match($fileArray[$l],"\(C.*\)").Value
$cu = $cu.Substring(1,$cu.IndexOf(")")-1)
}
$buildNumber = $fileArray[$l-5]
$releaseDate = $fileArray[$l+1] -replace " \*new",""
#Complement for SQL 2017 Build Numbers
if($buildNumber -eq "14.0.3192.2"){$cu = "CU15"}
if($buildNumber -eq "14.0.3035.2"){$cu = "CU9"}
#Complement for SQL 2016 Build Numbers
if($buildNumber -eq "13.0.5366.0"){$cu = "CU7"}
if($buildNumber -eq "13.0.5270.0"){$cu = "CU5"}
if($buildNumber -eq "13.0.5239.0"){$cu = "CU4"}
if($buildNumber -eq "13.0.5201.2"){$cu = "CU2"}
if($buildNumber -eq "13.0.4604.0"){$cu = "CU15"}
if($buildNumber -eq "13.0.4522.0"){$cu = "CU10"}
if($buildNumber -eq "13.0.2218.0"){$cu = "CU9"}
if($buildNumber -eq "13.0.2190.2"){$cu = "CU3"}
if($buildNumber -eq "13.0.2186.6"){$cu = "CU3"}
if($buildNumber -eq "13.0.2170.0"){$cu = "CU2"}
if($buildNumber -eq "13.0.2169.0"){$cu = "CU2"}
#Complement for SQL 2014 Build Numbers
if($buildNumber -eq "12.0.6293.0"){$cu = "CU3"}
if($buildNumber -eq "12.0.5659.1"){$cu = "CU17"}
if($buildNumber -eq "12.0.5532.0"){$cu = "CU2"}
if($buildNumber -eq "12.0.4437.0"){$cu = "CU4"}
if($buildNumber -eq "12.0.4237.0"){$cu = "CU3"}
if($buildNumber -eq "12.0.4232.0"){$cu = "CU3"}
if($buildNumber -eq "12.0.4419.0"){$cu = "CU1"}
if($buildNumber -eq "12.0.4487.0"){$cu = "CU9"}
if($buildNumber -eq "12.0.2381.0"){$cu = "CU2"}
if($buildNumber -eq "12.0.2485.0"){$cu = "CU6"}
if($buildNumber -eq "12.0.2548.0"){$cu = "CU8"}
#Complement for SQL 2012 Build Numbers
if($buildNumber -eq "11.0.6615.2"){$cu = "CU10"}
if($buildNumber -eq "11.0.6607.3"){$cu = "CU9"}
if($buildNumber -eq "11.0.6567.0"){$cu = "CU6"}
if($buildNumber -eq "11.0.5676.0"){$cu = "CU15"}
if($buildNumber -eq "11.0.5613.0"){$cu = "CU6"}
if($buildNumber -eq "11.0.3513.0"){$cu = "CU16"}
if($buildNumber -eq "11.0.3460.0"){$cu = "CU13"}
if($buildNumber -eq "11.0.3350.0"){$cu = "CU3"}
#Complement for SQL 2008 R2 Build Numbers
if($buildNumber -eq "10.50.4339.0"){$cu = "CU13"}
if($buildNumber -eq "10.50.4321.0"){$cu = "CU13"}
if($buildNumber -eq "10.50.2881.0"){$cu = "CU13"}
if($buildNumber -eq "10.50.2861.0"){$cu = "CU8"}
if($buildNumber -eq "10.50.1790.0"){$cu = "CU7"}
#Complement for SQL 2008 Build Numbers
if($buildNumber -eq "10.0.5890.0"){$cu = "CU17"}
if($buildNumber -eq "10.0.5869.0"){$cu = "CU17"}
if($buildNumber -eq "10.0.2841.0"){$cu = "CU14"}
if(-not($fileArray[$l].Contains("Withdrawn") -or $fileArray[$l].Contains("Deprecated"))){
#Extra validation to remove the build numbers from 2008 R2 that might slip in the 2008 validation
if($sqlVersion -eq "2008"){
if(-not $buildNumber.Contains("10.50")){
Write-Host "SP:$($sp) CU:$($cu) Extra:$($extra) BuildNumber:$($buildNumber) Release Date:$($releaseDate)"
}
}
else{
Write-Host "SP:$($sp) CU:$($cu) Extra:$($extra) BuildNumber:$($buildNumber) Release Date:$($releaseDate)"
}
}
}
}
}
}
#End Of Function
if($sqlVersion -ne '2019' -and $sqlVersion -ne '2017' -and $sqlVersion -ne '2016' -and $sqlVersion -ne '2014' -and $sqlVersion -ne '2012' -and $sqlVersion -ne '2008 R2' -and $sqlVersion -ne '2008R2' -and $sqlVersion -ne 'ALL'){
Write-Host "Invalid Parameter!!!" -ForegroundColor White -BackgroundColor Red
exit
}
$data = Invoke-WebRequest "https://sqlserverbuilds.blogspot.com/" -UseBasicParsing
$data.RawContent | Out-file "page.html"
$html = New-Object -ComObject "HTMLFile"
# This works in PowerShell with Office installed
$html.IHTMLDocument2_write($(Get-Content "page.html" -raw))
[string[]]$fileArray = $html.all.tags("td") | % innerText
if($sqlVersion -eq '2019' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2019 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(Cumulative update.*for SQL Server 2019)|(Security update.*for SQL Server 2019)|(On-demand hotfix update package.*for SQL Server 2019)|(Security update for the Remote Code Execution vulnerability in SQL Server 2019)|(SQL Server 2019 RTM)'
SQL-BuildNumbers $sqlArray "2019"
}
if($sqlVersion -eq '2017' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2017 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(Cumulative update.*for SQL Server 2017)|(Security update.*for SQL Server 2017)|(On-demand hotfix update package.*for SQL Server 2017)|(Security update for the Remote Code Execution vulnerability in SQL Server 2017)|(SQL Server 2017 RTM)'
SQL-BuildNumbers $sqlArray "2017"
}
if($sqlVersion -eq '2016' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2016 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(SQL Server 2016 Service Pack.*)|(Cumulative update.*for SQL Server 2016)|(Security update.*for SQL Server 2016)|(On-demand hotfix update package.*for SQL Server 2016)|(Security update for the Remote Code Execution vulnerability in SQL Server 2016)|(SQL Server 2016 RTM)'
SQL-BuildNumbers $sqlArray "2016"
}
if($sqlVersion -eq '2014' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2014 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(SQL Server 2014 Service Pack.*)|(Cumulative update.*for SQL Server 2014)|(Security update.*for SQL Server 2014)|(On-demand hotfix update package.*for SQL Server 2014)|(Security update for the Remote Code Execution vulnerability in SQL Server 2014)|(SQL Server 2014 RTM)'
SQL-BuildNumbers $sqlArray "2014"
}
if($sqlVersion -eq '2012' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2012 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(SQL Server 2012 Service Pack.*)|(Cumulative update.*for SQL Server 2012)|(Security update.*for SQL Server 2012)|(On-demand hotfix update package.*for SQL Server 2012)|(Security update for the Remote Code Execution vulnerability in SQL Server 2012)|(SQL Server 2012 RTM)'
SQL-BuildNumbers $sqlArray "2012"
}
if($sqlVersion -eq '2008 R2' -or $sqlVersion -eq '2008R2' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2008R2 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(SQL Server 2008 R2 Service Pack.*)|(Cumulative update.*for SQL Server 2008 R2)|(Security update.*for SQL Server 2008 R2)|(On-demand hotfix update package.*for SQL Server 2008 R2)|(Security update for the Remote Code Execution vulnerability in SQL Server 2008 R2)|(SQL Server 2008 R2 RTM)'
SQL-BuildNumbers $sqlArray "2008 R2"
}
if($sqlVersion -eq '2008' -or $sqlVersion -eq 'ALL'){
Write-Host "SQL Server 2008 Build Numbers" -ForegroundColor White -BackgroundColor Green
$sqlArray = $html.all.tags("td") | % innerText | Select-String -Pattern '(SQL Server 2008 Service Pack.*)|(Cumulative update.*for SQL Server 2008)|(Security update.*for SQL Server 2008)|(On-demand hotfix update package.*for SQL Server 2008)|(Security update for the Remote Code Execution vulnerability in SQL Server 2008)|(SQL Server 2008 RTM)'
SQL-BuildNumbers $sqlArray "2008"
}
#Delete the generated HTML file
Remove-Item "page.html"
Write-Host 'Done!'
Next Steps
- The output that the script provides more than enough for you to develop your own solution to start saving this information in a database and keep it up to date, with the respective logic that validates new entries between each execution cycles.
- Regardless of your weapon of choice to discover SQL Server build numbers, I hope that the PowerShell script described within this article can help you not only with stuff related to build numbers of SQL Server, but with any other piece of information that you find useful out there that can be a good fit or simply has a similar structure as the one exposed. Also, I would like to be very emphatic with the fact that solutions like this should not be used to maliciously bring down a website due to an overload of automated requests.
- As I mentioned in the beginning of the article, with what we learned here, I’m going to create a solution that integrates this script in a way that will allow the end user to keep the information of its SQL Server instances automatically up-to-date. If you want to take a quick glimpse at the core of the set of monitoring PowerShell scripts that I have contributed to MSSQLTips.com, click here.
