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.

20171110_114933-compressor.jpg

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
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)

find-dbacommand.png

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

invoke

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 🙂

TSQL2sDay – Folks Who Have Made a Difference

tsql2sday

This months TSQL2sDay is an absolute brilliant one hosted by Ewald Cress

the opportunity to give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data.

Fabulous, fabulous idea Ewald, I heartily approve

Now this is going to be difficult. There are so many wonderful people in the #SQLFamily who are so gracious and generous and willing to share. I am also lucky enough to be part of the PowerShell community which is also equally filled with amazing people. I do not want to write a novel or a massive list of people, I don’t want to risk missing someone out (Ewald, I’m beginning to question whether ‘fabulous’ should become ‘tricky and challenging’ !!)

So after consideration I am only going to talk about 4 wonderful people and the effect they have had on my life, my career and my community involvement but know that I truly appreciate the input that all of the peoples have had and the amazing friendships that I have all over the world. There is no order to this list, these are 4 of the people in equal first with all the other people I haven’t mentioned. This post should really scroll sideways. Interestingly I noticed after writing this that they are in reverse chronological order in my life!

The Hair!

At PASS Summit this year many people came up to me and said “Hey, Beard ……..” The first person who called me that is an amazing inspiring bundle of talented energy called Chrissy LeMaire

Many moons ago, we exchanged messages over social media and email, chatted after a PowerShell Virtual Group presentation and then one day she asked me to join as an organiser for the Virtual Group.

When dbatools was in it’s infancy she asked me to help and since then has given me interesting challenges to overcome from introducing Pester and appveyor to the dbatools development process to creating continuous delivery to our private PowerShell gallery for our summit pre-con forcing me to learn and implement new and cool things. Our shared love of enabling people to do cool things with PowerShell is so much fun to do 🙂

She is so generous and giving of her time and knowledge and has an amazing capability to get things done, whether by herself or by encouraging and supporting others.

We have presented at many conferences together, both SQL and PowerShell and we have the best of times doing so. It is so refreshing to find someone that I am comfortable presenting with and who has the same passion and energy for inspiring people. (It’s also fun to occasionally throw her off her stride mid-presentation (Thank you Cathrine 🙂 )

I am proud to call her my buddy. You are so inspiring Chrissy.

Thank you Ma’am

Amazing Couple

A few months after becoming a DBA I was the only DBA at the company as the others all left for various reasons. I was drowning in work, had no idea what I should be doing. I knew I didn’t have the knowledge and during that time I began to be aware of the SQL community and all the fine resources that it provides.

I then found out about a local user group and emailed the leader Jonathan Allen (He surprised me by reminding of this during our pre-con in Singapore a couple of weeks ago!) Jonathan and his wife Annette run the SQL South West user group and are also members of the SQL Bits committee, Annette is also the regional mentor for the UK. They give an awful amount of time and effort to the SQL Community in the UK. It took a few months before I even had the time to attend a user group and in those early days they both answered my naive questions and passed on so much of their technical knowledge and methodology to me and I soaked it up.

Later on, they invited me to help them to organise SQL Saturday Exeter, encouraged me to speak, gave me fabulous feedback and pointers to improve, encouraged me to volunteer for SQL Bits and have been incredibly supportive. I love them both very much. Neither like having their photo taken so I can’t embarrass them too much.

Next time you see them give them a hug.

Thank you J and A

The First One

Andrew Pruski dbafromthecold and SQL Containers Man

At the time I am talking about he was not a member of the SQL Community although he possessed all of the qualities that describe such a person. Now he is an established blogger and speaker and attender of SQL events.

He is one of the DBA’s who left me on my own!! He is the first SQL DBA I ever worked with. The person who taught me all those important first bits of knowledge about being a SQL DBA. He imparted a great amount of knowledge in a few months with great patience to an eager newbie.

More than that, he showed me that to succeed in IT, you need to do more than just an everyday 9-5, that it requires more time than that. He instilled in me (without realising it) a work ethic and a thirst for doing things right and gaining knowledge that I still have today. He inspired me when I was faced with trying to understand the mountain of knowledge that is SQL Server that it was possible to learn enough. He taught me the importance of testing things, of understanding the impact of the change that is being made. He showed me how to respond in crises and yet was still willing to share and teach during those times.

He has had a greater impact on me than he will ever know and I have told him this privately many times. I will never forgive him for abandoning me all those years ago and yet that is a large part of what made me who I am today. I was forced to have to deal with looking after a large estate by myself and needed to learn to automate fast and he just about left me with the skills to be able to accomplish that.

Massive shout out to you fella. Thank you

All the Others

Seriously, there are so many other people who I wish I could thank.

Every single one of you who blogs or speaks or records webinars that I have watched – thank you.

All of the organisers who ensure that events happen – thank you

All of the volunteers who assist at those events – thank you.

That group of amazing European speakers at the first SQL Saturday Exeter I attended. The cool group, my wife still reminds me of how I came home from that event so inspired by them. How incredibly generous and welcoming they were and how they welcomed me into their group even though I didn’t feel worthy to share their table. They taught me about the lack of egos and humbleness that defines the SQL family. I am proud to call them my friends now. Thank You (You know who you are)

We have a great community, may its ethos continue for a long time.

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

Compare-Object

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

agentjobcompare.png

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
comparison.png
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
sortedjobcompare.png
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

datecompare.png
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

20171031_083328.jpg

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.

dbatools with SQL on Docker and running SQL queries

I had a question from my good friend Andrew Pruski dbafromthecold on twitter or SQL Container Man as I call him 🙂

How do you guys run SQL Commands in dbatools

I will answer that at the bottom of this post, but during our discussion Andrew said he wanted to show the version of the SQL running in the Docker Container.

Thats easy I said. Here’s how to do it

You need to have installed Docker first see this page You can switch to using Windows containers right-clicking on the icon in the taskbar and choosing the command. If you have not already, then pull the SQL 2017 SQL image using

This may take a while to download and extract the image but its worth it, you will be able to spin up new SQL instances in no time

You can create a new SQL Docker container like this


In only a few seconds you have a SQL 2017 instance up and running (Take a look at Andrews blog at dbafromthecold.com for a great container series with much greater detail)

Now that we have our container we need to connect to it. We need to gather the IPAddress. We can do this using docker command docker inspect but I like to make things a little more programmatical. This works for my Windows 10 machine for Windows SQL Containers. There are some errors with other machines it appears but there is an alternative below


Those two lines of code (and several lines of comments) puts the results of the docker inspect command into a variable and then uses regex to pull out the IP Address

If you are getting errors with that you can also use

Thanks Andrew 🙂

Now we just need our credentials to connect to the instance


and we can connect to our SQL container

and get the version

and many many other properties, just run

to see them. At the bottom, you will see a ScriptMethod called Query, which means that you can do things like


Which looks like

It’s slightly different with a Linux SQL container. Switch Docker to run Linux containers by right-clicking on the icon in the taskbar and choosing the command to switch.
If you haven’t already pull the Linux SQL image

and then create a container


Now we just need to connect with localhost and the port number which we have specified already and we can connect again


Of course, this isn’t restricted just Connect-DbaInstance you can do this with any dbatools commands

Go and explore your Docker SQL conatiners with dbatools 🙂

You can get it using

and find commands with

Don’t forget to use Get-Help with the name of the command to get information about how to use it

Enjoy 🙂

A Pretty PowerBi Pester Results Template File

I have left the heat and humidity of Singapore where I have been presenting at the PowerShell Conference Asia and DevOpsDays Singapore to travel to Seattle for PASS Summit. During my Green is Good – Red is Bad session someone asked me if the PowerBi that I showed at the end would work with any Pester Test Results object and I said (without thinking) that it would.

It turns out that the PowerBi that I had set up for that session will work with my function to run Pester Tests against an Ola Hallengren installation but some of the formatting and custom columns were specific to that test.

I said that I would share a Power Bi file that people could plug any Pester Test Results into. This is the first iteration of that. I doubt that it will work for every single test but I think it will be a good starting point for people to use.

This is how to use it

Download the file from here.

Run your Pester Tests using the PassThru Parameter and set the results to a variable, you can also use the Show Parameter to reduce the output of the tests to the screen (and also speed up the tests)

Then we convert the $PesterResults object into a JSON file

Open the Power Bi file you downloaded

Click Home

then the words “Edit Queries”

then data source settings,

highlight the filename and click change source

then navigate to the JSON file you just created, click ok and close and the apply changes.

Which will load the data from the JSON file and display your pester results. You can then save this file with a new name and keep the template for other tests.

It’s not going to be perfect

It’s not going to work in all circumstances and I expect that with some test results it will display the results in a less than optimal manner but you should be able to modify this to suit your needs.

Please give it a try and see how you get on

Here is a sample report created with Demo 1 from my Green is Good session

You can click around and change the data you can see and also look at the other 4 pages

Here is another one that I created using my dbatools-scripts repo and a config file. Again, have a click around and see what it does.

 

I also created a quick video showing the process too which I will upload when I am not at 35000 feet!!

Enjoy 🙂 Also, let me know if you think it would be better to have the file in Github which would allow contributions but it would only be seen as a binary file and therefore merging will be difficult. I am happy to do so.

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!