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).
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).
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_Urlwhile($iex.Busy) { Start-Sleep -Milliseconds 200Start-Sleep 1$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(); });
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('URL', [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.URL = ($c.getElementsByTagName('a') | select -ExpandProperty href) -join ';'$dt.Rows.add($dr$col = New-Object System.Data.DataColumn('SQLCU_URL', [System.String]$col.DefaultValue = $SQLCU_Url$dt.columns.Add($colWrite-Output $dt -NoEnumeratecatch
{
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:
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).
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.
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:
- Use Get-SQLBuildHistory to get all CU releases
- 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.
You may also want to read a few related articles here:
