26 Validating your estate with dbachecks
For decades, most of the team writing this book had our own daily/weekly/monthly checklists to validate our SQL Server environments. At least once each day, we’d ensure that backups were scheduled and working as required. We’d check to see whether all of our integrity checks passed. We’d even spend a lot of time keeping our checklists up to date as our environment grew, and we learned more about managing SQL Server. Some of us performed this validation manually, whereas others created an automated routine that would perform the checks automatically.
What we didn’t have for all those years was a single, community-wide checklist—nor did we have have a free and open source framework to make our checks easier. Not having those meant a lot of wasted time and repeated work!
To address this problem, the SQL PowerShell community came together to create crowdsourced checks using dbatools and Pester (sqlps.io/pester) tests. This project became known as dbachecks.
26.1 What dbachecks and dbatools have in common
You may be wondering why we dedicated a chapter to dbachecks, a totally distinct PowerShell module from dbatools, in a book about dbatools. We did so for a number of reasons, including the following:
dbachecks uses dbatools to get a number of different configurations and properties, and uses the Pester testing framework PowerShell module to check whether the results align with our desired outcome.
Pester makes it possible to create our own SQL Server tests using PowerShell, and, as Rob likes to say, “If you can get it with PowerShell, you can test it with Pester.” With the framework provided by dbachecks, it is now easy to create and share these tests with the SQL Server community in a centralized repository.
Like dbatools, you can install dbachecks right from the PowerShell Gallery. To install dbachecks for your own user account, run the code shown here.
Listing 26.1 Installing dbachecks
This will install dbachecks, as well as two modules it depends on: dbatools and PSFramework. Compatible versions of Pester are included in Windows, but if you need to install Pester manually, you must install version 4.10.1 or earlier, as seen in the next listing.
Listing 26.2 Specifying a version when installing dbachecks
26.2 Our first check
Now that we have dbachecks installed, we can run our very first check. Because backups are one of the things data professionals care about most, we’ll make that our first check.
Previously in chapter 10, we showed how dbatools can help with database backups, and now, with dbachecks, we can run tests to ensure that our backups are running as expected. To check whether our databases have been running full backups in the last 24 hours, we’ll use the primary workhorse command: Invoke-DbcCheck. This is the command that performs all of the actual checks against both local and remote hosts. In the following listing, we’ll run one check, LastFullBackup, against one SQL Server instance, dbatoolslab.
Listing 26.3 Checking for last full backup in the previous 24 hours
Pretty easy, right? We tried to make this tool as easy as possible for our framework to be immediately useful. Ultimately, however, working with dbachecks can be as simple or as complex an experience as you’d like.
Regarding listing 26.3, you may also be wondering how we knew to specify the LastFullBackup value for the -Check parameter. We’ll get into that shortly, but before we do, let’s take a look at the output generated by executing the command shown next.
PS> Invoke-DbcCheck -SqlInstance dbatoolslab -Check LastFullBackup Pester v4.10.1 Executing all tests in 'C:Program FilesWindowsPowerShellModules dbachecks2.0.14checksDatabase.Tests.ps1' with Tags LastFullBackup Executing script C:Program FilesWindowsPowerShellModulesdbachecks 2.0.14checksDatabase.Tests.ps1 Describing Last Full Backup Times ① Context Testing last full backups on dbatoolslab ② [-] Database AdventureWorks should have full backups less than 1 days old on dbatoolslab 8ms ③ Expected the actual value to be greater than 2021-10-15T 16:57:48.8673943Z, because Taking regular backups is extraordinarily important, but got 2021-10-10T05:35:19.0000000Z. ④ 498: $psitem.LastBackupDate.ToUniversalTime() | Should -BeGreaterThan (Get-Date).ToUniversalTime().AddDays( - ($maxfull)) -Because "Taking regular backups is extraordinarily important" ⑤ at <ScriptBlock>, C:Program FilesWindowsPowerShellModules dbachecks2.0.14checksDatabase.Tests.ps1: line 498 [+] Database master should have full backups less than 1 days old on dbatoolslab 7ms [+] Database model should have full backups less than 1 days old on dbatoolslab 10ms [+] Database msdb should have full backups less than 1 days old on dbatoolslab 11ms Tests completed in 1.73s Tests Passed: 3, Failed: 1, Skipped: 0, Pending: 0, Inconclusive: 0 ⑥
① Describe block of the Pester test tells us the name of the current check we’re running.
② The Context block of the Pester test tells us which SQL Server we’re running the tests against.
③ The It block of the Pester test has the details on what we tested and the result. The [-] shows it was a failed test.
④ On a failed test, we get more details on why it failed. We can see we should have had a backup since 2021-10-15, but the last one was on 2021-10-10.
⑤ Pester allows you to add a because block to your tests that tells us why we care that the test failed.
In the summary output ⑥, you can see that three tests passed but one failed, and it provides details on the database that doesn’t have a recent backup and why this is important.
26.3 Viewing all available checks
dbachecks currently provides over 130 tests, or checks, that help validate the health of our SQL Server estates. This number has steadily increased over time as more SQL Server DBAs have added their own checks to the toolset. The following code shows how to get a list of all available checks by running Get-DbcCheck.
PS> Get-DbcCheck | Select-Object Group, UniqueTag Group UniqueTag Description ----- --------- ----------- Agent DatabaseMailEnabled Tests that the Database Mail XPs configu... Agent AgentServiceAccount Tests that the SQL Agent Account is runn... Agent DbaOperator Tests that the specified (default blank)... Agent FailsafeOperator Tests that the specified (default blank)... Agent DatabaseMailProfile Tests that the specified (default blank)... Agent AgentMailProfile Tests to see if the SQL Server Agent Ale... Agent FailedJob Tests that enabled Agent Jobs last outco... Agent ValidJobOwner Tests that all Agent Jobs have a Job Own... Agent AgentAlert Tests that there are Agent Alerts set up... Agent JobHistory Tests that the job history configuration... Agent LongRunningJob Tests that any currently running agent j... Agent LastJobRunTime Tests that the last duration of the agen... Database DatabaseCollation Tests that the Database Collation matche... Database SuspectPage Tests that there are 0 Suspect Pages for... Database TestLastBackup Restores the last backup of a database o... ...
The command outputs more information by default, but we wanted to give a gentle introduction to a list of all checks that are available. We’ll explore the functionality of Get-DbcCheck further in listing 26.6.
26.4 Configuring the check parameters
Out of the box, dbachecks uses reasonable default values for each check. For example, LastFullBackup tests to see whether a full backup has been completed in the past 24 hours. This is reasonable if you take full backups daily, but some organizations have very different backup strategies. Luckily, dbachecks is flexible and allows users to customize to match their requirements. For example, let’s say an organization has the following strategy:
This is pretty specific, but dbachecks is configurable enough to make this possible. Most, if not all, checks have configuration options available, which allows you to shape the dbachecks tests to be exactly what you’d expect in your environment.
You can discover the configuration options available for each check using Get-DbcCheck. The Config property, seen next, shows which options are available for the LastFullBackup check.
Listing 26.6 Viewing the metadata for the LastFullBackup check
PS> Get-DbcCheck -Tag LastFullBackup | Format-List Group : Database ① Type : Sqlinstance ② UniqueTag : LastFullBackup ③ AllTags : LastFullBackup, LastBackup, Backup, DISA, Varied, Database ④ Config : app.sqlinstance policy.backup.fullmaxdays policy.backup.newdbgraceperiod skip.backup.readonly skip.backup.secondaries ⑤ Description : Tests if the last full backup of a database is less than the specified number of days (default 1) except for offline databases and read-only databases (read-only if specified) and databases created recently (if specified). ⑥
① The group this check falls into; this check runs at the database level.
② The type of check tells us what object should be passed in.
③ Each check must have a unique tag; this is how we’ll call an individual check.
④ The check can also have more tags, which allows us to run multiple checks at once.
⑤ Config shows us the properties that we can control so the test will check we’re in the exact desired state.
⑥ Finally, we have a description of the check.
If you guessed that we’ll need to modify policy.backup.fullmaxdays, you were right.
Before we make any changes, let’s first confirm the current value for policy.backup .fullmaxdays. In the next listing, we expect to see a value of 1, which is the default that is available out of the box.
Listing 26.7 Confirming the value for policy.backup.fullmaxdays
PS> Get-DbcConfig -Name policy.backup.fullmaxdays Name Value Description ---- ----- ----------- policy.backup.fullmaxdays 1 Maximum number of days before Full...
To see all current configurations, run Get-DbcConfig without any additional parameters.
Now that we’ve confirmed that policy.backup.fullmaxdays is set to 1, the next code snippet shows how we can update that value to 7.
Listing 26.8 Changing the config setting
PS> Set-DbcConfig -Name policy.backup.fullmaxdays -Value 7 Name Value Description ---- ----- ----------- policy.backup.fullmaxdays 7 Maximum number of days before Full Backu...
We’ll also need to do the same to configure the settings for the differential and log backup thresholds.
Note The configurations are set in the registry, so once they have been set on the machine, they don’t need to be configured every time you run your checks.
Once the configuration is set up, we can run all the LastBackup checks to make sure our backup strategy is being met. This is shown in listing 26.9.
We’ve taken a full backup since the last failed check, so that should be back in our desired state. We’ve also specified Fails as the value for the -Show parameter on the call of Invoke-DbcCheck, which reduces the amount of output we see because only failed checks are highlighted.
Listing 26.9 Setting the config settings for full, diff, and t-log backups
PS> Set-DbcConfig -Name policy.backup.fullmaxdays -Value 7 ① PS> Set-DbcConfig -Name policy.backup.diffmaxhours -Value 24 ② PS> Set-DbcConfig -Name policy.backup.logmaxminutes -Value 240 ③ PS> Invoke-DbcCheck -SqlInstance dbatoolslab -Check LastBackup -Show Fails ④ Pester v4.10.1 Executing all tests in 'C:Program FilesWindowsPowerShellModules dbachecks2.0.14checksDatabase.Tests.ps1' with Tags LastBackup Executing script C:Program FilesWindowsPowerShellModulesdbachecks 2.0.14checksDatabase.Tests.ps1 Describing Last Full Backup Times ⑤ Context Testing last full backups on dbatoolslab Describing Last Diff Backup Times Context Testing last diff backups on dbatoolslab Describing Last Log Backup Times Context Testing last log backups on dbatoolslab [-] Database AdventureWorks log backups should be less than 240 minutes old on dbatoolslab 7ms ⑥ Expected the actual value to be greater than 2021-10-20T00:42:26 .6170000, because Taking regular backups is extraordinarily important, but got 2021-10-10T08:00:01.0000000Z.564: $psitem.LastLogBackupDate.ToUniversalTime() | Should -BeGreaterThan $sqlinstancedatetime.AddMinutes( - ($maxlog) + 1) -Because "Taking regular backups is extraordinarily important" at <ScriptBlock>, C:Program FilesWindowsPowerShellModules dbachecks2.0.14checksDatabase.Tests.ps1: line 564 Tests completed in 2.33s Tests Passed: 5, Failed: 1, Skipped: 0, Pending: 0, <7> Inconclusive: 0
① Sets the configuration to check for a differential backup within 24 hours/daily
② Sets the configuration to check for a full backup within 7 days
③ Sets the configuration to check for a log backup within 240 minutes/4 hours
④ Uses the -Show parameter to determine how much detail is returned
⑤ No failed tests for the full backup checks, so just the headings appear in the output.
⑥ A failed test, with details, for our log backup check
Now that you’ve seen how to find and modify values for these three configuration options, you can apply the same technique to all configuration options within dbachecks. This will help you customize your checks to align with your organizational policies.
26.5 Storing the output data in a database
We’ve now seen how to run the checks, and we learned how to read and understand the output. This is perfect for a few single checks that we want to verify on-demand, but what you may find even more useful is collecting the results to analyze over time.
dbachecks makes it easy to save the results of each check to a database, which allows you to follow the check’s evolution over time. Being able to identify trends helps you make decisions about what resources are needed for your estate, like more storage or a bigger datatype for an identity column. In this section, we’ll learn how to both store the output and identify trends over time with a Power BI dashboard.
26.5.1 Storing data
To store dbachecks output in a database, we first need to convert the output of our tests to a format that SQL Server can understand. Within dbachecks, we use the Convert-DbcResult command. Once the results have been converted, we can save them to a specific database using Write-DbcTable, as shown in the following listing.
Listing 26.10 Running dbachecks and storing the results in a database
PS> $splatInvokeCheck = @{ SqlInstance = "dbatoolslab" Check = "LastBackup" Passthru = $true } PS> Invoke-DbcCheck @splatInvokeCheck | Convert-DbcResult -Label dbatoolsMol | Write-DbcTable -SqlInstance dbatoolslab -Database DatabaseAdmin
The -Label parameter used in the Convert-DbcResult command is optional but can be useful when identifying and analyzing a specific set of results. If you’re curious about the output, the results of our own tests from listing 26.10 can be seen in figure 26.1.
As you run these checks over time, you will get a good picture of what your environment looks like day to day and how it’s changing. Depending on the check, it can also alert you to what needs to be addressed.
26.5.2 Power BI dashboard
With the PowerShell module, we also provide a Power BI dashboard (.pbix file) with different visualizations to help us analyze the results of our checks. This dashboard will point to our database and read the data from there. For that we can use the Start-DbcPowerBi command to open the dashboard, as illustrated in the next code snippet.
Listing 26.11 Opening the dbachecks Power BI dashboard
Note You will need to have Microsoft Power BI desktop installed to open the dashboard.
26.5.3 Configuring the connection
When the dashboard opens, you will be prompted for the SqlInstance and ChecksResultDBName values, as shown in figure 26.2.
Next, click the Load button, and your data will appear, as shown in figure 26.3.
26.6 Hands-on lab
-
Check the documentation at sqlps.io/dbacheckslatest. We have lots of blog posts from our contributors and users showing how to use it.
-
Run a check to validate whether your
MaxMemorysetting is configured correctly.