Pester is my Co-Pilot

When I was a senior in high school, I got my pilot’s license. One of the things I remember about my flight training was the preflight checklist. Before we started up the engine, we did a walk-around of the aircraft, checked the landing gear, ailerons, rudder, and oil. Before take-off, were the flaps up, doors closed and latched, seats in a locked position, etc.? These checks help ensure our safety, passengers’, and folks’ safety on the ground.

How do we ensure the infrastructure we deploy meets the expected guidelines and standards? Is there a way to have a “preflight checklist“ when deploying infrastructure? Several of you might be rolling your eyes and thinking, “Well yes, it’s called Infrastructure as Code.” While that is true, I have worked in shops where the DBA team consistently had to remediate issues with deployed infrastructure released to them.

In my current position, working with customers and installing SolarWinds® SQL Sentry, I sometimes hit various roadblocks when trying to get the solution up and running.

So, to stop banging my head against a wall, I decided to use Pester, a testing framework for PowerShell, to test my customers infrastructure to determine possible hiccups before installing SQL Sentry. Pester would execute my “preflight checklist.”

https://pester.dev/

Pester uses several functions, listed below.

DESCRIBE

Defines a group of tests

Tags allow you to run a test for a specific DESCRIBE block when a test has multiple DESCRIBE blocks

For Each allows you to loop through an array to run the DESCRIBE against multiple items

CONTEXT

A way to group IT blocks inside a DESCRIBE block

Validate a test inside a DESCRIBE block

SHOULD

Defines how you want to evaluate results from your test

MOCK

Allows you to define a script block to test your code against

In my Pester example testing my infrastructure, I will not be using the MOCK function

Here are the requirements for my test. For context I will be using dbatools and the Active Directory modules:

Test for Monitoring Service Server

Monitoring Service Account is enabled and not locked out

WINRM Port Check

WINRM is running on Monitoring Service Box

Test for Target Servers

WINRM Port Check

WINRM Service is running

SQL Port Check

WMI Port Check

SMB Port Check

Dynamic TCP Port Check

WMI Connectivity and Latency from Monitoring Server to Target Server is less than two seconds

Checking that Monitoring Service has elevated permissions on Target Server

I have a set of parameters and environment variables defined at the top of my test. I also import the dbatools and Active Directory modules.

param($MonitoringServiceServerName = '<ServerName>'
$sql_port = 1433,
$MonitoringServiceAccount = 'accountname',
$MonitoringServiceADGroup = 'ADGRoup name if Monitoring Service is not explicitly defined on the targetrs' )

$WarningPreference = 'SilentlyContinue'
$ErrorActionPreference = 'SilentlyContinue'
$ProgressPreference = 'SilentlyContinue'

Import-Module ActiveDirectory
Import-Module DBATools

$Servers = Get-Content 'C:\<path to text file of servernames>',

Here is an example of a DESCRIBE block, where you see my group of tests defined in each IT block. I am using the -BeTrue parameter to validate what is in the variable preceding the SHOULD operator.

The -Because flag allows you to send a response back to the console when a test fails. Also notice the -Tags parameter, when running the test, I can specify which DESCRIBE block to run by using this parameter.

Describe "--> Preflight Checks for Monitoring Service Box $($MonitoringServiceServerName)" -Tags 'MonitoringServiceServer' {

        IT "Checking if Monitoring Service Account is Enabled and not LockedOut"{
            $ADUser = Get-ADUser $MonitoringServiceAccount.Split('\')[1] -Properties * | ?{$_.LockedOut -eq $false -and $_.Enabled -eq $true}
            $ADUser | Should -BeTrue -Because "Monitoring Service Account should be enabled in Active Directory and verified account is not lockedout"
        }

        IT "WINRM Port Check" {
            $winrm_result = Test-NetConnection -ComputerName $MonitoringServiceServerName -InformationLevel Quiet -CommonTCPPort WINRM
            $winrm_result | Should -BeTrue
        }

        IT "Checking if WinRM is running on Monitoring Service Box"{
            $WinRM = Test-WSMan -ComputerName $MonitoringServiceServerName
            $WinRM | should -BeTrue -Because "This test requires WinRm to complete successfully"
        }
} ##Describe

The second DESCRIBE block uses a different TAG and the -ForEach parameter iterates over the $Server variable passing each element of the array to the $_ (system variable) in each IT block.

Describe "--> Preflight Checks for Target Server " -Tags 'TargetServer' -ForEach $Servers

        IT "WINRM Port Check on $($_)" {
            $winrm_result = Test-NetConnection -ComputerName $_ -InformationLevel Quiet -CommonTCPPort WINRM
            $winrm_result | Should -BeTrue
        }

        IT "WinRM Service is running on $($_)"{
            $WinRM = Test-WSMan -ComputerName $_
            $WinRM | should -BeTrue -Because "This test requires WinRm to complete successfully"
        }

        IT "SQL Port Check $sql_port on $($_)" {
            $port_sql = Test-NetConnection -ComputerName $_ -InformationLevel Quiet -Port $sql_port
            $port_sql | Should -BeTrue -Because "SQL Port Must be Unblocked"
        }

        IT "WMI Port Check on $($_)" {
            $port_135 = Test-NetConnection -ComputerName $_ -InformationLevel Quiet -Port 135
            $port_135 | Should -BeTrue -Because "SQL Sentry uses WMI and RPC to collect various metrics"
        }

        IT "SMB Port Check" {
            $port_445 = Test-NetConnection -ComputerName $_ -InformationLevel Quiet -Port 445
            $port_445 | Should -BeTrue -Because "SQL Sentry uses SMB to collect various metrics"
        }

        IT "Dynamic TCP Port Check on $($_)" {
            $SesionID = New-CimSession -ComputerName $_
            $DynamicTCP = Get-NetTCPSetting -Setting Internet -CimSession $SesionID | select dynamicportrangestartport, dynamicportrangenumberofports
            get-cimsession -ComputerName $_ | Remove-CimSession
            $DynamicTCP.DynamicPortRangeStartPort | Should -Be 49152 -Because "SQL Sentry uses Dynamic ports to collect various metrics"
            $DynamicTCP.DynamicPortRangeNumberOfPorts | Should -Be 16384 -Because "SQL Sentry uses Dynamic ports in this range of ports to collect various metrics"
        }

{

Here are a few more IT blocks defined in my test using a different evaluator than -BeTrue.

IT "WMI Connectivity and Latency From $($env:COMPUTERNAME)--> to Target--> $($_)"
            $stopwatch = [system.diagnostics.stopwatch]::StartNew()
            $Drives = Get-WmiObject win32_volume -ComputerName $_ | Select Name
                foreach($Drive in $Drives)
                    {
                    Get-WmiObject win32_volume -ComputerName $_ | ?{$_.name -eq $Drive.Name} | Out-Null
                    }
            $stopwatch.Stop()
            $stopwatch.ElapsedMilliseconds | Should -BeLessOrEqual 2000

        }

        IT "Checking that Monitoring Service Account... $($MonitoringServiceAccount) ...is Local Admin on Target $($_)"{
        $localAdminCheck = Invoke-Command -ComputerName "EC2AMAZ-Q1QSU12.cse.lab.com" -Scriptblock {Get-LocalGroupMember -Group Administrators -Member $MonitoringServiceAccount} -ErrorAction SilentlyContinue
                if(!$localAdminCheck)
                    {$localAdminCheck = invoke-command -ComputerName $($_) -Scriptblock{ $Admins = net localgroup administrators
                                                                                                                 $Admins | ?{$_ -cin ($Using:MonitoringServiceAccount, $Using:MonitoringServiceADGroup)}}}
            $localAdminCheck | Should -BeIn ($MonitoringServiceAccount,$MonitoringServiceADGroup) -Because "Please check that your monitoring Service account is Local Admin on the Target $($_)"

        }

        IT "Checking that Monitoring Service Account... $($MonitoringServiceAccount) ...is SysAdmin in SQL Server on Target $($_)"{
            $SysAdmin = Get-DbaServerRoleMember -SqlInstance $_ -ServerRole SysAdmin | ?{$_.name -eq $MonitoringServiceAccount -or $_.name -eq $MonitoringServiceADGroup}
            $sysAdmin.name | Should -BeIn ($MonitoringServiceAccount,$MonitoringServiceADGroup)
        }

Now that I have my test completed, how do I execute the test? From a PowerShell session you would Invoke-Pester. Pester follows this naming convention of *.Tests.ps1. (Pester 5 has other options beyond the scope of this article)

Invoke-Pester -Path C:\PreFlightChecks.Tests.ps1 -Show all

After executing my test, all tests completed successfully. Here is what you should see:

Inevitably, a test will fail and here is the expected output:

The test provides data to explain why it failed. The test expected a value of 16386 but got 16384.

Conclusion:

I want to work smarter and not harder. Implementing best practices for infrastructure, plain and simple, can make life much easier. Troubleshooting performance when the proper foundational concepts are not in place makes for a long day. Let me know if you find this set of preflight checks useful.