TSQL2sday #94 Lets get all Posh!


Welcome to T-SQL Tuesday for September 2017!

tsql2sdayT-SQL Tuesday is a chance for you to join in the SQL Server community and write a blog post on a suggested topic. It makes for a great way to find a bunch of blog posts showing the same subject from many different viewpoints. Please join in and write a blog post, maybe it’s your first ever, maybe you haven’t blogged for a while but even if you blog every day come and join the party and share your knowledge.

To participate:

  1. Write a post on the topic below
  2. Schedule the post to go live on Tuesday, September 12th (between zero am and midnight, UTC)
  3. Include the TSQL Tuesday logo in the top of your post
  4. Link the post back to this one (it’s easier if you comment on this post and link it)
  5. Optional: Tweet a link to your post using the #tsql2sday hash tag on Twitter

Extra credit: if you’d like to host your own TSQL Tuesday in the future, read the full rules for info on how to sign up. Just like I did but don’t forget its your month!!

This month’s topic: Let’s get all Posh – What are you going to automate today?

PowerShellIt is no surprise to those that know me that I will choose PowerShell as the topic for this month. I am passionate about PowerShell because it has enabled me to have the career I have today and to visit numerous countries all around the world, meet people and talk about PowerShell. By my reckoning searching the TSQL Tuesday website it has been over 3 years since we had a topic specific to PowerShell. So I would like you to blog about PowerShell and SQL Server (or other interesting data platform products)

If you don’t know or use PowerShell GREAT! That’s awesome.

Please spend an hour or so with it and tell us how you got on and what and how you learned. Just like Erik and Brent did. You could install one of the community modules like dbatools, dbareports , SQLDiagAPI  or the Microsoft ones sqlserver or SSRS and try them out and tell us what you learned.

If you want help whilst doing this please make use of the #powershellhelp channel in the SQL Server Community Slack

This will be of so much benefit to all people who don’t use PowerShell and want to start to learn about it.

If you do use PowerShell and SQL then either tell the tale of the best thing you have automated or a beginners post to show people how to start using PowerShell. I have heard many stories and am looking forward to tales of

  • testing backups
  • doing migrations
  • resetting log shipping
  • creating things in the cloud and on premises
  • SQL on Linux with PowerShell on Linux
  • using Pester for testing
  • automating manual tasks
  • automating incident knowledge gathering
  • continuous integration and delivery

and many more. I will read all of them and do a write up of them later next week.


keep calm and powershell.jpg

Writing Dynamic and Random Tests Cases for Pester

I have written a module SQLDiagAPI for consuming the SQL Server Diagnostics API with PowerShell. I blogged about how I used Pester to develop one of the functions . Whilst writing Get-SQLDiagFix I wrote some Pester Tests to make sure that the output from the code was as expected.


For those that don’t know. Pester is a PowerShell module for Test Driven Development

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands

If you have PowerShell version 5 then you will have Pester already installed although you should update it to the latest version. If not you can get Pester from the PowerShell Gallery follow the instructions on that page to install it. This is a good post to start learning about Pester

The Command Get-SQLDiagFix

Get-SQLDiagFix  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. One Fix looks like this

07 - Get-SQLDiagFix result.png

This is how I wrote the Pester tests for that command

Mocking the results

In my describe block for each function I mock Get-SQLDiagRecommendations. This is the command that each of the current available commands in the module use to get the recommendations from the SQL Server Diagnostic Recommendations API. I did this by creating a json file from the API and saving it in a json folder inside the tests folder

01 - JSON folder.png

I can then mock Get-SQLDiagRecommendations inside a BeforeAll code block using

This means that every time the code in the test calls Get-SQLDiagRecommendations it will not use the internet to connect to the API and return an object. Instead it will return the $Recommendations object which is loaded from a file on the file system. I am not, therefore, depending on any external factors and I have a known set of data for my test results.
I also have a set of mocks in my Output Context code block

The fixes.json is a file which was created from the recommendations.json and only contains the properties returned by GetSQLDiagFix which is what we are testing here. I can set variables for Products and Features using the commands from the module as these will call Get-SQLDiagRecommendations which we have already mocked.

Test All of the Fixes

I can now test that the code I have written for Get-SQLDiagFix returns the correct data without any parameters using this test with Compare-Object.

If there is no difference between the object returned from Get-SQLDiagFix and the $fixes object which uses the json file then the code is working as expected and the test will pass.

Test Cases

I learned about test cases from Mike Robbins blog post. Test cases enable you to provide a hash table of options and loop through the same test for each of them. Here is an example

There are the following products in the Recommendation API

  • SQL Server 2012 SP3
  • SQL Server 2016 SP1
  • SQL Server 2016 RTM
  • SQL Server 2014 SP1
  • SQL Server 2014 SP2
and I want to run a test for each product to check that the fixes returned from Get-SQLDiagFix for that product match the $fixes object filtered by Product for those products. Here is the code

You can click on the image below to see a larger, more readable version.
02 Test Cases.png

The $TestCases variable holds an array of hashtables, one for each product with a Name that matches the parameter that I use in the test and a value of the product name.

I wrote one test, one It code block.  I refer to the product in the title inside <> using the same name as the name in the hashtable. The test (It) needs a parameter of -TestCases with a value (in this example) of the $TestCases variable we have just defined. It also needs a param block with a parameter that matches the Name value from the hashtables.

The expected test results are placed in a $results variable by filtering the $Fixes variable (defined in the BeforeAll code block above) by the parameter $Productname

The test will then run for each of the test cases in the $TestCases variable comparing the results of Get-SQLDiagFix -Product $Productname with the expected results from the $fixes variable

Here are the test results

03 - product test results.png

Multiple Products in Test Cases

I also want to test that Get-SQLDiagFix will work for multiple Products. I need to create TestCases for those too. I do that in exactly the same way

Which looks like this when the tests run
04 - mulitple product test results.png

Single Feature Dynamic Test Cases

Get-SQLDiagFix can also filter the fixes by feature area. The features are returned from Get-SQLDiagFeature. This means that I can create a test for each of the features by using the $features variable which was defined in the BeforeAll block as

Then I can dynamically create test cases using

and the results look like

05 - single feature test results.png

Random Dynamic Multiple Feature Test Cases

I also need to test that Get-SQLDiagFix returns the correct results for multiple features and whilst I could create those by hand like the products example above why not let PowerShell do that for me?

I created 10 test cases. Each one has a random number of features between 2 and the number of features.  I can then write one test to make use of those test cases. This is how I do that

Now there are 10 tests each with a random number of features and the results look like this. Each time the test is run it will use a different set of features for each of the 10 tests but I will know that I am testing that the code will return the correct results for multiple features

06 - multiple features.png

Two Sets of Test Cases?

It is also possible for Get-SQLDiagFix to have one or more products and one or more features passed as parameters, which obviously also need to be tested to ensure the code is returning the correct results. As Pester is just PowerShell we can use normal PowerShell code. This means that I can test for a single product and a single feature using a foreach loop and Test Cases like this

To test for a single product and multiple features I use this code

Because it is dynamically creating the values for the two parameters, I have to check that there are some results to test on line 23 as Compare-Object will throw an error if the object to be compared is empty. I need to do this because it is possible for the test to pick products and features in a combination that there are no fixes in the results.

The reason I have commented it as a risky fix is because if someone changes the code and Get-SQLDiagFix does not return any results then the test would not run and therefore there would be no information from this test that the code had a bug. However, in this suite of tests there are many tests that would fail in that scenario but be careful in your own usage.

I test for multiple products with a single feature and multiple products with multiple features like this

You can see all of the unit tests for the SQLDiagAPI module in my GitHub repository

The module is available on the PowerShell Gallery which means that you can install it using

Using Get-SQLDiagFix to get information from the SQL Server Diagnostic API with PowerShell

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


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.

01 - APIKey

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

01 - SQLDiagAPI Commands.png

You can find out more about the commands on the GitHub Repository  and the Help files are in the documentation.


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

02 - Get-Help Get-SQLDiagFix.png

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


which will return all 123 Fixes currently referenced in the API.

03 get-sqldiagfix.png

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

05 Get-SQLDiagFix OutGridView Search.gif

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


06 Get-SQLDiagProduct.png

You can either specify the product

Get-SQLDiagFix -Product 'SQL Server 2016 SP1' | Format-Table

07 Get-SQLDiagFix Product.png

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

08 - Get-SQLDiagFix Product Search.png

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


09 get-sqldiagfeature.png

You can see the fixes in a particular feature area using the Feature parameter with

Get-SQLDiagFix -Feature Spatial | Format-Table -AutoSize

10 - Get-SQLDiagFix by feature.png

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

11 - Get-SQLDiagFix by feature query.png

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

12 - Get-SQLDiagFix by feature adn product.png

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}
13 - Open a webpage.gif


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