Green is good, Red is bad – Turning your Checklists into Pester Tests

Audio podcast:

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS
Leave us a review in iTunes

Target Audience:

This session is for DBAs specifically, but will be relevant to any technicians who use checklists


I was required to prove that I had successfully installed and configured a backup solution across a large estate. I had a number of success criteria that had to be met. Checking all of these by hand (eye) would have been error prone, so I wrote a test to do this for me and an easy for management to read HTML report using PowerShell and Pester.

The session has come from that situation and is about enabling you to provide an easy to read output to quickly and repeatedly show that infrastructure is as expected for a set of checks, also known as Operational Validation using Pester. There are many use cases for this type of solution; DR testing, installation, first line checks, presentation setups

What is Pester?

Pester is a Unit Testing framework for PowerShell which can be used for testing your code but also as shown in this session for validating your infrastructure. This is an excellent post by Adam Bertram to introduce Pester It is included with PowerShell on modern Operating Systems and free to download from the PowerShell Gallery or GitHub if not included

After this session, you will have a basic understanding of how Pester works and the capability to examine your checklists and create your own validation tests and provide some reporting for management.

Why I Want to Present This Session:

I am able to perform the following checks in 15 minutes across hundreds of servers and thousands of databases and I think it is cool and useful and everyone should be able to do so and take the functionality to create their own.

Every Job in Ola Hallengrens Maintenance Solution exists, is enabled, has a schedule, has succeeded, has 2 job steps, has a generate restore script job step, the root backup folder is contactable, for every database the correct folders exist for the full, differential and log backup depending on recovery model in that folder, that each of those backup folders has files in it, that the most recent file in each of those folders is less than the required frequency for those jobs

Think about how long that would have taken a junior DBA if I had tasked them with it, and how many mistakes would have been made in the checking

Additional Resources:

Here is an example report although only for two instances

Pester Report

Here is a Pester Test

Pester Report
Slides and code can be found here

Session Transcript:

Brent Ozar: Alright, so in this session at GroupBy, Rob Sewell, AKA the DBA with the beard is going to be talking about turning your SQL Server admin checklist into Pester tests; so take it away, Rob.

Rob Sewell: Hello, hello, nice to meet you all. My name’s Rob and as you can see, you can find me on Twitter on SQLDBAwithbeard, but my website is, and there’s a very good reason for that. And the reason is, that when I first changed my Twitter handle, Twitter’s only got a 15 character limit, so I became SQLDBAwithabear. I nearly kept it, and I thought, no. So SQLDBAwithbeard on Twitter. If you would like to find all of the slides and code that I use, it’s on my GitHub., and we have a GroupBy Green is Good, Red is Bad; or in fact, Green is Good, Red, and nothing else.

So, let’s find some slides and a clicker. I’m a guy who likes to run around and move around, so you’re not getting the full picture here just looking at me through your little screen, but everybody has got lists – my list for today, get up, drink appropriate liquid – that did used to say drink coffee until I was doing this presentation in Germany and a couple of people went “well I don’t drink coffee”, so we changed it to appropriate liquid, and then the rest of the stuff that we need to do. So, the point of this presentation is not to show you things that you should use. You should not take this code and put it onto your environment and make use of it. The idea is to give you some examples, to give you some ideas to make you think, to go with what is it you need for yourself and your own environments.

When you think about checklists, we all have checklists for a new server, for doing a migration, for incidents, software releases, all of these sorts of things. If we’re in an ITIL type environment, we’re going to have checklists for our change process, our change management process. And all of those are going to be very much set in stone by our environment, by our companies, by the processes and procedures that we need to follow as IT professionals. But this is more about the other, sort of, checklists that we’ve got.

So, I’d like you to go and ask your presenters, when they’re doing their presentations, did you write a Pester test? Did you make sure that everything was in place for your environment before you did your presentation? I’ll show you in a while, I have Pester tests that I’ve run before I’ve done this presentation, which hopefully means that everything is going to work as planned. Let’s pray to the demo-gods, but hopefully not as much. But if we’re doing demos for other companies, if we’re doing presentations at work or for clients or our morning checks, or for instance response, or maybe the times we say to first line support, before you call me out at three o’clock in the morning, I want you to check all of these things. Because at three o’clock in the morning, if you haven’t checked all those things, I’m going to be not very happy. I’m hopeful that all of you have got ideas in your head that you’re thinking about that will give you further things that you can make use of the code that we’ll go and have a look at in a while.

So, Pester, Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. It’s designed to do TDD for PowerShell code. So to unit test our PowerShell code and make sure that it does what we want so when we give the code to people it works as expected. However, it says for validating PowerShell commands. So some clever people went, oh hang on a minute, we don’t just have to use this for deducing unit code; we can use this to test results of any PowerShell that we use.

What does it look like? This is what it looks like. At the top, we have our describe block. That is our scope for those tests. We have a curly brace on the same line, and the reason we have a curly brace on the same line, and I mention it, is because otherwise, you get one of my most favorite PowerShell error messages, where it says, have you put the curly brace on the next line?

Next, we have a context. The context enables us to group a set of tests together. And again, it has its own scope, which may be important. And then we have our test. Our test uses the word it. It, name of the test, curly brace on the same line, and then the value of the thing I’m testing should be nothing. So I like to think of it as what I am should be what is expected. In the bottom half of the screen, you can see the results of running a test. We get some green saying, describing, this is our group of tests, our describe block. and then a context, with the name. If our test passes, it’s green. It also has a little plus sign and how long it took to run. And if it fails, it says it expected the string length to be 16 but it was nine. And then there’s a nice little arrow that’s going through and pointing up to where the difference is. And at the bottom, we have our total time and a summary of how many tests passed, failed, skipped, we’re still waiting on the answer and we don’t really know what the answer was.

So, as you can see from the slides, I am not somebody who likes to bore you with PowerPoint and bullet lists, however, I must give you my box of shame, because this slide does have a lot of text on it and I apologize, but I think you can see why. These are our should operators. So our test was, what I am, pipe, should, something and then what is expected. And this is the list of things that you can use, they’re called assertions. And I’m not going to describe any of this; you guys can read. And more importantly, because it’s PowerShell, you can see that in general be, be exactly or be greater than and be less than, you know exactly what they’re going to do. But what I’m going to point out to you, is contain. If you’re used to working with PowerShell, you quite often will use contains for getting items out of an array. It will not work in Pester. Pester uses contain for seeing if a file contains specific text.

So, the question is, Rob, what can you validate? And the answer can only be given – you’ll only get this if you know Top Gear, because it need to be given in a Jeremy Clarkson voice, and it goes “everything in the world”. Of course, it’s not really everything in the world, but everything in the world that you can test with PowerShell. So, in that example you can see there, that is nearly the SMO – our properties for a SQL Server, for the job server and for the logins, only on a SQL Server. So there’s all of those things that we can test straight away. And if you examine a SQL Server SMA object, there are probably thousands of different properties that you can check, and if you take PowerShell to look at any other service, windows service exchange, Linux, wherever you want to go, the numbers are only going to get bigger.

So, I don’t know about you, but I’m fed up of looking at slides; let’s have some demo. So I said I would how you my Pester test; here is my Pester test that I ran before I did my presentation, and what we’re doing is checking things like are my SQL Server instances up and running? Do I have the right code running, do I have PowerPoint running, do I have the right PowerPoint running? I shouldn’t have Mail open, I shouldn’t have Tweetem open, I shouldn’t have Prompt open. And all of these things, people have said to me before, you’re a little bit over the top about this, Rob. Maybe, but a lot of these things are things that I have experienced when I’ve come to do presentations. Doing a presentation about the SQL Server provider, but I’ve got Green is Good, Red is Bad PowerPoint open. I’ve got the wrong code open. So I haven’t written all of these in one big hit is the message, really. I’ve iterated through and built these up as we go. So let’s get ourselves down the bottom there, and we’ll have a look at demo number one.

Demo number one says – oh, I forgot one thing. When you’re working in Visual Studio code – this is the insider’s version – if you press F1, you get this magical box at the top allowing you to do things, and one of the things we can do is change our color theme. So I think, seeing as we’ve had a blackboard presentation theme, I think we’ll have a blackboard theme for our Visual Studio code, as long as the guys looking on the webcam say that we can read the code there. All good?

Bert Wagner: Yeah, looks good.

Rob Sewell: Excellent. So, at the top, it says, “Run, then talk, Rob.” So let’s run this and then talk. So, we’re looking at do things exist. So we can do test connection, just to pin does this server exist. And I do it quiet, because we don’t want any output from our PowerShell command, and I put error action, silently continue, which says, PowerShell, if something goes wrong, don’t tell me, just carry on. Because all I want to have are the results of my Pester tests and not a sea of blood from a PowerShell script that’s gone a bit screwy. We can check if our instance exists. So this is going to be dbatools, we’re going to have our module installed and this is going to test our SQL connection, are we getting a good connection? And we’re saying our connect success should be equal to true.

We can check our SQL error log. So our SQL error log should contain because remember, contain is for finding things inside files, SQL Server is now ready for client connections; a fairly goodish way of saying that SQL is about ready to go. Our error log shows CHECKDB finished without errors. We’re using some regex here, just to check, using a dot and a star. So we’re saying that our error log should contain CHECKDB for database and any other characters finish without errors. Normally, when I’m in front of a live audience, I’ll say, so this means that we have checked that all of our databases have successfully passed their CHECKDB, doesn’t it? And hopefully Drew is going to be shaking his head going no. because what this actually says is, the error log has one instance of CHECKDB for database, something, finish without errors. The important message is, don’t only understand when your test will pass, but understand what’s going to make them fail. So we need to know that if we want to have a CHECKDB for database check, we’re going to have to do a little bit more coding to make that work.

This is how we can use our PowerShell knowledge of contains within an array. So our server login’s name is an array of names that are on our server, and we’re saying it should contain the beard/Rob. And then we’re just saying that this should be true. The pipe should be true is not actually needed. I could just leave server logins name contains the beard/Rob, because Pester will assume that if the result is true, that is a pass and if the result is anything else, that is a fail.

So we can check other features. We can check our Hyper-V is installed. We can check if IIS is installed, we can check on a remote server if we’ve got SMB1, should be removed. Probably something a lot of people have been checking over the last few weeks with WannaCry. We could check our .NET versions, we could check which version of an operating system we’ve got. We can look at files. Again, we can just do test path, a normal generic PowerShell command, or we could use test SQL path from dbatools, which is checking that our SQL Server database engine account has access to a share. So for situations where our backup share is protected due to, you know, PCI or other security reasons, we still need to check that our database engine can actually access a share.

We could check how many files, we can check the versions of our files. We can check for creation dates or how old they are; or we could check our machine, do we have the right network adapters, the DNS servers, the correct gateway. Or our programs, should we have these programs or those programs, what versions should they be? These are all things we can check, not necessarily a must, but they’re things that you can do. And as you can see, the server that doesn’t exist returned a false; and the logins to not contain the beard/Rob on that particular instance. That’s just to show you what failed tests look like. You’ll also notice that I don’t have any summary in these results because I just ran the code. What I’d like to say is Pester is just PowerShell. So we can just highlight the code and run it in PowerShell, in VS code, in the ISE, any other IDE that you choose to use.

Number two, it’s all very well and good having tests that are going to run the same each time, but how about if you’re in a situation where you’re going to go into different environments and maybe you want slightly different versions? So if you’re reading that code, you can see that I can choose to put a list of servers into here. I can choose which SQL admin accounts I’m expecting, I can choose my backup directories, I can choose how often I’m running my Ola Hallengren backup jobs. I can choose if I have my sp_Blitz, sp_BlitzCache and the rest of those brilliant tools – this is a slightly older version. And this is how we can do it, we create ourselves a Palms hash table with name and value pairs, and then down at the bottom, we say test SQL default and give it these parameters. And or test SQL default file looks like this. as you can see, on the right there is a lot of code in here, but it basically comes down to this; we create ourselves a return object – let’s find where we create ourselves the return object, Rob. We create ourselves a return object for each server, we then log onto the server and run a script block to get all of our results back and place them into the return object like this. We say, in our return object create a backup directory, and it makes a value equal to the server backup directory. And once we’ve done that for all of those things, we can simply come down into our test, and we see that basically, the return temp files should be the variable temp files. Our Ola Hallengren system full frequency should be this variable that we’ve defined here. And we can put our variables into our test name as well, and that enables us to pass in these parameters like this. So if I go into a different environment, I can just change these values here to the ones that are correct for this particular environment and their RPO, RTO or whatever it is that they’ve got, and then I can just run my code. But it would work much better if I highlight all of my parameters as well.

So now, we’re going to just test these three SQL Servers and we’re going to check them for these default, which is what I have in my data center, i.e. my laptop here. And we’re just going to make sure that everything is running as you’d expect it to be. And as you can see, we’re going through checking our sp_Blitz, checking we’ve got certain agent jobs, have we got services, are all of our default alerts set up as we expected? Green is good, red is bad. I didn’t see any red, so I’m happy.

Let’s have a look at number three. So for test number three, we’re now going to look at how we can invoke Pester. And I use that word because this is the command that we use, invoke Pester. And what invoke Pester will do is run any files that have a name like this one; .tests.ps1, within a folder. It will just run all of them. If we look in this particular file, we can see that in our describe block we’ve got a few things added. So we’ve got some tags. So for testing our access to our backup share, we’ve got a tag of server and backup. For testing our collation, we’ve got a tag of server and collation. For testing our last known good DBCC CHECKDB, we’ve got a database and a DBCC, because we’re checking every database.

If we look here, we’re creating some test cases. Test cases allow us to loop through objects and run the same test again and again and again against different things. So we create ourselves our test case array of our SQL Servers, and then we access it by adding a parameter into our it block, the test cases parameter after the title; and then we can say test SQL path for every SQL Server, it should be this path and it should be true. However, that doesn’t always work. True died in the wool Pester people do not accept that this is a way of doing things. This is not how they like to see their unit tests done. But this is not a unit test, this is an environment of validation. And in this scenario, it’s easier for me to use a for each loop, because I need to for each through my SQL Servers and then I need to for each through my tests; and I couldn’t make the test cases work with two loops. But it doesn’t matter, you don’t have to use test cases, you can just use your for each loops.

So if we make sure that we change our prompt, we can then run invoke Pester, tag backup. And all that’s going to do is run one of the tests that are in this file here. So we’ve got a number of tests in this file, a number of describe blocks, but only one of them has the tag of server, and that’s the only one that was run. If we just wanted to do the collation, we’re just going to check that all of the databases on our instance have a collation that matches the server collation, or a DBCC CHECKDB.

I don’t know – I do know how to do this in tSQL. I don’t think you can do this one line of code any quicker than that. We’ve just checked 84 times whether a database has had a successful CHECKDB, whether it was run within the last 14 days and whether it has data purity enabled. And that’s, again – I’m going to say dbatools again because if you’re using SQL Server or you’re using PowerShell, dbatools is, without doubt, the module you need. And it has this wonderful command called get DBA last good CHECKDB, which enables us to do, in one line of code, just like that, have all our databases had a successful CHECKDB. We could check our server tag; that’s going to check the backup and the collation because they were two tags, two tests. Or we could check all of them in one go. So it’s another way of enabling you to filter which tests you need to run in which environments. Maybe you would tag them as development test and production. If you were having an environment where you were having to test different things for different environments, you could write different tests, put them all in the same test script and access them just using the tag parameter.

That took 14.41 seconds to run our 89 tests, but the information, if you can imagine we’re doing 15,000 Pester tests and we had one failure, it would scroll up. You’d be looking up going where did it go, where did it go? Come back, come back. So we have a show parameter with Pester, and what show allows us to do is to restrict the amount of information that we display to the screen. So if instead of running the entire suite of tests again, but with the fails, that’s with an S, all we’re getting is our describe block, it would have our context block if we had any, and any failures. And also, you see, that test ran only in 7.6 seconds. Or we could have just the summary, and when we run the summary, nothing’s happening; test is just – something’s going on. Normally runs really much quicker than this. All you get at the end is just the summary and it runs quicker; normally when you’re just showing summary, significantly quicker.

However, just because all we have returned to the screen is the summary, does not mean that that’s all of the information that we can get out of our invoke Pester. So when we run invoke Pester with a show summary and this pass through parameter, we can assign it to a variable. When we do that, we’re still going to see the same information as we saw for the last test. But actually, what we have stored here in our variable is something better. We have our entire test results stored in a variable; so we can see how many tests we ran, how many passed, how many failed. And then we have our test results here.

So now, we can start to examine our object. If you’re new to PowerShell, get-member is one of the commands you need to know. It enables you to examine an object and see all of the properties and events that are available to you. So we could see, these are all of the things we have available. We have the test results. Let’s have a look at our first five test results. When we look at that, we can see that it’s the same sort of information that we had displayed onscreen. We have an error message, expected false but was true, the parameterized name and then the actual name of the test as well; and how long each particular test started to look.  So, we could just return how many tests passed, just down there at the bottom or how many failed, just the one, or we could format our information a little differently and say, well just give me the same of the test and whether it passed or not, and if there is a failure message, give me the failure message as well. I don’t want all of the rest of the information, that’s all that I want.

So we’re moving on, we’re developing what we can do with our Pester. Oh, shall we do demo number four before we do demo number five? Probably better. So, one of the things we can do, instead of hard coding all of our parameters into our test SQL defaults and doing things in that way, maybe we want to be even more dynamic in the way that we work with our tests. So we can use the JSON file like this. and within the JSON file, in this example, we’re saying for the backup share test, this is the backup share we want, I’d like you to search for the Rob XPS server and don’t skip. Skip is another way of working with the tests that you have within Pester. So it enables you to just, maybe if you’ve written the test but you haven’t written the code for it yet. Maybe if your environment works for – you need to test this particular thing in your production environment but it doesn’t exist in your development environment. Maybe when you’re running it in dev, you might want to be able to skip that test.

So, this is a way of enabling us to do that. We can get our SQL Server names in a number of different ways. I keep trying to work out a way of writing this JSON to enable it to be usable for everybody, but as we were talking about in the break, sometimes it’s difficult to fit everybody’s experience because people have different ways of doing things. So I’ll say, these are a number of different ways you can grab your SQL Servers using PowerShell. You can get them from your Hyper-V server. So in my particular environment here at home, I use this Hyper-V parameter from within my JSON files. You could use your reg to servers via dbatools, the get SQL reg to server name, and you could just pick out which group of servers you want to use. You could just use the SQL provider with PowerShell and the SQL Server module, which is available on the PowerShell Gallery now, and just get them straight from your central management server; either from your remote central management server or from your local registered servers. Both would be available there. But whichever way you find to do it, you could just put them in as an array. Grab them out of a text file, there are a number of millions of different ways of grabbing your SQL Servers. Once we’ve got our SQL Servers into this variable – so run that bit of code, Rob – then we need to grab our configuration. And this is how we get our configuration out of our JSON.

We need to add this little join here to turn this into JSON that PowerShell will understand. We run that, we don’t get an output, but in our config variable we have all of the JSON, displayed slightly differently, but it’s still just a JSON.

Now what we can do is, within this dbatools-scripts is a repository that I’ve got on my GitHub which is just a number of Pester tests which make use of the dbatools module and enable you to test different things in different ways. And some of them enable you to test at a server level or a database level, or even more detailed, maybe at a column level or something for identity. And we can run these tests just using the configuration that we’ve got in our JSON file. So we’re testing our collations, but instead of testing it just for the server, like we did previously, now we’re checking that every single database on those three instances is correct. One of my favorite ones, the identity column, we’re checking every single database on those instances. We’re checking every column in those databases and we’re seeing if they are an identity column, and are they less than 90% full? Because we don’t want to be the guys that end up with an identity column that fills up and everything stops and all the white shirts come and start shouting, “can you fix it, can you fix it, can you fix it? I need it to work.”

Keep going – I can’t remember what the next tests are, otherwise, I’d be telling you what’s coming up. But all of this configuration is coming straight out of our JSON. So if I want to change it, I just go and alter that JSON file, maybe a create a JSON file for different environments, and use that and run them against the same set of scripts that just goes and picks them up; so our last good DBCC CHECKDB. But instead of checking each database individually, we’ll check all of the databases on the instance so we can filter and compress the amount of information that we’re getting. And there’s an example of a skip.

So, I’m testing the tempdb configuration using the dbatools command and I don’t want to get a failure for having my tempdb files on my C drive because I only have a C drive. So I can just set to skip within my configuration JSON, I run my test, and I will just get a yellow skipped, and here a yellow skipped.

So what we can do is take our test result’s variable and we can convert that to the JSON and stick it into a file. I’m not going to run that because I want to show you the one with more failures in than this has got, but we’re going to use that in a minute. It’s just a way of saying the test’s variable we’ve got is just held in memory within our PowerShell. But if you want to convert it into JSON, XML, HTML, whatever you want, you can do that and then save it into a file.

So we’re doing well for demo number five. This is where I first saw the beauty of Pester in a real valued way. Because what I had to do is I had to set up Ola Halengren’s maintenance solution in an estate and I had to prove, to get paid, that all of these things were correct. So for Ola Hallengren, I needed to check – I really should check that the SQL agent is running on each of these instances, otherwise none of my jobs are going to run. I need to check that it’s set to auto start, because then when the box is bounced or, for whatever reason, it’s going to come back up. I need to make sure I’ve got the following jobs: the command cleanup, all of the database backups, full, diffs, logs, the integrity checks, the index optimizer, the delete backup history, the system policy, purge history, every single one of those jobs should exist. Should have run successfully, because I must have tested it before I handed the estate over, and it must be scheduled so that we know it’s going to run regularly. And I need it to check the files, because it’s all very good saying I’ve got these backup jobs and they’re all running, but I want to make sure that these files actually exist.

So there needs to be a backup share for every folder on the server, and there needs to be a folder for every database inside that; except where we’ve got availability groups, because they have their own folder which need to have a database folder in them. Inside each of those database folders, we need to have a full, a diff, and a log folder. Except for system databases and databases that are in simple recovery and the ones that are log shipped, because they only need a full and a diff folder. And then every full folder should have files in it and the newest file should be less than seven days old because that was the RPO that I’m making up here. And every diff folder should have files in it and the newest one should be less than 24 hours old. And every log folder should have files in it and they should be less than 30 minutes old.

So what I could have done is I could have said to my junior DBA Drew, Drew, could you go through and check these things off and write them down on a piece of paper, please? Tick, tick, tick, tick, tick. Could have done, tick, tick, tick… On 100 instances with 2000 odd databases, it’s going to take him a couple of weeks. But would you trust him to get every single thing right?

Bert Wagner: No.

Rob Sewell: No, because we’re human, you know. We’re human, we make mistakes. Can you imagine going and looking at all those folders or files and making sure – you’re going to make a mistake. So I wrote a PowerShell script, and the PowerShell script I wrote, I’ve blogged about it, it’s on my GitHub and it enables me to do this. and when I ran this command, it ran 15,500 tests in slightly under 15 minutes, partly because the storage this backup was sat on was particularly slow. As you can see, you can see some red going through there because my log backups have not been run. But we have just checked – let’s do it that way – we’re checking, for each database, we’re now checking the files on our file system. We’re checking that all of our backups exist, that they’re enabled, that they’re scheduled. We’re checking that all of our other maintenance jobs exist and are enabled and scheduled and succeeded. And we’re checking all of our databases, checking that we’ve got the right files in the folder structure that Ola creates for us when we run the maintenance solution.

And we ran 273 tests there in just over a minute. If I wasn’t running GoToWebinar or anything, it generally runs in about 20 seconds. So that’s brilliant. We’ve got our results, we’ve put them into a variable, we can convert them into JSON like this. You can convert them into HTML. There’s a module called Format Pester, which will enable you to turn the results of your Pester tests into an HTML page. I’m not going to show it because, to be honest, I like some of these other ones a bit better. What I like to do is, run this code before I talk because otherwise, I have to sit and watch it, is go back to what we did before, where we took our parameters for our Pester test and run a set of tests. It’s just going to be exactly the same tests as we’ve got before, and we’re also going to output them, using this output format parameter, into Nunit XML. So, people who are used to CI, continuous integration type things, know that you can now make use of that. You can consume that into your continuous integration system and make it part of the procedure.

But what we’re going to do, we’re going to output this into an XML file and we’re going to use this little tool called report unit EXE, which you can download from there or you can take this code and it will do it automatically. And it will expand it and leave it ready. And then what it’s going to do is run through, in my temporary folder, grab all of the XML files and turn them into an HTML page that looks like that. With the old report unit and the old version of Pester, it came up looking a lot better. But we can see, Rob XPS Dave, 106 tests, 102 passed, four failed, got a little quick summary over here.

Somebody at some point’s going to go, hang on a minute, 106, 126, 150, total tests 1528? Something’s not right. I think it’s something that inside the report unit viewer EXE, which I haven’t bothered to explore. This used to be a beautiful way of turning ourselves into some nice pretty things that management can use to filter by failed and pull up or message and everything looked wonderful, but really it’s not so truthful now. We can’t trust it, I don’t trust things that add up like that. So I created some Power BI. I didn’t use the XML, because I found it hard to pass that inside Power BI. And here’s another thing to say, I’m a DBA. I’m an automation guy, I’m a trainer, I do some continuous integration. I do not do BI. I deliberately choose things that look horrible so that the BI people out there, like you Bert, shaking your head going no, you don’t do it like that, you can display information much better, you shouldn’t use those colors. I know those things, I deliberately do it like this because it makes people like you think, I could do that better. And I go, yes.

I’ll do the Pester, you make it look pretty. But we’ve taken – all of this is based off of the JSON file that we created as a result of our Pester test, and it means that we can do things like this. we can click on things and they’ll change, we can – oh no, we’re on filtered aren’t we, let’s go to detailed. Let’s try the detailed one, have a look. When it comes in, there we go, we’ve got little buckets that fill up, that’s excellent. Little buckets that fill up with – 98.18%, no, 92% succeeded and 8% failed. And we can see which jobs we had running, so we can filter them there. We’ve got our failure messages coming out. Perhaps we want to know how long our tests took. So we do it like this – oh look at that, it looks horrible. Yes, it does, but it works. We can see that our new backup jobs took 1.8 seconds. Or we could filter it in a different way; don’t filter it by the same one, that would be daft. Filtering all of our file checks took nine seconds. Or perhaps we just want to look at our 2016 instance, that took nine seconds.

All of these different ways we can filter stuff around in Power BI. And the last one – oh yes, the last one looks pretty much just like that. Green is good, red is bad, except if you’re a green red color blind person, in which case, they’re both sort of grayish and you can’t really tell.

Bert Wagner: Or a dog.

Rob Sewell: Or a dog, absolutely. I mention that because if you look at the results of the Pester tests, even though it’s green and it’s red, the format of them means that colorblind people can still see which ones have passed and which ones have failed; so I’ve been told. I can see green and red so I can’t say for absolute certain.

So, in a summary, everybody’s got checklists, you’ve got them up in your head, you’re thinking about the ones you’ve got now. Hopefully, you’re making notes, because manual testing is error-prone. Drew cannot do 15,000 tests, even in two weeks, and get them all right, unfortunately. Pester is a unit testing framework for PowerShell, for writing your PowerShell code. If you’re going to get deep into writing good PowerShell code, start looking at and understanding Pester. But, it’s just PowerShell. You can use it to validate your infrastructure. Basic syntax, describe, context and hit code blocks. Don’t forget to put your curly brace on the same line. I bet you all go and put your curly brace on the next line, just so you can look at the message and see that it really does say what I said. The should command and all of the horrible slide that was full of text is used for testing our assertions. Understanding our greater than, our less than, our should and should nots. using Pester, we can output our information as Nunit XML and consume it in any sort of system. We can use the report viewer .EXE, but be aware that some of the numbers might not add up quite as you expect. But if you export it to JSON, you can consume it with Power BI and make pretty things that change color and shape; and everybody knows that management like things that change and move around.

But I’m going to tell you a story. We talked about how our Pester tests could be used to make sure that service tester didn’t call us at three o’clock in the morning without checking that everything had worked correctly. I know of an organization where when the system goes wrong, it automatically creates a ticket in the ticket system. And what they used to then do was go, oh three o’clock in the morning, system’s gone wrong; we need to call Drew. Drew, fix it, it’s a database problem. It’s bound to be, it’s always the database… It’s never the database. And when we started being on call, we’d have to understand what this process was, and at three o’clock in the morning, nobody’s really at their best. So you then go, oh no, where was that documentation? Log on with my VPN and remember that password and get to there, find the share for where that stuff’s saved and go through it and then go back and do all these tests and then go through and find it’s not the database’s problem and pass it to somebody else.

Having watched an earlier version of this, he said, perhaps we could do that better. So now, when the ticketing system for that system, PowerShell, the account holder in the ticketing system goes, I’ll grab that thank you very much, I’ll assign it to myself. And then it runs the Pester tests. All the Pester tests are all the things that the on-call person would normally do. And once it’s done those, it analyzes them, and for some of them, it might be able to go, hang on, that’s the server that needs restarting, I can do that. Run the Pester test again, everything’s green, hand the ticket back to the service desk, I think you can close this, everything’s fixed. But if not, they could go, that’s a network problem I think. By my Pester tests, that looks like a network problem. I’ll assign it to the network on-call person. Here you go network on-call person, here is all of the things that I have done beforehand and the results of them, and I think it’s your problem. So when you get called out at three o’clock in the morning, you look at the ticket, the ticket has all of these things, now you can really start working on what’s going on.

So here’s what I say to people: do you want to be the person who has PowerShell as their manager, telling them what to do or do you want to be the person who writes the PowerShell that tells other people what to do? I know which one I want to be. Any questions?

Bert Wagner: that’s really good stuff, Rob. Any time I see automation, it’s a beautiful thing. We did have some questions come in during your talk. Going for this back, SQlGene asked about, “Can you elaborate about how Pester uses behavior driven design and how that compares to test driven design?”

Rob Sewell: Thanks, Gene. I can’t speak competently enough on that to be able to take that forward. Drew, do you know?

Drew Furgieile: No, you know, when I think of TDD, it’s tough because back when I was a developer, that was, kind of, before I really knew a lot about TDD or behavior versus unit testing and all that stuff. It’s a tough one to elaborate on. It’s one of those things where if you Google it, you might get four different results on the subject and they’ll all be different. It becomes a philosophical discussion at that point.

Rob Sewell: Yeah, I must say, I’m much newer to the TDD side of PowerShell and Pester than I am to the environment validation side. I’ve done a lot more client work with validating that things are as they should be, and most of my TDD has been within dbatools, trying to back port Pester unit tests and integration tests into existing code, which is never the easiest way of doing things, you really should do it the other way around.

Drew Furgiuele: Right, and when I occasionally do write code in my job today, it’s all TDD, you know. Resharper and all that stuff just helps tremendously with .NET code and all that stuff.

Rob Sewell: So now that you’ve looked at the funny picture, and hopefully you’ve all looked at the funny picture, and that’s not me… Further reading…

Bert Wagner: I was just going to ask you that, because one of the questions that came in was, “Where can people just learn more about Pester in general?”

Rob Sewell: Right, without a doubt, if you want to learn more about Pester, get the Pester book by Adam Bertrand. It’s an iteratively developed Pester ebook, so every few whatevers, he releases a new version with additions added to it, and it is very comprehensive and very, very good. If you’d like to see some more integration type tests, Andre and I did a session at the PowerShell conference where we used AppVeyor to spin up a machine and then run some tests against it, which hopefully could give you some ideas of how you could take that bit further. And if you’d like to know about an introduction to TDD with Pester, come to Dublin, because that’s the session I’m doing in Dublin, and I’ve submitted it elsewhere as well. I use the cognitive services faces API to check speaker faces for beards and show you how to build a function out of that with TDD.

Bert Wagner: I have to say, I think that was the smoothest transition I’ve seen all day. Straight from where can you learn more to that slide there. Another question that had come in was, “Have you thought about autocorrecting some of the errors that Pester returns?”

Rob Sewell: You cannot do that within your Pester test. So you couldn’t have a test that failed and then doo an autocorrect. You can analyze the results within your test results variable and go oh, that one failed, that’s a service, I can restart the service.

Drew Furguiele: You know, it’s one of those things where – I love automation, believe me, I love writing PowerShell and I love automation, but some of those things I don’t know if you’d want to correct right away. I think just correcting it doesn’t tell you why it happened.

Rob Sewell: It’s a tough one, and that’s why I think it’s better that you analyze. So you go and have a look, what is that test about? That test is saying that the service has not started. Perhaps we could try restarting the service a couple of times to see if that works. But that test is saying that the network latency has improved; well there’s nothing we can really do about that. That’s not something we can change. And if it’s, perhaps, a SQL Server property or something that we’re checking, maybe we don’t want to be changing that in the middle of the day just when this test is run.

Brent Ozar: My favorite was, we had a maintenance window, we were trying to stop a service and go do some patching and some yoyo had written a script to continuously if he found a stopped SQL service, go start it. [crosstalk] Right, it’s one of those things that seems like such a good idea when you code it, then whoopsy. Alright, well nice job, Rob, thank you very much for volunteering.

The following two tabs change content below.
Rob is a SQL Server DBA with a passion for Powershell, Azure, Automation, and SQL (PaaS geddit?). He is an officer for the PASS PowerShell Virtual Chapter and has spoken at and volunteered at many events. He is a member of the committee that organises SQL Saturday Exeter and also European PowerShell Conference. He is a proud supporter of the SQL and Powershell communities. He relishes sharing and learning and can be found doing both via Twitter, Slack and his blog. He spends most of his time looking at a screen and loves to solve problems. He knows that looking at a screen so much is bad for him because his wife tells him so. Thus, you can find him on the cricket field in the summer and flying a drone in the winter. He has a fabulous beard
, ,
Previous Post
SQL Server & Containers
Next Post
Successful production deployments with columnstore index in SQL Server 2016

1 Comment. Leave new

This sounds like a great session! I have been looking for a solution just like this for double checking job parameters and schedules across many instances.
I think ‘Technicians who use checklists’ could be made a bit more engaging. Maybe something like ‘Anyone who as ever second guessed themselves and ended up checking multiple servers one by one on a Friday night’


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.