Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Creating a SQL Server Build and Patch Release Table


By:   |   Read Comments (11)   |   Related Tips: More > Upgrades and Migrations

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.

GoogleResult - Description: Google result for "sql server build numbers"

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:

Build number - Description: Example of SQL 2017 build numbers

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() });
                continue;
            }
 
            ## If we haven't found any table headers, make up names "P1", "P2", etc.
            if(-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($col);
                }
                if ($IncludeLink)
                {  
                    $col = New-Object System.Data.DataColumn('Link', [System.String]);
                    $dt.Columns.Add($col);
                }
 
            } #if $dt.columns.count -eq 0
 
            $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($dr);
        }
        Write-Output $dt;
    }#try
    catch
    {
        Write-Error $_;
    }
}# Get-SQLBuildHisotry 

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  
   
First_PS_Example - Description: First PS example

We can also run the following:

Get-SQLBuildHistory -TableNumber 2 -IncludeLink | Select-Object -First 5 | Format-Table -Auto 
   
2nd_ps_example - Description: 2nd PS Example

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 -DisableNameChecking;
for ($i=1; $i -le 7; $i++) #extract sql server 2017 to SQL Server 2005 build history
{
    $t = Get-SQLBuildHistory -TableNumber $i -IncludeLink;
    Write-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:

InventoryTable - Description: query result of the inventory table

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;
Monthly Patch number

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:



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 11, 2018 - 11:40:13 AM - Brandon Back To Top

 

Jeff, that is exactly how I have created the file so it looks like I was on the right track! Thank you so much for the help, it is very much appreciated as I am just getting started and have A LOT to learn!


Friday, May 11, 2018 - 12:43:56 AM - jeff_yao Back To Top

 Brandon,

Assuming you already create the table needed (i.e. using the first t-sql script), you can create a script (let's name it as ABC.ps1) containing the following two parts.

1. Copy the whole script of 2nd script window (i.e. the script with #requires -version 4.0 as 1st line)

2. Copy the whole script in last PowerShell script window, i.e. 

 

#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 -DisableNameChecking;
for ($i=1; $i -le 7; $i++) #extract sql server 2017 to SQL Server 2005 build history
{
    $t = Get-SQLBuildHistory -TableNumber $i -IncludeLink;
    Write-SqlTableData -ServerInstance localhost -DatabaseName MSSQLTips `
    -SchemaName dbo -TableName SQLVer -InputData $t;
}

Note you need to change 'localhost' to your sql server instance name. (You need to change MSSQLTIPS database name to your own as well)
Save this ABC.ps1 into a folder, and you can schedule a job to run this ABC.ps1.

Thursday, May 10, 2018 - 4:05:36 PM - Brandon Back To Top

jeff_yao

Thanks for the reply. So are you able to give me an example of what the script should look like that I am scheduling to run weekly? Do I schedule the entire thing to run or just the part from Import-Module down?


Wednesday, May 09, 2018 - 4:33:21 PM - jeff_yao Back To Top

 

Hi Brandon,

I would always include this line "import-module sqlserver " in my .ps1 script, because it does not hurt anything. If the module is already loaded, it will NOT be reloaded.

(Of course, if the module is loaded in your profile, you do not even need to include the line in your .ps1 script)

Thanks,

jeff_yao


Wednesday, May 09, 2018 - 11:56:58 AM - Brandon Back To Top

 

New here, so forgive my lack of experience in working with powershell... just stepped in to my new role as a DBA and am loving it, but I have A LOT to learn. In regards to the PowerShell script, what all should the final .ps1 file include from all of the code snippets above? I have tested the process to the best of my ability and it seems like it's going to work perfectly, but I am trying to figure out exactly what needs to be in the .ps1 file that I will be scheduling to run weekly.  The thing that threw me was the Import-Module sqlserver line, is that something that needs to run every time even though the module is already imported?

Thanks for the help!


Monday, January 15, 2018 - 3:15:08 PM - David Back To Top

 I ran the 'Get-SQLBuildHistory -TableNumber 1' and got data to return. I then ran the 'Get-SQLBuildHistory' code and all worked fine. Thanks

 


Monday, January 15, 2018 - 2:20:22 PM - Salam Back To Top

 Wondefull and usefull article, I executed all steps, everything went OK and I was able to update 2 test SQL servers with the information

Thanks again

 


Monday, January 15, 2018 - 1:20:18 PM - jeff_yao Back To Top

 

@David, your error seems to indicate the -InputData is invalid, i.e. $t is not correctly returned from Get-SQLBuildHistory. To debug this issue, I'd like you to run the following code manually

Get-SQLBuildHistory -TableNumber X -IncludeLink  #change X from 1 to 7 and see whether you can get anything back. If everything is OK, try your script again.

 

I have run this in my different environments (both at home and in office), I have no problem so far.


Monday, January 15, 2018 - 12:51:52 PM - David Back To Top

 I got this to work, which is great. But I am having an issue when it runs. There are two processes that run writing data to the table, the first completes quickly. The second hangs at 97%. Errors below:

Get-SQLBuildHistory : Arithmetic operation resulted in an overflow.

At C:\sqlbuild\Populate_Table.ps1:7 char:10

+     $t = Get-SQLBuildHistory -TableNumber $i -IncludeLink;

+          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-SQLBuildHistory

 

Write-SqlTableData : Cannot validate argument on parameter 'InputData'. The argument is null. Provide a valid value for the argument, and then try running the command again.

At C:\sqlbuild\Populate_Table.ps1:9 char:50

+     -SchemaName dbo -TableName SQLVer -InputData $t;

+                                                  ~~

    + CategoryInfo          : InvalidData: (:) [Write-SqlTableData], ParameterBindingValidationException

    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

 

Get-SQLBuildHistory : Arithmetic operation resulted in an overflow.

At C:\sqlbuild\Populate_Table.ps1:7 char:10

+     $t = Get-SQLBuildHistory -TableNumber $i -IncludeLink;

+          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-SQLBuildHistory

 

Write-SqlTableData : Cannot validate argument on parameter 'InputData'. The argument is null. Provide a valid value for the argument, and then try running the command again.

At C:\sqlbuild\Populate_Table.ps1:9 char:50

+     -SchemaName dbo -TableName SQLVer -InputData $t;

+                                                  ~~

    + CategoryInfo          : InvalidData: (:) [Write-SqlTableData], ParameterBindingValidationException

    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

 


Monday, January 15, 2018 - 12:51:23 PM - jeff_yao Back To Top

 

Thanks @Kamil, you are right, sqlserver PS should be installed. It's my bad as I have my environment set up for long time and thus I may take everything for granted without thinking through. Thanks again for your reminder.


Monday, January 15, 2018 - 11:23:44 AM - Kamil Back To Top

 Nice tutorial.  Thank you.  It didn't work 100% at first, so I thought I'd drop a note.  Some folks may need to run:

Install-Module -Name SqlServer -AllowClobber   

Set-ExecutionPolicy RemoteSigned

 


Learn more about SQL Server tools