Creating a SQL Server Build and Patch Release Table

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 3^rd 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 3^rd 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 3^rd 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:

• How to tell what SQL Server version you are running
• Applying SQL Server Service Packs
• Extracting Tables from PowerShell’s Invoke-WebRequest
• New Features and Best Practices of SQL Server PowerShell
• How to determine the version, edition, and update level of SQL Server and its components

Jeffrey Yao

Jeffrey Yao is a senior SQL Server consultant with 15 years of database administration, his current interests are on database administration automation and visualization. He enjoys reading the concise and smart sql tips and loves R&D work in DBA Engineering topics. His blog is at: http://www.sqlservercentral.com/blogs/jeffrey_yao/.