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 {$ -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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.