TSQL2sDay – Get-PostRoundup

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

#TSQL2sDay – Starting Out with PowerShell

tsql2sdayThis months TSQL2sday is hosted by me!

Surprise! – I chose PowerShell

I am really looking forward to seeing what other people post. Some advanced scripts that will help you and show you how you can use PowerShell to save you time and hopefully some beginner posts explaining experiences or showing you how to start with PowerShell and SQL Server. I decided to go with the latter.

First though a warning.

PowerShell is another language, you are not going to be as proficient in a new language as you are in the language you spend all day working with. You will have to go through the learning curve and you will have to understand how to interpret errors. There is a learning curve just like with any language. Undoubtedly you will get frustrated at times. Reach out for help. Use twitter, use the #powershellhelp in the SQL Server Community Slack channel. There are many other places and plenty of people who will be glad to help you.

Open the Editor

If you are using a Windows machine you will have PowerShell installed. You will find it in your start menu on Windows 7 under All Programs, Accessories, Windows PowerShell folder, and then click Windows PowerShell or Windows PowerShell ISE. On Windows 8 or 10 search for PowerShell.

PowerShell or PowerShell ISE? Use PowerShell instead of cmd.exe for command line usage and ISE for developing scripts, functions and modules. (However, read on before making your choice)

PowerShell on Other O/S’s (and Windows!)

Just as SQL Server is available on Linux PowerShell is also available cross-platform, you can run and edit PowerShell on Linux and on Mac natively. The best editor to use is VS Code which is also available cross-platform. In fact, as Microsofts David Wilson says in this blog post

The PowerShell ISE has been the official editor for PowerShell throughout most of the history of Windows PowerShell. Now with the advent of the cross-platform PowerShell Core, we need a new official editor that’s available across all supported OS platforms and versions. Visual Studio Code is now that editor and the majority of our effort will be focused there.

So my advice is, whichever operating system you are using, use VS Code to write, edit, debug and run your PowerShell as that is the editor where Microsoft are spending the majority of their effort in development. Follow the instructions here to install You can also use it with many other languages including T-SQL. I find it a very useful tool

Where to start?

Well that depends what you want to do. Start by reading. Start by trying to accomplish something in PowerShell even if you know how to do it another way. This will also help you to realise when you are trying to use a hammer to put in a screw (using the wrong tool for the job)

What Command?

You don’t know where to start so how do you know the command to use? Here is a first command. You will use it often. I use it everyday still despite using PowerShell daily for many years. But don’t run it yet!

PowerShell is quite intuitive in it’s command naming. It uses a Verb-Noun syntax. You know without me explaining what this command will do. It will “Get” the “Command” (s). PowerShell uses singular nouns for its commands. I have 8000+ commands on my machine so it would have overloaded you with commands if you had run it without filters!

We use * as a wildcard in PowerShell so we can use that with our Get-Command command to find some commands to run. Type Get-Com and then hit the tab button. This will not only help you as it will auto-complete, it will reduce the number of errors as it will only auto-complete correct command names :-).

This will show all commands which end with service. The results will have some different headings. The screenshot below shows just one command to reduce confusion.

03 - get-service.png

The type of command, the name of the command, the version of the command and the module it is in. PowerShell uses modules to group commands together and to enable you to install the ones that you require from a repository like the PowerShell Gallery (more on this later).

You can find all of the commands in a module with the Get-Command command like this

This will show you all of the commands in the management module which may be a good place to start exploring. In this module for example

  • Test-Connection is like ping but better
  • Test-Path tests the existence of files and file paths
  • *-Service works with services
  • *-Process works with processes
  • *-Computer for power options for a computer
  • Get-ComputerInfo which will give you computer information (surprisingly 🙂 )

This is also something to remember in the future when you want to know what commands a module has or what the precise name of the command is.

Maybe once you install the dbatools module, the sqlserver module or the SSRS module??

HOMEWORK – Examine the names of commands in other modules that you find in

or for more choices

How do I use that command though?

You need to read! PowerShell comes with a lot of documentation, a lot of it is available on your machine. Lets introduce another command

As you can imagine, this gets the help for a command or returns help topics about PowerShell. Again, I use this every single day and you should too. Lets start with the first one. Lets get help for the Get-ChildItem command

Now, some of you may not get very much returned and a message at the bottom which says

Get-Help cannot find the Help files for this cmdlet on this computer. It is displaying only partial help.
— To download and install Help files for the module that includes this cmdlet, use Update-Help.
— To view the Help topic for this cmdlet online, type: “Get-Help Get-ChildItem -Online” or
go to https://go.microsoft.com/fwlink/?LinkID=113308.

When you have something else to do I would recommend running Update-Help as it suggests (It can take a few minutes to run) but for now make use of the -Online switch.

HOMEWORK – Run Update-Help

This is available for all of the Microsoft modules and some of the better open source modules, dbatools is an example of a (brilliant) community module which has -Online URLs for it’s commands. Running Get-Help (with or without the -Online switch) will give you plenty of information about the command, the switches available, the inputs and outputs.

Maybe start by using the -examples switch

will show you some examples of how to use the command

05 gethelp examples

Now you can start to explore the file system with this command (also the SQL Server, Registry, Certificate Store and many more things)

I also like to use the ShowWindow switch

which will open up the help in a separate window which can be useful when you are writing a script and it is searchable

04 - gethelp showwindo

But Chrissy prefers to use the -Detailed switch

You can decide which way you like best.

Now you know how to find a command and hot to find out how to use it. If you are using VS Code (or ISE) you have access to Intellisense and snippets to help you with the syntax. You can run a command and get some results.

HOMEWORK – Use Get-Help to understand how to use commands. ( Extra merit marks for teachers pets who read the topics they find using Get-Help About_*) Also, instant demerit points throughout the course (your career) for students who do not use Get-Help when using any command for the first time.

Hopefully you are confident enough to have run some commands. If you are worried about breaking things only run the commands which start with Get- as these should only be returning information or objects and not changing anything.

This isn’t cmd 

This isn’t cmd. What you are getting back is not text.

It is an object.

Unless you choose to use one of the formatting commands or the command you are using outputs using a formatting command (pro-tip don’t do that) then what you will receive as the results of your command is an object.

This means that there is more that you can do with the results than just have them in text format. You want Text File,JSON, XML, CSV, Excel output or to write to a database? Look here for ideas

Objects,Objects,Objects

Objects are awesome. Lets start with a SQL Server Instance object to show you how to explore objects. You will need to install the most useful PowerShell module you will find if you are working with SQL Server – dbatools

Disclosure – I am a contributor to the open source module dbatools. I am presenting a full day workshop in Singapore for the PowerShell Conference Asia in October. I am also proctoring at Chrissy and CK’s PASS Summit in Seattle. I highly recommend that you come and join us at one of those events if you can to further learn how you can use PowerShell to administer SQL Server

You can install it from the PowerShell Gallery using

You will be asked if you want to trust the repository, say Yes. If this doesn’t work on your machine (you are on an older version of PowerShell) follow the instructions on this page.  Or ask for help in #powershellhelp in Slack

Now that the module is installed we can create a SQL Server Instance object by assigning the results of a command to a variable.

A variable in PowerShell is designated by a $.

I suggest that you get used to following this step as well. This is a great way of exploring the results of any command and understanding what you can do with it.

We will use the Get-Member command. Another one that I use every single day.

Warning – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to general tasks without “coding”

This is an example to get you comfortable with PowerShell by using a target that you are comfortable with and can recognise.

Now that we have an object we can explore it

By just calling the variable it will display the default properties

06 - default properties.png

If you want to see what else is available you can use Get-Member You just pipe | to Get-Member

This will show you all of the events, methods and properties available on the object.  Methods are really useful, enabling you to do things like start agent jobs or enumerate permissions or members

If you wish to see all of the processes that are running on the instance

Out-GridView is a useful command allowing you to see the results of your command in a graphical format and easily filter using the search bar at the top.

07 - ogv.png

Other things you can do include reading the errorlog

08 - errorlog.png

Get the connections to a database

Get the current Traceflags

The properties that are available at this level are similar to the view that you see in SSMS Object Explorer for the instance. You can see a databases folder in SSMS and you can expand it. You can do the same in PowerShell

That is going to just show you the names of the databases on that instance. You can do the same thing using the pipe | again and Select-Object

This will pass the Databases array of objects “along the pipeline” and then you can use Select-Object to choose them. Multiple properties can be chosen with commas.

09 - databases.png

PowerShell will let you select things that do not exist so for example

does not return an error but some of the columns are empty 🙂 You need to use the names of the properties returned from Get-Member in your Select statement (Hmm, sounds a bit like T-SQL)

18 select.png

You can look at a single database default properties using

You can then use Get-Member to explore deeper and deeper.

You can also look at the Agent from the instance level object

HOMEWORK – Using the code above explore the $SMO object, look at the databases, tables, the columns and the indexes. Explore other properties and select them. (Extra merit points for exploring the SQL Server like a file system using the SQLServer drive. Start with cd SQLSERVER:\ and use Get-ChildItem or its alias dir or ls)

Warning Reminder – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to do general tasks without “coding”

But this is a very useful way of understanding and exploring using PowerShell with a type of object that you understand. You will use these methods all of the time and when you need to accomplish the next thing you have the tools. Already. Now.

Recap

You should take from this for the future

  • Get-Command *search* – Find a Command
  • Get-Help NameOfCommand – How do I use the Command ?
  • $var = Some Command here – Set the output of a command to a variable
  • $var | Get-Member – What Events, Methods, Properties do I have on this object
  • $var | Select Property1, Property2, Property3

That’s All Code – Where’s The Easy Buttons ?

There are two answers to this.

Firstly, yes it’s all code, but you will find that code is the future. Every time you run the same piece of code it will do the same thing, every time a junior DBA follows the steps to manually do something in a GUI like SSMS they may make a mistake and a different mistake each time. As DBAs we use stored procedures, views, functions and more, they are all code. Using code is vital for automation, for making things easy, for reducing the risk of mistakes, for getting rid of the mundane.

It is also required for DevopsAgileScrumContinuousIntegrationDeliveryWhatsTheNextBuzzWord which is all the rage (and also a lot of fun with interesting challenges)

As estates get bigger and bigger the GUI becomes less useful to you.

Secondly, remember my warning at the top of this post? There is no easy button. You will have to learn new things you will make mistakes and get errors, you need to ask for help

I Have An ERROR !

Don’t be disheartened, errors happen. The red text looks scary and frightening but its not.

Read the Error

Lets use Microsofts sqlserver module. There are all sorts of ways to get it. If you have SSMS you probably have it already. Lets check

A result like this means that you have it

10 - get-module.png

If not

Again you will be asked if you want to trust the repository, say yes!

If I run

I get

11 - error.png

That error is fairly easy to understand. I can’t connect to an instance that isn’t running

If I run

I get a different error

12 sql login error.png

The Get-SqlLogin from Microsofts sqlserver module returns an error if it cannot find the specified login name. The important part of this error message is ObjectNotFound. That tells us what has happened

If we look at the logins

13 - Logins.png

and choose one that exists everything is good

14 one login.png

HOMEWORK – Here is another object to explore with Get-Member. Explore the Login object

For example

15 - login script.png

But back to errors. Sometimes it isn’t quite so obvious. I have altered my Create-Database function so that it shows an error instead of a helpful message.

16 - db error.png

This is an example of the sort of error message that you might not be able to decipher so easily. Even if you read the message it says Exception calling .ctor with 2 arguments. How can you, as a new PowerShell user work out what has happened?

Objects

Remember, PowerShell is great because of objects? The errors are objects too. Lets have a look at the error in full. This is another piece of code that I still use every single day

The $error variable holds an array of error objects for that session. The [0] targets the 1st object in the array which is the latest. We pipe that to fl which is an alias for Format-List. Normally I am very clear about not using aliases in my blog posts as I believe that you should not use aliases in your scripts and functions (This is due to a bad experience trying to google for % to find out what it was in PowerShell many years ago). However, you are only going to use this at the command line so its ok.

The results are

17 error.png

Once you read the error, you will see that there is a generic connection failed error message which you will be used to seeing and now you can go about fixing it.

Errors aren’t scary. You just have to read them. Sometimes you have to expand them to read them. Once you have read them don’t forget that there is also a lot of help out there on the internet. I recommend the #powershellhelp channel in the slack but with the full error message you will be able to get some useful results from whatever your choice of helpful solutions is.

That’s exactly 3000 words – which is slightly (!) more than I intended to write and a lot to read, so if you have got all the way down here. Congratulations, when you see me at Summit or at a SQL Saturday come up and tell me and I will give you a special sticker 🙂

Hopefully this has given you some guidance to starting to use PowerShell with SQL and how you can help yourself.

Good luck.

Automatically updating the version number in a PowerShell Module – How I do regex

I am presenting Continuous Delivery for your PowerShell Module to the PowerShell Gallery at PSDayUK in London. Go and register if you will be close to London on Friday 22nd September.

In 45 minutes we will

– Use Plaster to create our module framework
– Use GitHub for Version Control
– Use Pester to develop our module with TDD
– Use VSTS to Build, Test (with Pester) and Release our changes to the PowerShell Gallery

45 minutes will not give me much time to dive deep into what is done but I will release all of my code on GitHub.

One of the things I needed to accomplish was to update the version number in the module manifest file. I did this with some regex and this is how I achieved it.

I went to regex101.com and pasted in the contents of a module file into the test string box and start to work out what I need. I need the value after ModuleVersion = ‘ so I started like this

01 - regex101.png

Under the quick reference it has some explanations which will help you. Keep going until you have the correct value in the match information. In the image below you can see that it has a Group 1 in green which matches the value I want (0.9.13). There is also an explanation of how it has got there. This is what I shall use in the PowerShell script

01 - regex.png

Now we can go to PowerShell. First get the contents of the file

Then we use [regex]::matches() to get our value as shown below. I always like to write in the comments what the regex is doing so that I (or others) know what was intended. We also set the value to a type of Version. I reference the group 1 value that I saw in the website.

Next we need to add one to the version number

and then replace the value in the file, notice the Get-Content has braces around it

That’s how I do it and the process I use when I need regex!

Here is the full script

 

An easier way

My fabulous friend and MVP Ravikanth Chaganti has told me of a better way using Import-PowerShellDataFile

This command is available on PowerShell v5 and above. There is no need to use regex now 🙂 You can just get the manifest as an object and then use the Update-ModuleManifest to update the file

 

 

 

 

 

 

 

 

 

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

Using PowerShell to check if your password has been in a breach

We know that we need to keep our credentials secure. We know that we should not re-use our passwords across different services. Hopefully, by now, most readers of this blog are aware of Troy Hunts excellent free service https://haveibeenpwned.com/ which will notify you if your email has been found in a breach. If not, go and sign up now.

Recently Troy announced on his blog

This blog post introduces a new service I call “Pwned Passwords”, gives you guidance on how to use it and ultimately, provides you with 306 million passwords you can download for free and use to protect your own systems.

So I thought I would write a quick PowerShell script to make use of it and place it on the PowerShell Gallery

You can install it using

You will be asked if you want to add ‘C:\Program Files\WindowsPowerShell\Scripts’ to your PATH environment variable if this is the first script you have installed

Then load it into your session with a period and a space.

and check some old passwords. Take notice of the screen shot below in which Troy states that you should not send your password currently in use to any third party sites including this one.

There was a game on Twitter which involved finding ‘interesting’ passwords that people have used! Search for it, it’s nsfw by the way!

passwords.png

UPDATE

After posting this Jos made a comment

and Mathias added

Which is a good point. You don’t want you co-workers or friends seeing your Passwords over the shoulder. So I have updated the script to prompt for a Password and convert it to secure string and added a hash parameter as the API also allows you to pass the SHA1 hash of a password.

passwords2.png

Which is a bit better I think. Thank you guys.

UPDATE 2 – This actually broke the script meaning that every password came back as pwned as I was not decoding the securestring correctly. I have fixed this with version 1.2 which you can get if you have already installed the script by running

Thanks to Henkie and Russell for letting me know

There is also a good use case for us technical folk to assist our none-technical friends with their password usage. You can visit this page

https://haveibeenpwned.com/Passwords

and get them to put their old password in the box (look at the screenshot for advice on current passwords) and see if their password has been used in a breach or not and use this as a means to have a discussion about password managers

pwnedpasswords.png

 

Announcing PSDay.UK – Whats a PSDay?

On Thursday evening I attended the joint London WinOps and PowerShell User Group. It was an excellent evening with two great sessions by Jaap Brasser and Filip Verloy.

PSDay.UK

There was also an exciting announcement about PSDay.UK  https://psday.uk

PSDay.UK is a one day PowerShell event providing the opportunity for you to spend a whole day learning PowerShell from renowned experts from the UK and international speaking community. It will be held at

Skills Matter | CodeNode, 10 South Place, London, EC2M 7EB, GB

on

Friday 22nd September 2017  .ics

We will be running two tracks

  • PowerShell Zero to Hero
  • DevOps with PowerShell

Register your interest

Please go and visit the website and have a look and register your interest to get further notifications about the event.

Follow the @PSDayUK twitter account and Facebook page https://www.facebook.com/PSDayUK/ and keep yourself informed on this fantastic new event.

Want to Speak at PSDay.UK ?

We already have some fantastic speakers lined up but we would like to invite people to send us submissions for more sessions. If you have a PowerShell talk that will fit into one of the tracks and experience of delivering sessions at events please send us submissions via the website.
If you have questions about speaking feel free to contact me via twitter at @sqldbawithbeard

What is a PSDay ?

The International PowerShell community has three main global events which run over a number of days with top notch international speakers and Microsoft PowerShell team members, delivering in-depth information about the latest PowerShell trends and technologies, and connecting national communities with another.

There are a number of other PowerShell events that have been organised by wonderful volunteers in numerous countries and we feel there is an opportunity to create national events which complement the global events and help PowerShell passionates and professionals to get in touch and learn from another with a similar branding of PSDay.

We foresee PSDays to be smaller one day national events promoting speakers from the host country supported by other international speakers with the aim of increasing the exposure of national PowerShell user groups as well as providing excellent PowerShell training.

There will be a board of PowerShell community folk set up who will approve requests to use the PSDay name and shield logo providing the event is professionally organized and offer help with technical questions, viral marketing, and experience. We hope that this will enable people to set up their own PSDay in their own country and increase the exposure of the PowerShell community as well as PowerShell knowledge whilst sharing resources, knowledge, experience and skills and ensuring a good standard of PowerShell community national events.

Further details of this will be forthcoming and we welcome offers of assistance from people with relevant experience

 

 

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.

Pester

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