The SQL Server Diagnostics Preview was announced just over a week ago It includes an add-on for SQL Server Management Studio to enable you to analyse SQL Server memory dumps and view information on the latest SQL Server cumulative updates for supported versions of SQL Server. Arun Sirpal has written a good blog post showing how to install it and use it in SSMS to analyse dumps.
There is also a developer API available so I thought I would write some PowerShell to consume it as there are no PowerShell code examples available in the documentation!
In a previous post I have explained how I created the module and a GitHub repository and used Pester to help me to develop the first command Get-SQLDIagRecommendations. At present the module has 5 commands, all for accessing the Recommendations API.
This post is about the command Get-SQLDiagFix which returns the Product Name, Feature Name/Area, KB Number, Title and URL for the Fixes in the Cumulative Updates returned from the SQL Server Diagnostics Recommendations API.
PowerShell Gallery
The module is available on the PowerShell Gallery which means that you can install it using
Install-Module SQLDiagAPI
as long as you have the latest version of the PowerShellGet module. This is already installed in Windows 10 and with WMF 5 but you can install it on the following systems
- Windows 8.1 Pro
- Windows 8.1 Enterprise
- Windows 7 SP1
- Windows Server 2016 TP5
- Windows Server 2012 R2
- Windows Server 2008 R2 SP1
following the instructions here.
If you are not running your PowerShell using a local administrator account you will need to run
Install-Module SQLDiagAPI -Scope CurrentUser
to install the module.
If you can’t use the PowerShell Gallery you can install it using the instructions on the repository
API Key
To use the API you need an API Key. An API Key is a secret token that identifies the application to the API and is used to control access. You can follow the instructions here to get one for the SQL Server Diagnostics API.
You will need to store the key to use it. I recommend saving the API Key using the Export-CliXML command as described by Jaap Brasser here .
Get-Credential | Export-CliXml -Path "${env:\userprofile}\SQLDiag.Cred"
You need to enter a username even though it is not used and then enter the API Key as the password. It is saved in the root of the user profile folder as hopefully, user accounts will have access there in most shops.
This will save you from having to enter the APIKey every time you run the commands as the code is looking for it to be saved in that file.
The Commands
Once you have installed the module and the APIKey it will be available whenever you start PowerShell. The first time you install you may need to run
Import-Module SQLDiagAPI
to load it into your session. Once it is loaded you can view the available commands using
Get-Command -Module SQLDiagAPI
You can find out more about the commands on the GitHub Repository and the Help files are in the documentation.
Get-Help
Always, always when starting with a new module or function in PowerShell you should start with Get-Help. I like to use the -ShowWindow parameter to open the help in a separate window as it has all of the help and a handy search box.
Get-Help Get-SQLDiagFix
Good help should always include plenty of examples to show people how to use the command. There are 12 examples in the help for Get-SQLDiagFix. You can view just the examples using
Get-Help Get-SQLDiagFix -examples
Get All Of The Fixes
The easiest thing to do is to get all of the available fixes from the API. This is done using
Get-SQLDiagFix
which will return all 123 Fixes currently referenced in the API.
That is just a lot of information on the screen. If we want to search through that with PowerShell we can use Out-GridView
Get-SQLDiagFix | Select Product, Feature, KB, Title | Out-GridView
Or maybe if you want to put them in a database you could use dbatools
$Fixes = Get-SQLDiagFix | Out-DbaDataTable
Write-DbaDataTable -SqlServer $Server -Database $DB -InputObject $Fixes -Table Fixes -AutoCreateTable
Get Fixes for a Product
If you only want to see the fixes for a particular product you can use the product parameter. To see all of the products available in the API you can run
Get-SQLDiagProduct
You can either specify the product
Get-SQLDiagFix -Product 'SQL Server 2016 SP1' | Format-Table
or you can pipe the results of Get-SQLDiagProduct to Get-SQLDiagFix which enables you to search. For example, to search for all fixes for SQL Server 2014 you can do
Get-SQLDiagProduct 2014 | Get-SQLDiagFix | Format-Table -AutoSize
Which will show the fixes available in the API for SQL Server 2014 SP1 and SQL Server 2014 SP2
Get The Fixes for A Feature
The fixes in the API are also categorised by feature area. You can see all of the feature areas using Get-SQLDiagFeature
Get-SQLDiagFeature
You can see the fixes in a particular feature area using the Feature parameter with
Get-SQLDiagFix -Feature Spatial | Format-Table -AutoSize
or you can search for a feature with a name like query and show the fixes using
Get-SQLDiagFix -Feature (Get-SQLDiagFeature query) | Format-Table -AutoSize
Get Fixes for a Product and a Feature
You can combine the two approaches above to search for fixes by product and feature area. If you want to see the fixes for SQL Server 2016Â to do with backups you can use
Get-SQLDiagProduct 2016 | Get-SQLDiagFix -Feature (Get-SQLDiagFeature backup) | Format-Table -AutoSize
No-one wants to see the words “…restore fails when….”! This is probably a good time to fix that.
Open the KB Article Web-Page
As well as getting the title and KB number of the fix, you can open the web-page. This code will open the fixes for all SP1 products in the feature area like al in Out-GridView and enable you to choose one (or more) and open them in your default browser
Get-SQLDiagProduct SP1 | Get-SQLDiagFix -Feature (Get-SQLDiagFeature -Feature al) | Out-GridView -PassThru | ForEach-Object {Start-Process $_.URL}`
There is a YouTube video as well showing how to use the command
You can find the GitHub repository at https://github.com/SQLDBAWithABeard/SQLDiagAPI