Pester Your SQL Servers!

In March I did my first presentation at SQL Saturday. I’d say it went very well and I was very encouraged with the attendee feedback. My presentation was on infrastructure testing with the Pester Powershell module. I didn’t go into depth on the advanced features of Pester (ie. mocking) but instead focused on how you can write Pester tests to validate SQL Server. You can find a repo of my demo and slides here. Fair warning, my presentation is heavy demo and talking, with brief slides.

As far as infrastructure testing goes, here’s a simple example that tests if the SQL Server service is running:

Describe 'SQL Server Services' {     
    It 'Should be running' {
        (Get-Service 'MSSQL$SQL1').Status | Should -Be 'Running'
    }
}

And here’s an example of querying SQL internals (it test if the max memory has been changed from the default value):

$SQLInstance = 'localhost\sql1'

$Query       = `
"SELECT [name], [value] 
FROM sys.configurations
WHERE [name] IN 
('max server memory (MB)')"

$ConfigQueryRes = Invoke-SQLCMD -ServerInstance $SQLInstance -Database 'master' -Query $Query

Describe "Instance Configuration on $SQLInstance"  {

    It "Maximum Server Memory should be set" {
        $maxmem = ($ConfigQueryRes | Where {$_.name -eq 'max server memory (MB)'}).value
        $maxmem | Should -Not -Be 2147483647
    }
}

Pester gives you the ability to break up your tests into many test scripts, thus avoiding a single lengthy Powershell script. I spoke at my presentation about how you could build a library of scripts. One for instance configuration, one for logins, one for jobs, etc… The advantage of Pester is that you can write scripts, run them, and collect results all in a consistent way.

At my present employer, I’ve written well over 1000 lines of Pester tests, spread across a dozen or so test files. However, to run all these tests is a simple call to invoke-pester, a one liner.