Some Pester Tests for SQL Defaults

When I was at PowerShell Conference EU in Hannover last month (The videos are available now – click here and the slides and code here) I found out about Irwin Strachans Active Directory Operations Test which got me thinking.

I decided to do the same for my usual SQL Set-up. Treating all of your servers to the same defaults makes it even easier to manage at scale remotely.

I am comfortable with using SMO to gather and change properties on SQL Instances so I started by doing this

        It 'Should have a default Backup Directory of F:\SQLBACKUP\BACKUPS' {
$Scriptblock = {
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server .
return $srv.BackupDirectory}
$State = Invoke-Command -ComputerName ROB-SURFACEBOOK -ScriptBlock $Scriptblock
$State |Should Be 'F:\SQLBACKUP\BACKUPS'

This is the how to find the properties that you want

  ## Load the Assemblies
## Create a Server SMO object
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server SERVERNAME

## Explore it

## If you find an array pick the first one and expand and then explore that
$srv.Databases[0] | select *
$srv.Databases[0] | gm

I quickly found as I added more tests that it was taking a long time to perform the tests (about 5 seconds each test) and that it took an age to fail each of the tests if the server name was incorrect or the server unavailable.

I fixed the first one by testing with a ping before running the tests

   ## Check for connectivity
if((Test-Connection $Server -count 1 -Quiet) -eq $false){
Write-Error 'Could not connect to $Server'

The continue is there because I wanted to loop through an array of servers

I improved the performance using a remote session and a custom object

      Describe "$Server" {
BeforeAll {
$Scriptblock = {
[pscustomobject]$Return = @{}
$srv = ''
$SQLAdmins = $Using:SQLAdmins
$srv = New-Object Microsoft.SQLServer.Management.SMO.Server $Server
$Return.DBAAdminDb = $Srv.Databases.Name.Contains('DBA-Admin')
$Logins = $srv.Logins.Where{$_.IsSystemObject -eq $false}.Name
$Return.SQLAdmins = @(Compare-Object $Logins $SQLAdmins -SyncWindow 0).Length - $Logins.count -eq $SQLAdmins.Count
$SysAdmins = $Srv.Roles['sysadmin'].EnumMemberNames()
$Return.SQLAdmin = @(Compare-Object $SysAdmins $SQLAdmins -SyncWindow 0).Length - $SysAdmins.count -eq $SQLAdmins.Count
$Return.BackupDirectory = $srv.BackupDirectory
$Return.DataDirectory = $srv.DefaultFile

The BeforeAll script block is run, as it sounds like it should, once before all of the tests, BeforeEach would run once before each of the tests. I define an empty custom object and then create an SMO object and add the properties I am interested in testing to it. I then return the custom object at the end

   $Return.Alerts82345Exist = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825}).Count
$Return.Alerts82345Enabled = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825 -and $_.IsEnabled -eq $true}).Count
$Return.SysDatabasesFullBackupToday = $srv.Databases.Where{$_.IsSystemObject -eq $true -and $_.Name -ne 'tempdb' -and $_.LastBackupDate -lt (Get-Date).AddDays(-1)}.Count
Return $Return
try {
$Return = Invoke-Command -ScriptBlock $Scriptblock -ComputerName $Server -ErrorAction Stop
catch {
Write-Error "Unable to Connect to $Server"

I was then able to test against the property of the custom object

   It 'Should have Alerts for Severity 20 and above' {
$Return.Alerts20SeverityPlusExist | Should Be 6
It 'Severity 20 and above Alerts should be enabled' {
$Return.Alerts20SeverityPlusEnabled | Should Be 6
It 'Should have alerts for 823,824 and 825' {
$Return.Alerts82345Exist |Should Be 3
It 'Alerts for 823,824 and 825 should be enebled' {
$Return.Alerts82345Enabled |Should Be 3

Occasionally, for reasons I haven’t explored I had to test against the value property of the returned object

          It "The Full User Database Backup should be scheduled Weekly $OlaUserFullSchedule" {
$Return.OlaUserFullSchedule.value | Should Be $OlaUserFullSchedule

I wanted to be able to run the tests against environments or groups of servers with different default values so I parameterised the Test Results as well and then the logical step was to turn it into a function and then I could do some parameter splatting. This also gives me the opportunity to show all of the things that I am currently giving parameters to the test for

   $Parms = @{
Servers = 'SQLServer1','SQLServer2','SQLServer3';
SQLAdmins = 'THEBEARD\Rob','THEBEARD\SQLDBAsAlsoWithBeards';
BackupDirectory = 'C:\MSSQL\Backup';
DataDirectory = 'C:\MSSQL\Data\';
LogDirectory = 'C:\MSSQL\Logs\';
MaxMemMb = '4096';
Collation = 'Latin1_General_CI_AS';
TempFiles = 4 ;
OlaSysFullFrequency = 'Daily';
OlaSysFullStartTime = '21:00:00';
OlaUserFullSchedule = 'Weekly';
OlaUserFullFrequency = 1 ;## 1 for Sunday
OlaUserFullStartTime = '22:00:00';
OlaUserDiffSchedule = 'Weekly';
OlaUserDiffFrequency = 126; ## 126 for every day except Sunday
OlaUserDiffStartTime = '22:00:00';
OlaUserLogSubDayInterval = 15;
OlaUserLoginterval = 'Minute';
HasSPBlitz = $true;
HasSPBlitzCache = $True;
HasSPBlitzIndex = $True;
HasSPAskBrent = $true;
HASSPBlitzTrace =  $true;
HasSPWhoisActive = $true;
LogWhoIsActiveToTable = $true;
LogSPBlitzToTable = $true;
LogSPBlitzToTableEnabled = $true;
LogSPBlitzToTableScheduled = $true;
LogSPBlitzToTableSchedule = 'Weekly';
LogSPBlitzToTableFrequency = 2 ; # 2 means Monday
LogSPBlitzToTableStartTime  = '03:00:00'}

Test-SQLDefault @Parms

I have some other tests which always return what I want, particularly the firewall rules which you will have to modify to suit your own environment

To be able to run this you will need to have the Pester Module. If you are using Windows 10 then it is installed by default, if not

  Find-Module –Name 'Pester' | Install-Module

You can find more about Pester here and here and also these videos from the conference
You can find the tests on GitHub here and I will continue to add to the defaults that I check.
This is not a replacement for other SQL configuration tools such as PBM but it is a nice simple way of giving a report on the current status of a SQL installation either at a particular point in time when something is wrong or after an installation prior to passing the server over to another team or into service


DBA Database scripts are on Github

It started with a tweet from Dusty


The second session I presented at the fantastic PowerShell Conference Europe was about using the DBA Database to automatically install DBA scripts like sp_Blitz, sp_AskBrent, sp_Blitzindex from Brent Ozar , Ola Hallengrens Maintenance Solution , Adam Mechanics sp_whoisactive , This fantastic script for logging the results from sp_whoisactive to a table , Extended events sessions and other goodies for the sanity of the DBA.

By making use of the dbo.InstanceList in my DBA database I am able to target instances, by SQL Version, OS Version, Environment, Data Centre, System, Client or any other variable I choose. An agent job that runs every night will automatically pick up the instances and the scripts that are marked as needing installing. This is great when people release updates to the above scripts allowing you to target the development environment and test before they get put onto live.

I talked to a lot of people in Hannover and they all suggested that I placed the scripts onto GitHub and after some how-to instructions from a few people (Thank you Luke) I spent the weekend updating and cleaning up the code and you can now find it on GitHub here


I have added the DBA Database project, the Powershell scripts and Agent Job creation scripts to call those scripts and everything else I use. Some of the DBA Scripts I use (and links to those you need to go and get yourself for licensing reasons) and the Power Bi files as well. I will be adding some more jobs that I use to gather other information soon.

Please go and have a look and see if it is of use to you. It is massively customisable and I have spoken to various people who have extended it in interesting ways so I look forward to hearing about what you do with it.

As always, questions and comments welcome



A Day In The Life of a SQLBits Volunteer

DSCN0286 (3)

So SQLBits has finished for another year. About 1500 people registered for the largest SQL Conference in Europe held over 4 days in Liverpool last week. 2 days of full day sessions and 2 days of shorter sessions, a pub quiz, a party (literally) out of this world.

It is organised and run by volunteers

Yes, you’re right. Wow.

Of course we must pay special thanks to Simon, Darren, Chris, Jonathan, Annette, Allan and Alex without forgetting all that JRJ has done. They ensured that the venue, sessions, speakers, sponsors, audio visual and not forgetting the amazing party by Sneaky Experience (Thank you Julia), all occurred at the right time in the right place with the right people and the work and effort they have put in cannot be over-estimated but this post isn’t about those generous, inspiring, amazing folk

Several people asked me what a volunteer does and how they can become one and hopefully this post will answer both of those questions. More than 40 people volunteered to help at SQL Bits

Some volunteers arrived on Tuesday to help with the set up. Making sure that we knew where everything was and helping to lift and carry all of the things that needed moving.

WP_20160504_07_13_44_Pro (2)


We wanted to begin the process of packing the swag bags so that all the attendees on Wednesday could have them as they arrived but unfortunately we were let down by a delivery firm and that was not possible. That job was completed by some very dedicated helpers during all of Wednesday and finished off by all helpers after Wednesdays sessions had finished. Many, many thanks and respect particularly to Bob and Conan for their dedication to the bag-packing cause, although I did hear “If I ever see another bag again ……..”





bean bags
All of the registration desks with badges, lanyards and booklets were set up ready for the next day, the covers were put on the tables, the bean bags put out. (They were needed later!!)



We started every morning with a helpers briefing at 7am
WP_20160506_07_40_18_Pro (2).jpg

where we ensured that everyone knew what was happening that day. Most helpers took some part in registration whether it was being at the desks handing out badges, assisting speakers and sponsors on arrival with questions and some the other jobs that needed doing such as putting the table cloths on the tables in the domes and getting out the bags.

WP_20160504_10_31_33_Pro (2)

DSCN0284.JPGDuring every session there were two room monitors. Their responsibility was to ensure that everything went smoothly during the day and during each of the sessions on Friday and Saturday. They assisted the speaker with drinks, timings and the other foibles that technology can throw, they would have known what to do if there were issues with the domes.


dome monitoringThey also made sure that everyone was in the correct session and answered lots of questions. The bit that hopefully most attendees missed was that in the case of any issues they called in one of the 5 or 6 super helpers who were patrolling the arena, who could go and get the AV guys or the venue staff to iron out the wrinkles, making sure that the water coolers got re-filled etc

They took all of your feedback forms and took them to the main desk. There were always people at the front desk helping, although Alex, Annette and Terri spent almost all of their time there.

Of course it doesn’t stop there. During the breaks and particularly at lunchtime the helpers were available and indeed noticeable. They assisted with the queues at lunchtime and for the key-note


answered questions and they helped attendees with session choices. There were always people and questions that they were willing to answer or to find someone who could.

Once the sessions had finished, the helpers didn’t. They manned and womanned the registration desks in the evening. Returned all of the feedback forms and helped with sorting out the venue ready for the next day. There was also a de-brief for any lessons learned.

WP_20160504_17_54_40_Pro (2)

They even gave out the free drinks tokens at the entrance to the party! (More Pictures Here)

DSCN0376 (2)

So why do they do it? They are all part of this amazing community dedicated to providing great events for people to learn. Many of them are volunteers at other events and/or user group leaders and attendees and want to give something back to the community.) . They were able to choose the full day and hour sessions that they attended so were still able to learn from the amazing speakers at the brilliant sessions available. Everyone was able to choose which days they helped it doesn’t have to be for every day you are at the conference. They are a wonderful group of people and I have made some fantastic friendships by volunteering and get great hugs 🙂

WP_20160506_09_15_33_Pro (2).jpgYou also get asked the most brilliant questions like “Excuse me, I have a Dalek in my van, what would you like me to do with it?”

If that sounds like something you would like to do, you just need to email with ‘I would like to be a helper next year at SQLBits’ in the subject. You can also offer to help at your local user group or at other events, speak to your user group leaders for more details on that

If it’s not for you, that’s cool but please, for me, the next time you see them at an event just say thank you. It really means a lot and they thoroughly deserve every word of thanks that we give them.