Be Mindful of Object Types when Writing Unit Tests and Performing Operational Validation in PowerShell with Pester

I recently wrote a Pester test that performs some basic operational validation (smoke tests) of SQL Servers. I've previously written similar tests as functions as shown in my Write Dynamic Unit Tests for your PowerShell Code with Pester blog article, but I decided to write this one as a script with the naming convention that seems to be recommended. The name of this particular test is Validate-MrSQLServer.Tests.ps1. You're probably thinking Validate isn't an approved verb and you're right, but this isn't a function, it's a script.

Just because I decided to write a script instead of a function doesn't mean that I can't take advantage of features such as a Requires statement, parameter input along with parameter validation, and error handling. It just needs to be written as an advanced script like an advanced function that you're probably already familiar with. Pester tests are simply written with PowerShell code, so why not take advantage of any language feature that you want?

 1#Requires -Version 3.0 -Modules MrToolKit
 3param (
 4    [ValidateNotNullOrEmpty()]
 5    [string[]]$ComputerName = $env:COMPUTERNAME,
 7    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
10foreach ($Computer in $ComputerName) {
12    Describe "Validation of a SQL Server: $Computer" {
14        try {
15            $Session = New-PSSession -ComputerName $Computer -Credential $Credential -ErrorAction Stop
16        }
17        catch {
18            Write-Warning -Message "Unable to establish a connection to: '$Computer'."
19        }
21        It 'The SQL Server service should be running' {
22            (Invoke-Command -Session $Session {Get-Service -Name MSSQLSERVER}).status |
23            Should be 'Running'
24        }
26        It 'The SQL Server agent service should be running' {
27            (Invoke-Command -Session $Session {Get-Service -Name SQLSERVERAGENT}).status  |
28            Should be 'Running'
29        }
31        It 'The SQL Server service should be listening on port 1433' {
32            (Test-Port -Computer $Computer -Port 1433).Open |
33            Should be $true
34        }
36        It 'Should be able to query information from the SQL Server' {(
37            Invoke-Command -Session $Session {
38                if (Get-PSSnapin -Name SqlServerCmdletSnapin* -Registered -ErrorAction SilentlyContinue) {
39                    Add-PSSnapin -Name SqlServerCmdletSnapin*
40                }
41                elseif (Get-Module -Name SQLPS -ListAvailable){
42                    Import-Module -Name SQLPS -DisableNameChecking -Function Invoke-Sqlcmd
43                }
44                else {
45                    Throw 'SQL PowerShell Snapin or Module not found'
46                }
47                Invoke-SqlCmd -Database Master -Query "select name from sys.databases where name = 'master'"
48            }
49        ).name |
50            Should be 'master'
51        }
53        Remove-PSSession -Session $Session
55    }

Woohoo! Everything works great!

1.\Validate-MrSQLServer.Tests.ps1 -ComputerName SQL01, SQL02


Not so fast. The first rule of writing unit tests was violated. Write the tests first and make sure they fail before writing any code (functions, scripts, etc). You might ask, how to do that for operational validation since you're not testing any code? The obvious answer would be to write the tests before building the environment but that's not possible when the environment already exists. In that scenario, the answer is to run the tests in a simulated environment where the servers and/or services don't exist.

In this example, I'll simply use the name of a SQL Server that doesn't exist to simulate writing the operational tests before the environment is created:

1.\Validate-MrSQLServer.Tests.ps1 -ComputerName DoesNotExist


"Houston, we have a problem." The problem is that one of the tests passes successfully when it should fail since it's being run against a machine that doesn't exist. In other words we have a false positive. Oh, and by the way, make sure the machine you're testing against really doesn't exist.

The funny thing is that based on the way the code is written, everything looks like it should work. So what's the problem? The problem is an assumption was made for the type of objects that Test-Port returns. The Open property returns a string and it's being compared to a Boolean.

Always double check the type of output you're receiving instead of making assumptions:

1Test-Port -computer SQL01 -port 1433 | Get-Member


To resolve this problem, the test can be changed to compare the output of Test-Port with the string 'True' instead of the Boolean $true.

1It 'The SQL Server service should be listening on port 1433' {
2    (Test-Port -Computer $Computer -Port 1433).Open |
3    Should be 'True'

This change is no longer necessary since the creator (PowerShell MVP Boe Prox) of the Test-Port function that I'm using has updated it so the Open property now produces a Boolean instead of a string. The Test-Port function can be downloaded from Boe's PowerShell Scripts Repository on GitHub. I've added it to my MrToolkit module which can be downloaded from my PowerShell repository on GitHub.

The moral of the story here is don't assume anything and make sure you're comparing apples to apples and not strings to Booleans or vice versa.

The Validate-MrSQLServer.Tests.ps1 test shown in this blog article can be downloaded from my Operational Validation repository on GitHub.