In this blog post, I’ll do an introduction to the PowerShell module dbachecks.
dbachecks uses Pester and dbatools to validate your SQL Server infrastructure.
With very minimal configuration you can check that your infrastructure is configured following standard best practices or your own policy.
We will see the following topics
– Prerequisites for dbachecks Installation
– Introduction to Pester
– Perform a Check
– Manage the Configuration items – Import & Export
– Power BI dashboard
Prerequisites for dbachecks Installation
The dbachecks module depends on the following modules:
The easiest way to perform the installation is to do a simple Install-Module. It will get the latest dbachecks version from the PSGallery and install all the requires modules up to date.
I had many issues with this method.
The latest versions of PSFramework (1.4.150) did not seem to work with the current dbachecks version.
Installing the latest version of Pester (5.0.4) brings issues too.
When running a command I would get the following error:
Unable to find type [Pester.OutputTypes]. At line:219 char:9 + [Pester.OutputTypes]$Show = 'All' + ~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (Pester.OutputTypes:TypeName) , RuntimeException + FullyQualifiedErrorId : TypeNotFound
To avoid this, prior to installing dbachecks, you should first install PSFramework with version 1.1.59.
Pester is already shipped with the recent versions of Windows with version 3.4.
If want to get a newer version, install manually version 4. Issues seem to come with version 5.
Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted Install-Module PSFramework -RequiredVersion 1.1.59 Install-Module Pester -RequiredVersion 4.10.1 -Force -SkipPublisherCheck Install-Module dbachecks
dbacheks relies heavily on Pester. Pester is a framework that brings functions to build a unit-test for PowerShell code.
If you have don’t know what is Pester I’d recommend you read my introduction to Pester post here.
Perform a Check
Now let’s talk about dbachecks. It’s is basically a set of Pester tests for your SQL Server infrastructure with code relying heavily on dbatools module.
Let’s look at the list of available “Checks” from dbachecks with Get-DbcCheck.
Let’s run a Check on an SQL Server instance. To do so we use the Invoke-DbcCheck command with the Check UniqueTag and the target Instance name.
This one checks for the database owner for all user databases of the instance. The default value for this check is configured to “sa”.
My check returned everything green. There’s only one database on this instance and its database owner is “sa”.
Check multiple instances
They are many ways to run checks against multiple instances.
You can define a list of instances in the config parameter with the command below. I’ll come to configuration elements in a minute.
Set-DbcConfig -Name app.sqlinstance -Value "server1\InstA", "localhost", "server2\instA"
Here I will use a CMS and the dbatools command Get-DbaRegisteredServer to get my list of instances. On the other instance, one of the databases got a non-“sa” database owner.
Maybe this owner is a valid one and I want to have this check succeed. We can modify the check configuration.
Check Configuration elements
All checks can have configuration elements.
To search in the configuration elements you can use Get-DbcConfig. I want to change the database owner’s name, I can search for all config items with names like “owner”.
The configuration values are also available with Get-DbcConfigValue.
So now, with Set-DbcConfig I can add a valid database owner to the ValidDatabaseOwner check.
Manage the Configuration items – Import & Export
We have seen how to use Set-DbcConfig to modify your checks configuration. You don’t need to change those configurations one by one every time you want to check your infrastructure.
All configuration items can be exported to a JSON file and imported back again.
I can set the configuration items as needed and then do Export-DbcConfig specifying the destination file:
# LastFullBackup - Maximum number of days before Full Backups are considered outdated Set-DbcConfig -Name policy.backup.fullmaxdays -Value 7 # Percent disk free Set-DbcConfig -Name policy.diskspace.percentfree -Value 5 # The maximum percentage variance that the last run of a job is allowed over the average for that job Set-DbcConfig -Name agent.lastjobruntime.percentage -Value 20 # The maximum percentage variance that a currently running job is allowed over the average for that job Set-DbcConfig -Name agent.longrunningjob.percentage -Value 20 # Maximum job history log size (in rows). The value -1 means disabled Set-DbcConfig -Name agent.history.maximumhistoryrows -Value 10000 # The maximum number of days to check for failed jobs Set-DbcConfig -Name agent.failedjob.since -Value 8 # The number of days prior to check for error log issues - default 2 Set-DbcConfig -Name agent.failedjob.since -Value 3 Export-DbcConfig -Path "$($HOME)\Documents\WindowsPowerShell\MorningCheck-Qual.json"
As you can guess imports of Config files are done with Import-DbcConfig.
Import-DbcConfig -Path "$($HOME)\Documents\WindowsPowerShell\MorningCheck-Qual.json"
The Show parameter
The dbachecks output in the console gives a great level of details on what is going on. When you have thousands of checks running you might not want to get this wall of green text.
To show only the Failed checks you can use the -Show parameter of Invoke-DbcCheck with the value “Fails”.
Invoke-DbcCheck -Check ValidDatabaseOwner -Show Fails
Here is an output example:
<?xml version="1.0" encoding="utf-8" standalone="no"?> <test-results xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_noNamespaceSchemaLocation="nunit_schema_2.5.xsd" name="Pester" total="2" errors="0" failures="1" not-run="0" inconclusive="0" ignored="0" skipped="0" invalid="0" date="2020-12-14" time="15:29:47"> <environment clr-version="4.0.30319.42000" user-domain="win10vm4" cwd="C:\Users\win10vm4admin\Documents\WindowsPowerShell\Modules\dbachecks\2.0.7\checks" platform="Microsoft Windows 10 Pro|C:\WINDOWS|\Device\Harddisk0\Partition4" machine-name="win10vm4" nunit-version="188.8.131.52" os-version="10.0.18363" user="win10vm4admin" /> <culture-info current-culture="en-US" current-uiculture="en-US" /> <test-suite type="TestFixture" name="Pester" executed="True" result="Failure" success="False" time="0.3166" asserts="0" description="Pester"> <results> <test-suite type="TestFixture" name="C:\Users\win10vm4admin\Documents\WindowsPowerShell\Modules\dbachecks\2.0.7\checks\Database.Tests.ps1" executed="True" result="Failure" success="False" time="0.3166" asserts="0" description="C:\Users\win10vm4admin\Documents\WindowsPowerShell\Modules\dbachecks\2.0.7\checks\Database.Tests.ps1"> <results> <test-suite type="TestFixture" name="Valid Database Owner" executed="True" result="Failure" success="False" time="0.2048" asserts="0" description="Valid Database Owner"> <results> <test-suite type="TestFixture" name="Testing Database Owners on localhost" executed="True" result="Failure" success="False" time="0.1651" asserts="0" description="Testing Database Owners on localhost"> <results> <test-case description="Database dbi_tools - owner sa should be in this list ( sa ) on win10vm4" name="Valid Database Owner.Testing Database Owners on localhost.Database dbi_tools - owner sa should be in this list ( sa ) on win10vm4" time="0.0022" asserts="0" success="True" result="Success" executed="True" /> <test-case description="Database testDB - owner win10vm4\win10vm4admin should be in this list ( sa ) on win10vm4" name="Valid Database Owner.Testing Database Owners on localhost.Database testDB - owner win10vm4\win10vm4admin should be in this list ( sa ) on win10vm4" time="0.0043" asserts="0" success="False" result="Failure" executed="True"> <failure> <message>Expected collection sa to contain 'win10vm4\win10vm4admin', because The account that is the database owner is not what was expected, but it was not found.</message> <stack-trace>at <ScriptBlock>, C:\Users\win10vm4admin\Documents\WindowsPowerShell\Modules\dbachecks\2.0.7\checks\Database.Tests.ps1: line 172 172: $psitem.Owner | Should -BeIn $TargetOwner -Because "The account that is the database owner is not what was expected"</stack-trace> </failure> </test-case> </results> </test-suite> </results> </test-suite> </results> </test-suite> </results> </test-suite> </test-results>
These XML files can be used to automate reporting with the tool of your choice.
There’s a way to export the results to Excel. If you want to try it I’d recommend you to read Jess Pomfret’s blog post dbachecks meets ImportExcel.
Power BI dashboard
Checks can be displayed in a beautiful PowerBI dashboard.
The Update-DbcPowerBiDataSource command converts results and exports files in the required format for launching the Power BI command Start-DbcPowerBI.
The Update-DbcPowerBiDataSource command can take an “Environnement” parameter which is useful to compare your environments.
Here is an example of how it can be used.
Import-DbcConfig -Path "$($HOME)\Documents\WindowsPowerShell\MorningCheck-Qual.json" Invoke-DbcCheck -Check ValidDatabaseOwner, ErrorLogCount ` -Show Summary -Passthru | Update-DbcPowerBiDataSource -Environment 'Qual' Import-DbcConfig -Path "$($HOME)\Documents\WindowsPowerShell\MorningCheck-Prod-Listener.json" Invoke-DbcCheck -Check ValidDatabaseOwner, ErrorLogCount ` -Show Summary -Passthru | Update-DbcPowerBiDataSource -Environment 'Prod' Start-DbcPowerBi
From my experience, dbatools use amongst DBA has grown a lot recently. Likewise, I think dbacheck will be used more and more by DBAs in the years to come.
It’s easy to use and can save you save a lot of time for your Daily/Weekly SQL Server checks.
This blog post was just to get you started with dbachecks. Do not hesitate to comment if you have any questions.