Pester is a testing framework and it can be used to automate the testing of your PowerShell code. It provides a language that allows you to define test cases. There have been a few posts on Pester and dbatools including:
- Rob Sewells’ (t|b|g) “Test-DbaLastBackup“
- Claudio Silvas’ (t|b|g) “Someone is not following the best practices” (scroll just past halfway down to get to the Pester section)
- More recently, Andrew Pruski (t|b) “Checking SQL Configuration with Pester & Dbatools“
Andrew’s post recent post pushed me to go ahead and do my own. With what I have learned from Pester, it seems to be best to test each command on its own. That got me to thinking, what if I walk into a large environment and I want to see an enterprise summary of best practices? I started working on a few different versions with the ultimate goal of using dbatools function Get-DbaRegisteredServer. I have included a few examples of Test functions that include best practices, as well as some default database best practices all from dbatools.
In my first run, I wanted to make sure that I can pass multiple servers with multiple tests and came up with the below script. You will notice the top section that uses the Get-Dba functions, I had to supply my own values of the properties I am testing. In the bottom section, you will notice that dbatools Test-Dba functions have the IsBestPractice property already and that is all you have to test against.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
# Check if the modules are present if ((Get-Module -ListAvailable -Name dbatools, Pester | Sort-Object -Unique).Count -eq 2) { try { # Import the modules Import-Module dbatools Import-Module Pester # test the connections to the instance $result = Test-DbaConnection -SqlInstance $SqlInstance -SqlCredential $Credential if ($result[-1].ConnectSuccess) { # we are going to check for best pratice server settings using the Get-Dba functions Describe 'Testing Database Best Practices' { # Loop through the SqlServer $SqlInstance.ForEach{ # Create a Context for each Instance. Context "Testing User Databases on $($_)" { # Loop through the User databases on the instance (Get-DbaDatabase -SqlInstance $_ -ExcludeAllSystemDb).ForEach{ # Refer to the database name and Instance name inside a $() It "Database $($_.Name) on Instance $($_.Parent.Name) should not have TRUSTWORTHY ON" { $_.Trustworthy | Should Be $false } It "PageVerfiy set to Checksum" { $_.PageVerify| Should Be "Checksum" } It "AutoShrink set to False" { $_.AutoShrink| Should Be $false } It "AutoClose set to False" { $_.AutoClose| Should Be $false } } } Context "Testing LastGoodCheckDb on $($_)" { (Get-DbaLastGoodCheckDb -SqlServer $_ ).ForEach{ { It "$($_.Server) database $($_.Database) had a successful CheckDB" { $_.Status | Should Be 'Ok' } It "$($_.Server) database $($_.Database) had a CheckDB run in the last 3 days" { $_.DaysSinceLastGoodCheckdb | Should BeLessThan 3 } It "$($_.Server) database $($_.Database) has Data Purity Enabled" { $_.DataPurityEnabled| Should Be $true } } } } #now we are going to check for best pratice server settings using the Test-Dba functions #create empty array $TestCases = @() # Fill the Testcases with the values and a Name of Instance Describe 'Testing for Server BestPractices' { $SqlInstance.ForEach{ # Put the TestCases 'Name' in <> and add the TestCases parameter It "Memory is set to Best Practices on $($_)" -TestCases $TestCases { # Write the test using the TestCases Name (Test-DbaMaxMemory -SqlInstance $_).SqlMaxMB | Should Be (Test-DbaMaxMemory -SqlInstance $_).RecommendedMB } It "TempDbConfiguration is set to Best Practices on $($_)" -TestCases $TestCases { # Write the test using the TestCases Name (Test-DbaTempDbConfiguration -SqlInstance $_).IsBestPractice | Should Be $True, $True, $True, $True, $True } It "PowerPlan is set to Best Practices on $($_)" -TestCases $TestCases { # Write the test using the TestCases Name (Test-DbaPowerPlan -SqlInstance $_).IsBestPractice | Should Be $True } It "OptimizeForAdHoc is set to Best Practices on $($_)" -TestCases $TestCases { # Write the test using the TestCases Name (Test-DbaOptimizeForAdHoc -SqlInstance $_).CurrentOptimizeAdHoc | Should Be (Test-DbaOptimizeForAdHoc -SqlInstance $_).RecommendedOptimizeAdHoc } } } } } } else { Write-Warning "Couldn't connect to instance $SqlInstance" } } catch { Write-Warning "Something went wrong.`n$($_)." } } else { Write-Warning "Please check if the module dbatools or Pester is installed!" } |
Look at those results! You notice “Memory” is not set correctly on one system and “TempDb” is not set correctly on another. Since this worked, I had to make one code change so I can call it from another script for a summary level. I commented out the instances I was checking manually and then put in a parameter so I can pass multiple servers from another script.
|
1 2 3 4 5 6 7 8 |
#$SqlInstance= 'SqlServerA','SqlServerB' [CmdletBinding()] Param ( [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)] [Alias("ServerInstance", "SqlServer", "SqlServers")] [DbaInstanceParameter[]]$SqlInstance ) |
Next, I decided I want to only see failures. With Pester, to show failures, all you have to do is use parameter -Show Fails after Invoke-Pester. I also wanted to receive an email on those failures across the enterprise in case a change was made and I was not near a system. Note: If you plan to utilize the script, please make sure you update the Send-MailMessage parameters.
|
1 2 3 4 5 6 7 8 |
$serverlist = Get-DbaRegisteredServer -SqlInstance localhost Foreach($SqlServer in $serverlist){ Invoke-Pester -Show Fails -Script @{Path = 'C:\SQl_Tools\Pester\Test-DbaBestPractices.ps1'; Parameters = @{ SqlServer = $SqlServer}} -PassThru | Select -ExpandProperty TestResult | Where {-not $_.passed} | foreach { Send-MailMessage -From "DBA@sftp.com" -Subject "$($_.Describe) Pester Failure" -body ($_ | Out-String) -To 'Dba@sftp.com' -SmtpServer 'smtp.domain.com' } } |
The Email output looks like the following:
There we go! My first Pester script and tests across an enterprise environment. I will be re-visiting this post down the road once I learn more about Pester. I cannot wait for the latest beta build to be pushed to the Gallery. There are some new -Should commands that I would like to utilize.