Pester 4.2.0 has a Because…… because :-)

I was going through my demo for the South Coast User Group meeting tonight and decided to add some details about the Because parameter available in the Pester pre-release version 4.2.0.

To install a pre-release version you need to get the latest  PowerShellGet module. This is pre-installed with PowerShell v6 but for earlier versions open PowerShell as administrator and run

You can try out the Pester pre-release version (once you have the latest PowerShellGet) by installing it from the PowerShell Gallery with

There are a number of improvements as you can see in the change log I particularly like the

  • Add -BeTrue to test for truthy values
  • Add -BeFalse to test for falsy values

This release adds the Because parameter to the all assertions. This means that you can add a reason why the test has failed. As JAKUB JAREŠ writes here

  • Reasons force you think more
  • Reasons document your intent
  • Reasons make your TestCases clearer
  • So you can do something like this

Which gives an error message like this 🙂

As you can see the Expected gives the expected value and then your Because statement and then the actual result. Which means that you could write validation tests like

and get a result like this

Or if you were looking to validate your SQL Server you could write something like this

or maybe your security policies allow Windows Groups as logins on your SQL instances. You could easily link to the documentation and explain why this is important. This way you could build up a set of tests to validate your SQL Server is just so for your environment

Just for fun, these would look like this

and the code looks like

This will be a useful improvement to Pester when it is released and enable you to write validation checks with explanations.

Chrissy has written about dbachecks the new up and coming community driven open source PowerShell module for SQL DBAs to validate their SQL Server estate. we have taken some of the ideas that we have presented about a way of using dbatools with Pester to validate that everything is how it should be and placed them into a meta data driven framework to make things easy for anyone to use. It is looking really good and I am really excited about it. It will be released very soon.

Chrissy and I will be doing a pre-con at SQLBits where we will talk in detail about how this works. You can find out more and sign up here

Converting a Datarow to a JSON object with PowerShell

This is just a quick post. As is frequent with these they are as much for me to refer to in the future and also because the very act of writing it down will aid me in remembering. I encourage you to do the same. Share what you learn because it will help you as well as helping others.

Anyway, I was writing some Pester tests for a module that I was writing when I needed some sample data. I have written before about using Json for this purpose This function required some data from a database so I wrote the query to get the data and used dbatools to run the query against the database using Get-DbaDatabase

Simple enough. I wanted to be able to Mock $variable. I wrapped the code above in a function, let’s call it Run-Query

Which meant that I could easily separate it for mocking in my test. I ran the code and investigated the $variable variable to ensure it had what I wanted for my test and then decided to convert it into JSON using ConvertTo-Json

Lets show what happens with an example using WideWorldImporters and a query I found on Kendra Littles blogpost about deadlocks

If I investigate the $variable variable I get

data results

The results were just what I wanted so I thought I will just convert them to JSON and save them in a file and bingo I have some test data in a mock to ensure my code is doing what I expect. However, when I run

I get

json error.png

and thats just for one row!

The way to resolve this is to only select the data that we need. The easiest way to do this is to exclude the properties that we don’t need

which gave me what I needed and a good use case for -ExcludeProperty

json fixed.png

Handling Missing Instances when Looping with Pester

In my previous posts about writing your first Pester Test and looping through instances I described how you can start to validate that your SQL Server is how YOU want it to be.

Unavailable machines

Once you begin to have a number of tests for a number of instances you want to be able to handle any machines that are not available cleanly otherwise you might end up with something like this.

01 - error.png

In this (made up) example we loop through 3 instances and try to check the DNS Server entry is correct but for one of them we get a massive error and if we had created a large number of tests for each machine we would have a large number of massive errors.

Empty Collection

If we don’t successfully create our collection we might have an empty collection which will give us a different issue. No tests

02 - no tests.png

If this was in amongst a whole number of tests we would not have tested anything in this Describe block and might be thinking that our tests were OK because we had no failures of our tests. We would be wrong!

Dealing with Empty Collections

One way of dealing with empty collections is to test that they have more than 0 members

Notice the backtick ` before the $ to escape it in the Write-Warning. An empty collection now looks like
03 - uh-oh.png
Which is much better and provides useful information to the user

Dealing with Unavailable Machines

If we want to make sure we dont clutter up our test results with a whole load of failures when a machine is unavailable we can use similar logic.

First we could check if it is responding to a ping (assuming that ICMP is allowed by the firewall and switches) using

This will just try one ping and do it quietly only returning True or False and if there are any errors it shouldn’t mention it

In the example above I am using PSRemoting and we should make sure that that is working too. So whilst I could use

this only checks if a WSMAN connection is possible and not other factors that could be affecting the ability to run remote sessions. Having been caught by this before I have always used this function from Lee Holmes (Thank you Lee) and thus can use

which provides a result like this

04 - better handling.png

Which is much better I think 🙂

Let dbatools do the error handling for you

If your tests are only using the dbatools module then there is built in error handling that you can use. By default dbatools returns useful messages rather than the exceptions from PowerShell (You can enable the exceptions using the -EnableExceptions parameter if you want/need to) so if we run our example from the previous post it will look like

05 - dbatools handling.png

which is fine for a single command but we don’t really want to waste time and resources repeatedly trying to connect to an instance if we know it is not available if we are running multiple commands against each instance.

dbatools at the beginning of the loop

We can use Test-DbaConnection to perform a check at the beginning of the loop as we discussed in the previous post

Notice that we have used -WarningAction SilentlyContinue to hide the warnings from the command this tiime. Our test now looks like
06 - dbatools test-dbaconnection.png
Test-DbaConnection performs a number of tests so you can check for ping SQL version, domain name and remoting if you want to exclude tests on those basis

Round Up

In this post we have covered some methods of ensuring that your Pester Tests return what you expect. You don’t want empty collections of SQL Instances making you think you have no failed tests when you have not actually run any tests.

You can do this by checking how many instances are in the collection

You also dont want to keep running tests against a machine or instance that is not responding or available.

You can do this by checking a ping with Test-Connection or if remoting is required by using the Test-PSRemoting function from Lee Holmes

If you want to use dbatools exclusively you can use Test-DbaConnection

Here is a framework to put your tests inside. You will need to provide the values for the $Instances and place your tests inside the Describe Block

2 Ways to Loop through collections in Pester

In my last post I showed you how to write your first Pester test to validate something. Here’s a recap

  • Decide the information you wish to test
  • Understand how to get it with PowerShell
  • Understand what makes it pass and what makes it fail
  • Write a Pester Test

You probably have more than one instance that you want to test, so how do you loop through a collection of instances? There are a couple of ways.

Getting the Latest Version of the Module

The magnificent Steve Jones wrote about getting the latest version of Pester and the correct way to do it. You can find the important information here

Test Cases

The first way is to use the Test Case parameter of the It command (the test) which I have written about when using TDD for Pester here

Lets write a test first to check if we can successfully connect to a SQL Instance. Running

shows us that the Test-DbaConnection command is the one that we want from the dbatools module. We should always run Get-Help to understand how to use any PowerShell command. This shows us that the results will look like this

01 - gethelp test-dbaconnection

So there is a ConnectSuccess result which returns True or false. Our test can look like this for a single instance

which gives us some test results that look like this

successful test.png
which is fine for one instance but we want to check many.
We need to gather the instances into a $Instances variable. In my examples I have hard coded a list of SQL Instances but you can, and probably should, use a more dynamic method, maybe the results of a query to a configuration database. Then we can fill our TestCases variable which can be done like this
Then we can write our test like this
Within the title of the test we refer to the instance inside <> and add the parameter TestCases with a value of the $TestCases variable. We also need to add a Param() to the test with the same name and then use that variable in the test.
This looks like this
Testcases test.png

Pester is PowerShell

The problem with  Test Cases is that we can only easily loop through one collection, but as Pester is just PowerShell we can simply use ForEach if we wanted to loop through multiple ones, like instances and then databases.

I like to use the ForEach method as it is slightly quicker than other methods. It will only work with PowerShell version 4 and above. Below that version you need to pipe the collection to For-EachObject.

Lets write a test to see if our databases have trustworthy set on. We can do this using the Trustworthy property returned from Get-DbaDatabase. 

We loop through our Instances using the ForEach method and create a Context for each Instance to make the test results easier to read. We then place the call to Get-DbaDatabase inside braces and loop through those and check the Trustworthy property

and it looks like this


So there you have two different ways to loop through collections in your Pester tests. Hopefully this can help you to write some good tests to validate your environment.
Happy Pestering

Spend a Whole Day With Chrissy & I at SQLBits

If you would like to spend a whole day with Chrissy LeMaire and I at SQLBits in London in February – we have a pre-con on the Thursday
You can find out more about the pre-con
and you can register at

Write Your first Pester Test Today

I was in Glasgow this Friday enjoying the fantastic hospitality of the Glasgow SQL User Group @SQLGlasgow and presenting sessions with Andre Kamman, William Durkin and Chrissy LeMaire

I presented “Green is Good Red is Bad – Turning your checklists into Pester Tests”. I had to make sure I had enough energy beforehand so I treated myself to a fabulous burger.


Afterwards I was talking to some of the attendees and realised that maybe I could show how easy it was to start writing your first Pester test. Here are the steps to follow so that you can  write your first Pester test

Decide the information you wish to test
Understand how to get it with PowerShell
Understand what makes it pass and what makes it fail
Write a Pester Test

The first bit is up to you. I cannot decide what you need to test for on your servers in your environments. Whatever is the most important. For now pick one thing.

Logins – Lets pick logins as an example for this post. It is good practice to disable the sa account is advice that you will read all over the internet and is often written into estate documentation so lets write a test for that

Now we need the PowerShell command to return the information to test for. We need a command that will get information about logins on a SQL server and if it can return disabled logins then all the better.

As always when starting to use PowerShell with SQL Server I would start with dbatools if we run Find-DbaCommand we can search for commands in the module that support logins. (If you have chosen something none SQL Server related then you can use Get-Command or the internet to find the command you need)


Get-DbaLogin . That looks like the one that we want. Now we need to understand how to use it. Always always use Get-Help to do this. If we run

we get all of the information about the command and the examples. Example 8 looks like it will help us

get-dbalogin example

So now try running the command for our disabled sa account

disabled sa account

So we know that if we have a disabled sa account we get a result. Lets enable the sa account and run the command again

not disabled.png

We don’t get a result. Excellent, now we know what happens for a successful test – we get one result and for failed test we get zero results. We can check that by running

login count

The first one has the account disabled and the second one not. So now we can write our Pester Test. We can start with a Describe Block with a useful title. I am going to add a context block so that you can see how you can group your tests.

describe context

and then we will write our test. Pester Tests use the It keyword. You should always give a useful title to your test

it should

Now we can write our test. We know that the command returns one result when we want it to pass so we can write a test like this

login test.png

The code I have added is

which is
  • the code for getting the information about the thing we wanted to test (The count of the disabled sa logins on the instance)
  • a pipe symbol |
  • The Should key word
  • The Be keyword
  • and the result we want to pass the test (1)

Ta Da! One Pester test written. You can run the test just by highlighting the code and running it in VS Code (or PowerShell ISE) and it will look like this for a passing test

passing test

It is better to save it for later use and then call it with Invoke-Pester


So now you can write your first Pester test. Just find the PowerShell to get the information that you need, understand what the results will be for passing and failing tests and write your test 🙂

Getting the Latest Version of the Module

The magnificent Steve Jones wrote about getting the latest version of Pester and the correct way to do it. You can find the important information here

Spend a Whole Day With Chrissy & I at SQLBits

If you would like to spend a whole day with Chrissy LeMaire and I at SQLBits in London in February – we have a pre-con on the Thursday
You can find out more about the pre-con
and you can register at

Comparing Agent Jobs across Availability Group Replicas with PowerShell

On the plane home from PAS Summit I was sat next to someone who had also attended and when he saw on my laptop that I was part of the SQL Community we struck up a conversation. He asked me how he could compare SQL Agent Jobs across availability group replicas to ensure that they were the same.

He already knew that he could use Copy-DbaAgentJob from dbatools to copy the jobs between replicas and we discussed how to set up an Agent job to accomplish this. The best way to run an Agent Job with a PowerShell script is described here


I told him about Compare-Object a function available in PowerShell for precisely this task. Take these two SQL instances and their respective Agent Jobs


So we can see that some jobs are the same and some are different. How can we quickly and easily spot the differences?

Those three lines of code will do it. The first two get the agent jobs from each instance and assign them to a variable and the last one compares them. This is the output
The arrows show that the first three jobs are only on the Bolton instance and the bottom three jobs are only on the default instance.

What If ?

 Another option I showed was to use the -WhatIf switch on Copy-DbaAgentJob. This parameter is available on all good PowerShell functions and will describe what the command would do if run WARNING – If you are using the old SQLPS module from prior to the SSMS 2016 release -WhatIf will actually run the commands so update your modules.
We can run

and get the following result

which shows us that there are two jobs on Rob-XPS which would be created on the Bolton instance

And if they have been modified?

Thats good he said, but what about if the jobs have been modified?
Well one thing you could do is to compare the jobs DateLastModified property by using the -Property parameter and the passthru switch
This is going to return the jobs which are the same but were modified at a different time
so that you can examine when they were changed. Of course the problem with that is that the DateLastModified is a very precise time so it is pretty much always going to be different. We can fix that but now it is a little more complex.

Just the Date please

We need to gather the jobs in the same way but create an array of custom objects with a calculated property like this
and then we can compare on the Date field. The full code is

This will look like this

Which is much better and hopefully more useful but it only works with 2 instances

I have more than 2 instances

So if we have more than 2 instances it gets a little more complicated as Compare-Object only supports two arrays. I threw together a quick function to compare each instance with the main instance. This is very rough and will work for now but I have also created a feature request issue on the dbatools repository so someone (maybe you ?? ) could go and help create those commands

which looks like this. It’s not perfect but it will do for now until the proper commands are created

compare agent jobs.png

Using Plaster To Create a New PowerShell Module

Chrissy, CK and I presented a pre-con at PASS Summit in Seattle last week


Tracey Boggiano T | B came along to our pre-con and afterwards we were talking about creating PowerShell modules. In her blog post she explains how she creates modules by copying the code from another module (dbatools in this case!) and altering it to fit her needs. This is an absolutely perfect way to do things, in our pre-con we mentioned that there is no use in re-inventing the wheel, if someone else has already written the code then make use of it.

I suggested however that she used the PowerShell module Plaster to do this. We didnt have enough time to really talk about Plaster, so Tracy, this is for you (and I am looking forward to your blog about using it to 😉 )

What is Plaster?

Plaster is a template-based file and project generator written in PowerShell. Its purpose is to streamline the creation of PowerShell module projects, Pester tests, DSC configurations, and more. File generation is performed using crafted templates which allow the user to fill in details and choose from options to get their desired output.

How Do I Get Plaster?

The best way to get Plaster is also the best way to get any PowerShell module, from the PowerShell Gallery

You can just run

If you get a prompt about the repository not being trusted, don’t worry you can say yes.

Following PowerShell’s Security Guiding Principles, Microsoft doesn’t trust its own repository by default. The advice as always is never trust anything from the internet even if a bearded fellow from the UK recommends it!!

The PowerShell Gallery is a centralised repository where anyone can upload code to share and whilst all uploads are analyzed for viruses and malicious code by Microsoft, user discretion is always advised. If you do not want to be prompted every time that you install a module then you can run

if you and/or your organisation think that that is the correct way forward.

What Can We Do With Plaster?

Now that we have installed the module we can get to the nitty gritty. You can (and should) use Plaster to automate the creation of your module structure. If you are going to something more than once then automate it!

I created a repository for my Plaster Template You are welcome to take it and modify it for your own needs. I created a folder structure and some default files that I always want to have in my module folder

module framework.png

So in my template I have created all of the folders to organise the files in the way that I want to for my modules. I have also included the license file and some markdown documents for readme, contributing and installation. If we look in the tests folder

tests folder.png

There are some default test files included as well.

But Plaster is more than just a file and folder template repository, if we look in the installation markdown file,  it looks like this

We can paramatarise the content of our files. This will create a very simple markdown showing how to find and install the module from the PowerShell Gallery which saves us from having to type the same thing again and again. Lets see how to do that

The Manifest XML file

The magic happens in the manifest file You can create one with the New-PlasterManifest command in the template directory

This will create a PlasterManifest.xml file that looks like this
You can see that the parameters and content tags are empty. This is where we will define the parameters which will replace the tokens in our files and the details for how to create our module folder.

Plaster Parameters

At present my parameters tag looks like this
So we can set up various parameters with their names and data types defined and a prompt and if we want a default value.
We can then use
in our files to make use of the parameters.

Plaster Content

The other part of the manifest file to create is the content. This tells Plaster what to do when it runs.

Mine is split into 3 parts

We can provide messages to the user with the message tag. I create the folders using the filesource tag
This part creates all of the required files. You can see that the static files (those which do not require any sort of parameterisation for the contents use the same file source tag as the folders with the source defined. The files that have content which is parameterised use a tag of templateFile Source telling Plaster to look inside there for the tokens to be replaced.
The last part of the content creates the module manifest.
which I have filled in with the parameters for each of the values.

Creating a new module at the command line

Now you can easily create a module with all of the required folders and files that you want by creating a directory and running

which looks like this

Its that easy 🙂

Create a module without prompts

You can also create a module without needing to answer prompts. We can prefill them in our parameter splat
Which will look like this

Make Your Own

Hopefully this have given you enough information and shown you how easy it is to automate creating the framework for your new PowerShell modules and parameterising them. Let me know how you get on and share your examples

Further Reading

Kevin Marquettes blog post is an excellent and detailed post on using Plaster which you should also read for reference as well as David Christians post which has some great content on adding user choice to the parameters enabling one plaster template to fulfill multiple requirements.