Problem
With Microsoft releasing new SQL Server versions more frequently, patching all of the different SQL Servers in a timely way can be challenging especially in a big environment with hundreds of SQL Server instances, ranging from SQL Server 2008 to the latest SQL Server 2017. To make this more complex, different companies may have different patching policies, for example, one company requires to patch only the N – 1 patch, i.e. currently if SQL server is at CU2 level, for a CU3 to be applied, we have to wait until CU4 is released. Another company requires that we need to wait for 3 weeks after the patch release date before we can apply that patch to the dev environment so as to minimize risks (Microsoft does make errors in patches). There may be other rules if a database is used for a 3rd party tool, such as any patch needs approval from the application vendor, etc.
No matter what rules there are, we all need to know the latest patch release for each SQL Server version, especially SQL Server 2012+ versions as they are all in their prime support stages, meaning CUs are released frequently.
So how can we create a SQL Server patch inventory system to allow us monitor the latest SQL Server patch and make timely patches when needed?
Solution
To know the latest release of each SQL Server patch, we can monitor some of the Microsoft dedicated web pages, for example, for SQL Server 2014 build versions, we can go here, for SQL Server 2016, we can go here, etc. But I personally prefer some 3rd party web pages, where everything is in the same place. For example, if you Google “sql server build numbers”, you may come up with the following result page.
I especially like the first link https://buildnumbers.wordpress.com/sqlserver/, it is pretty clean and comprehensive, and usually the owner updates the page within a few days once a new CU (Cumulate Update) or SP (Service Pack) is released.
If we can extract information from this page, and dump the info into an inventory table, we will be able to compare the SQL Server build number in existing SQL Server instances against the inventory table and make the patch decision accordingly.
The following solution is based on PowerShell to extract information from the web page and then dump the data into a SQL Server table.
Analyze the Web Page
Looking at https://buildnumbers.wordpress.com/sqlserver/, we can see that there are a few tables on the page. For each SQL Server version, there is a corresponding table listing all the build numbers of this SQL Server version, like the following:
In this table, there are three columns, i.e. Build, Description and Release Date, but in Description, other than the obvious text, there is also hidden information, i.e. a reference URL to the web page for the related release. For example, under “CU2 for Microsoft SQL Server 2017 (KB4052574)”, the link is “https://support.microsoft.com/en-us/help/4052574”.
So what I want is to extract each table and dump the data into a SQL Server table. We will create a table as follows:
-- inventory table for SQL Server Version build numbers and the web URL to the each release
use MSSQLTips -- change it to your own db
drop table if exists dbo.SQLVer;
create table dbo.SQLVer (
Build varchar(32)
, Description varchar(256)
, ReleaseDate varchar(30)
, WebLink varchar(512) -- web url to the MS CU/SP release
);
PowerShell Script
The following script is based on a script of Lee Holmes, who has long been a member of the PowerShell developer team. I modified the original script by adding the capability to extract the HREF data out of the column [Description], and I also changed the output from a PSCustomObject to a DataTable object, so I can handle it more efficiently.
#requires -version 4.0 <# .SYNOPSIS Retrieve SQL Server build history from 3rd party website .DESCRIPTION Get-SQLBuildHistory is to extract SQL Server build numbers from a web site, which defaults to https://buildnumbers.wordpress.com/sqlserver/ .Parameter -SQLUrl weblink, defaulted to https://buildnumbers.wordpress.com/sqlserver/, non mandatory .PARAMETER -TableNumber indicates which table in the SQLUrl website shall we extract the SQL Build number, the table number is 0 based, and defaults to 1 for the default https://buildnumbers.wordpress.com/sqlserver/, not mandatory .PARAMETER -IncludeLink a switch parameter, if exists, will extract the HREF link out of the table column value .EXAMPLE The following returns the latest SQL Server (SQL Server 2017 as of today, 2018/Jan/02) Version Build Get-SQLBuildHistory | format-table -auto Build Description Release Date ----- ----------- ------------ 14.0.3008.27 CU2 for Microsoft SQL Server 2017 (KB4052574) 2017 November 28 14.0.3006.16 CU1 for Microsoft SQL Server 2017 (KB4038634) 2017 October 24 14.0.1000.169 SQL Server 2017 (vNext) RTM 2017 October 2 14.0.900.75 SQL Server 2017 (vNext) RC2 (Release Candidate 2) 2017 August 2 14.0.800.90 SQL Server 2017 (vNext) RC1 (Release Candidate 1) 2017 July 17 14.0.600.250 SQL Server 2017 (vNext) CTP 2.1 (Community Technology Preview 2.1) 2017 May 17 14.0.500.272 SQL Server 2017 (vNext) CTP 2.0 (Community Technology Preview 2.0) 2017 April 19 14.0.405.198 SQL Server vNext CTP 1.4 (Community Technology Preview 1.4) 2017 March 18 14.0.304.138 SQL Server vNext CTP 1.3 (Community Technology Preview 1.3) 2017 February 17 14.0.200.24 SQL Server vNext CTP 1.2 (Community Technology Preview 1.2) 2017 January 20 14.0.100.187 SQL Server vNext CTP 1.1 (Community Technology Preview 1.1) 2016 December 16 14.0.1.246 SQL Server vNext CTP 1 (Community Technology Preview 1) 2016 November 16 .EXAMPLE The following returns the SQL Server 2016 version build and the link Get-SQLBuildHistory -TableNumber 2 -IncludeLink | Format-Table -Auto #> function Get-SQLBuildHistory { [cmdletbinding()] param( [Parameter(Mandatory = $false)] [string]$SQLUrl='https://buildnumbers.wordpress.com/sqlserver/', [Parameter(Mandatory = $false)] [int] $TableNumber=1, # 0 based [Parameter(Mandatory=$false)] [switch] $IncludeLink ) ## Extract the tables out of the web request try { $WebRequest = invoke-webrequest $SQLUrl$tables = @($WebRequest.ParsedHtml.getElementsByTagName("TABLE")) $table = $tables[$TableNumber]$titles = @(); $dt = new-object System.Data.DataTable$rows = @($table.Rows); ## Go through all of the rows in the table foreach($row in $rows) { $cells = @($row.Cells) ## If we've found a table header, remember its titles if($cells[0].tagName -eq "TH") { $titles = @($cells | % { ("" + $_.InnerText).Trim() }); continueif(-not $titles) { $titles = @(1..($cells.Count + 2) | % { "P$_" }) } if ($dt.Columns.Count -eq 0) { foreach ($title in $titles) { $col = New-Object System.Data.DataColumn($title, [System.String]$dt.Columns.Add($colif ($IncludeLink) { $col = New-Object System.Data.DataColumn('Link', [System.String]$dt.Columns.Add($col$dr = $dt.NewRow(); for($counter = 0$counter -lt $cells.Count; $counter++) { $c = $cells[$counter]$title = $titles[$counter]if(-not $title) { continue$dr.$title = ("" + $c.InnerText).Trim(); if ($IncludeLink) { if ($c.getElementsByTagName('a').length -gt 0) { $dr.Link = ($c.getElementsByTagName('a') | select -ExpandProperty href) -join $dt.Rows.add($drWrite-Output $dtcatch { Write-Error $_
Once we run this function in a PowerShell ISE window, we can open another ISE window and run the following command to see what happens.
Get-SQLBuildHistory | Format-Table -Auto
We can also run the following:
Get-SQLBuildHistory -TableNumber 2 -IncludeLink | Select-Object -First 5 | Format-Table -Auto
Since we want to dump the data into the inventory table, we need to write another a few lines of code.
#assume the latest SQL Server PS module is installed, if not #go to https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module Import-Module sqlserver -DisableNameCheckingfor ($i=1$i -le 7$i++) #extract sql server 2017 to SQL Server 2005 build history { $t = Get-SQLBuildHistory -TableNumber $i -IncludeLinkWrite-SqlTableData -ServerInstance localhost -DatabaseName MSSQLTips ` -SchemaName dbo -TableName SQLVer -InputData $t
Of course, in my case, I am writing to my localhost with default instance and to a database named [MSSQLTips], you may need to change these values in your own environment.
After the PS script is run, we can then go back to the SQL Server table and do a quick SELECT.
select * from MSSQLTips.dbo.SQLVer
order by cast(substring(build,1, charindex('.', build)-1) as int) asc
, build asc
We will get the following:
We can schedule a job to update this inventory table weekly and once the update is done, we can check against our SQL instance and list the candidate instances for the next patch according to our business rules.
This inventory table may provide some interesting analysis, such as for each SQL Server version, how many CU patches have been released grouped by Service Packs or how many releases were made before the RTM, etc.
Summary
As a DBA, we need to automate as many things as possible to have fun and also to take the burden off as much as possible.
In this tip, we used PowerShell to extract the SQL Server build history from a 3rd party website and dumped the data into a pre-defined SQL Server table, so we can use the data in some useful ways, such as scheduling our next SQL Server patch.
Next Steps
There are some typos in the original web page https://buildnumbers.wordpress.com/sqlserver/, such as "January" is written as "Janury", we may do some cleanup work after the data is dumped into the SQL Server table. We can also do some analysis of the data, for example, which month would see most patches?
I did a quick cleanup and then a quick analysis check.
use MSSQLTips;
-- cleanup
Update t set ReleaseDate = replace(ReleaseDate, 'Janury', 'January')
from dbo.SQLVer t
where ReleaseDate like '%Janu%';
--find # of builds released in each month
select [Month]=month(convert(date, ReleaseDate)), PatchNum=count(*)
from dbo.sqlver
group by month(convert(date, ReleaseDate))
order by PatchNum;
July and August see most patches while May and September the least. Does this mean Microsoft employees usually do not take summer vacations, but prefer May and September instead?
You can read these related articles:
