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

 

Extract Hot Fix Details from a SQL Server CU KB Articles


By:   |   Last Updated: 2018-03-30   |   Comments   |   Related Tips: More > Upgrades and Migrations

Problem

Each time Microsoft releases a SQL Server Cumulative Update (CU) package, it will publish a corresponding KB article that will include a table detailing what fixes are included in this CU package. Around March 2014, the table has a new column [Fix Area] as shown below (using SQL Server 2014 CU1 as an example).

sql server hotfixes

This [Fix area] is very valuable to DBAs for them to make decisions whether they need apply this CU or not. For example, if this CU does not include any SSAS fixes, then we may skip this CU on a SSAS box.

For SQL Server 2017 CU KB articles, Microsoft has added another column [Platform] because SQL Server 2017 can now support Linux, so a fix needs to be clarified for what platform it applies to, as shown below (using SQL Server 2017 CU1 as an example).

sql server hotfixes

So, is there way that I can automatically collect this information into a table instead of manually reviewing each KB article?

Solution

To automate this process, we will first create a CU detail table and then we will use PowerShell to read the CU KB weblink and then extract the needed data.

The CU detail table should accommodate the latest SQL Server 2017 CU KB article, which contains the 5 columns.

use MSSQLTips
 
-- store the fix information in CU KB article
if object_id('dbo.SQLCU_Detail', 'U') is not null
   drop table dbo.SQLCU_Detail;

create table dbo.SQLCU_Detail (
 VSTSBugNumber varchar(50)
 , KBNumber varchar(12)
 , [Description] varchar(1024)
 , [FixArea] varchar(50)
 , [Platform] varchar(50)
 , KB_URL varchar(256)
 , SQLCU_URL varchar(256)
 , LogDate datetime default current_timestamp
 , id int identity primary key
);
			

The typical SQL Server CU KB web page uses JQuery to render the final content in the web page. This means if we use invoke-webrequest with the CU KB web link, we will not get the whole content. To overcome this hurdle, I will use PowerShell to start an IE explorer (assuming Microsoft IE is installed). The detailed code is shown below.

<#
.SYNOPSIS
Retrieve Details of SQL Server KB change log
 
.DESCRIPTION
Get-SQLCUDetail is to extract the summary of changes described in SQL Server KB for a CU
This is more for CU KBs released after Mar, 2014, because in these CU KB article, there is new column [fixe area], 
indicating to what service the fix is applicable, such as wheter it is SSAS or SSRS or SQL Service etc.
 
.Parameter
-SQLCU_Url weblink to the CU KB, it is mandatory
 
.PARAMETER
-TableNumber indicates which table in the SQLCU_Url website shall we extract the SQL KB info,
the table number is 0 based, and defaults to 0. Not mandatory
 
.PARAMETER
-IncludeLink a switch parameter, if exists, will extract the HREF link out of the table column value, 
we assume there is only one column has HREF link.
 
.EXAMPLE
The following returns the fix summary of SQL Server 2017 CU4 for RTM
Get-SQLCUDetail -SQLCU 'https://support.microsoft.com/en-us/help/4056498/cumulative-update-4-for-sql-server-2017' | 
select 'kb article number', 'fix area', 'description' | ft -auto -wrap
 
KB article number Fix area                Description                                              
----------------- --------                -----------                                              
4042948           Reporting Services      FIX: Data-driven subscription fails after you upgrade    
                                          from SSRS 2008 to SSRS 2016                              
4053550           SQL Engine              FIX: Processing XML message through Service Broker       
                                          results in hung session in SQL Server 2016 and 2017      
4052123           Reporting Services      FIX: Sliding expiration for authentication cookie isn't  
                                          working and fails to redirect to logon page in SSRS 2016 
                                          and 2017                                                 
4057054           SQL performance         FIX: CXPACKET and CXCONSUMER wait types show             
                                          inconsistent results for some parallel query plans in    
                                          SQL Server 2017                                          
4057087           SQL Engine              FIX: Unable to restore a database using replace option   
                                          if the FILENAME contains double slash operator in SQL    
                                          Server 2017                                              
4076982           High Availability       FIX: Pacemaker demotes existing primary replica of an    
                                          AlwaysOn AG in SQL Server 2017 on Linux and never        
                                          promotes a new one                                       
4077103           In-Memory OLTP          FIX: In-Memory databases in an Availability Group hang   
                                          during recovery in SQL Server 2017                       
4077683           SQL Engine              FIX: System stored procedure sp_execute_external_script  
                                          and DMV sys.dm_exec_cached_plans cause memory leaks in   
                                          SQL Server 2017                                          
4055727           In-Memory OLTP          FIX: Recovery of database takes a long time when it      
                                          contains memory-optimized tables in SQL Server 2016 and  
                                          2017                                                     
4053439           SQL Engine              Improvement: Move master database and error log file to  
                                          another location in SQL Server 2017 on Linux             
4057759           Analysis Services       FIX: Internal error when you drill down hierarchy        
                                          members in SSAS 2016 and 2017 in multidimensional mode   
..... 
#>
 
function Get-SQLCUDetail
{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$SQLCU_Url, 
 
        [Parameter(Mandatory = $false)]
        [int] $TableNumber=0, # 0 based,
 
        [Parameter(Mandatory=$false)]
        [switch] $IncludeLink
    )
 
    try
    {
        $iex = new-object -ComObject "InternetExplorer.Application";
        $iex.silent = $true;
        $iex.navigate($SQLCU_Url);
        while($iex.Busy) { Start-Sleep -Milliseconds 200; }
        Start-Sleep 1; #adjust 1 to higher number if your computer or internet speed connection is slow
 
        $tables =@($iex.Document.documentElement.getElementsByTagName('table'));
        if ($tables -eq $null -or $tables.count -eq 0)
        {
            throw {"this $SQLCU_URL is not a valid SQL CU URL"};
        }
 
        $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('URL', [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.URL = ($c.getElementsByTagName('a') | select -ExpandProperty href) -join ';';
                    }
                }
            }
            $dt.Rows.add($dr);
        } #foreach
 
        #add the CU link to the $dt table 
        $col =  New-Object System.Data.DataColumn('SQLCU_URL', [System.String]);
        $col.DefaultValue = $SQLCU_Url;
        $dt.columns.Add($col);
 
        Write-Output $dt -NoEnumerate;
    }#try
    catch
    {
        Write-Error $_;
    }
    $iex.Stop();
    $iex.Quit();
}#Get-SQLCUDetail
				 
			

The PowerShell script is pretty simple and is identical to the code in Creating a SQL Server Build and Patch Release Table.

If we run the following script:

Get-SQLCUDetail -SQLCU 'https://support.microsoft.com/en-us/help/4056498/cumulative-update-4-for-sql-server-2017' | 
select 'kb article number', 'fix area', 'description' | ft -auto -wrap; 
			

We will get:

sql server hotfixes extracted data

Now let’s try to use this function and dump the information to the dbo.SQLCU_Detail when a CU link is given.

import-module sqlserver -DisableNameChecking;
#define the cu web url
$sqlcu_url = 'https://support.microsoft.com/en-us/help/4056498/cumulative-update-4-for-sql-server-2017';
 
#retrieve the CU details from $sqlcu_url, and assume internet connection exists
$dt = Get-SQLCUDetail -SQLCU $sqlcu_url -IncludeLink;
				 
#write to sql table, change 
'localhost' 
and 'mssqltips' 
to your proper values
Write-SqlTableData 
-Server 'localhost' 
-DatabaseName 'mssqltips' 
-SchemaName dbo 
-TableName SQLCU_Detail 
-InputData $dt;

Now if we open an SSMS window and run the following query

use MSSQLTips
select * from dbo.SQLCU_Detail			

We will get the following result (this is just a partial display).

extract sql server hot fix info into a sql table

In the above output, we can see Microsoft made a typo in the link of the second row, the hyper-link value should be: https://support.microsoft.com/en-us/help/4053550 instead of just https://support.microsoft.com/.

In other words, the original KB page, [KB article number] 4053550 has a bad link in its [Description] column.

sql server hotfixes mistake

Summary

In this tip, we have explored a way to automatically read a SQL Server CU KB web page and extract the details of the fixes included in the CU package. This information will assist DBAs to decide whether a CU package needs to be patched for a SQL Server instance, also it will provide insights for analysis of each CU package, such as how many fixes are included in the CU, how many fixes are applicable to which fix areas (SSAS, or SSRS or SQL engine or others) and under which platform (for SQL Server 2017+).

Next Steps

You can use this tip’s code together with the code in my previous tip to retrieve all CUs’ hotfixes. The general idea is:

  1. Use Get-SQLBuildHistory to get all CU releases
  2. Use Get-SQLCUDetail to loop through each CU release link obtained in step 1 to retrieve the fixes of each CU.

The following is a quick example of retrieving all hotfixes in all SQL Server 2017 CUs (as of 2018/March/12) and dump the results into our previous SQL table dbo.SQLCU_Detail (You can analyze fixes for SQL Server 2016 and previous versions).

import-module sqlserver -DisableNameChecking; 
Get-SQLBuildHistory -IncludeLink | where description -like 'CU* for Microsoft *' | 
foreach { $dt=Get-SQLCUDetail -SQLCU $($_.link).split(';')[0] -IncludeLink;  
          Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips -SchemaName dbo -TableName SQLCU_Detail -InputData $dt;
        }  
			

If we do a quick analysis of categorizing the hotfixes, we can see the following:

use MSSQLTips
select count(*) as [NumOfHotFixes], [FixArea]
from dbo.SQLCU_Detail
group by [FixArea]
			

There was a total of 176 hotfixes after SQL Server 2017 RTM release less than 5 months ago (note: SQL Server 2017 was released on Oct 2, 2017, and the latest CU is RTM CU4 released on Feb 20, 2018). Among which KB 4037412 appears twice, once in CU2 and also in CU4 (meaning it was not really fixed in CU2?) It is indeed very interesting if you do some analysis like this.

categorizing the sql server hotfixes based on Fix Area

You may also want to read a few related articles here:



Last Updated: 2018-03-30


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.



    



Learn more about SQL Server tools