Infrastructure at your Service

Steven Naudet

Validate your SQL Server infrastructure with dbachecks

Introduction

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
– Output

– Power BI dashboard

Prerequisites for dbachecks Installation

The dbachecks module depends on the following modules:

  • dbatools
  • Pester
  • PSFramework

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

Here is what I got working:

Pester

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.

dbatools

The checks performed by dbatools are based on dbatools functions. If you didn’t tried dbatools yet I’d recommend you to have a look at dbatools’ repository and try a few commands.

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.

As you can see, they are currently 134 checks available covering a wide range of configurations you might want to check.

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.

Here is the output of the same check run again:

Of course, multiple tests can be run at the same time, for example:

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"

Here is the output of the Export-DbcConfig:

As you can guess imports of Config files are done with Import-DbcConfig.

Import-DbcConfig -Path "$($HOME)\Documents\WindowsPowerShell\MorningCheck-Qual.json"

Output

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

If you want even fewer details, you can use -Show Summary.

XML files

Tests results can also be saved to XML files using the OutputFile parameter like this:

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="2.5.8.0" 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 &lt;ScriptBlock&gt;, 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.

Excel export

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

The dashboard.

Conclusion

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.

3 Comments

  • Ed Zerylnick says:

    Thank you for a useful article. I tuned into it because I haven’t been able to do a completely successful upgrade of dbachecks for quite a while. My last successful upgrade was dbachecks version 1.2.21 with Pester 4.9.0. That got me looking further and now I find that Pester 4.10.0 is recommended by the sqlcollaborative on GitHub. I will see about grabbing that version and maybe my upgrade will work!

  • Ed Zerylnick says:

    Yes, your info that the version of Pester and PS Framework matters eventually led to upgrade Nirvana and finding that dbachecks had added 25 new checks since my last upgrade. That plus discovering that I needed to update the app.checkrepos DbcConfig value to point to the upgraded dbachecks version’s folder. Thank you for your part in solving this mystery.

  • Steven Naudet says:

    Hello Ed, I’m glad it could help. Thank you for reading.

Leave a Reply

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

Steven Naudet
Steven Naudet

Consultant