Open source powershell module maintained by SQL community contributing to it. dbachecks is a framework created by and for SQL Server pros who need to validate their environments. Basically, we all share similar checklists and mostly just the server names and RPO/RTO/etc change.
This open source module allows us to crowdsource our checklists using Pester tests. Such checks include:
- Backups are being performed
- Identity columns are not about to max out
- Servers have access to backup paths
- Database integrity checks are being performed and corruption does not exist
- Disk space is not about to run out
- All enabled jobs have succeeded
Prerequisites
Client requirements
- PowerShell 4+ is required.
- Automatic installation of the dependent modules will only be provided via the PowerShell Gallery.
When you install from the Gallery, it’ll auto-install:
- dbatools
- Pester
- PSFramework
When you import, it’ll auto-import
- dbatools
- Pester
- PSFramework
If you have already installed the module and you update it, you may be required to update the Pester or the PSFramework modules before it will import. If you see a message like
Then you need to
Install-Module Pester -SkipPublisherCheck -Force
Import-Module Pester -Force
SQL requirements
dbachecks uses dbatools for most of it’s data gathering so it supports SQL Versions from SQL 2000 to SQL vNext including SQL running on Linux. (dbachecks will not install on PowerShell Core yet so can not be run on a Linux client) Obviously some of the Services adn disk space checks will not work against instances running on Linux as they are usin gWindows API calls.
Getting started
Checks are performed using Invoke-DbcCheck which is basically a wrapper for Invoke-Pester. This means that supported Invoke-Pester parameters work against Invoke-DbcCheck.
In this module, a “Check” is synonymous with a “Tag” in Pester. So you can Invoke-DbcCheck and specify a Check that you want to run. You can see a list of the available Checks with Get-DbcCheck.
Once you’ve decided on the Check(s) you want to run, it’s time to ensure you have a list of servers to run the checks against.
Making server lists
Similar to the dbatools module, dbachecks accepts -SqlInstance and -ComputerName parameters.
Invoke-DbcCheck -SqlInstance $servers -Checks SuspectPage, LastBackup
If you have a simplified (single) environment, however, you can set a permanent list of servers. “Servers” include both SQL Server instances and Windows servers. Checks that access Windows Server (e.g. disk space checks) will utilize -ComputerName parameter. A pure SQL Server command(s) (such as the backup check) utilizes the -SqlInstance parameter.
# Set the servers you'll be working with
Set-DbcConfig -Name app.sqlinstance -Value sql2016, sql2017, sql2008, sql2008\express
Set-DbcConfig -Name app.computername -Value sql2016, sql2017, sql2008
# Look at the current configs
Get-DbcConfig
# Invoke a few tests
Invoke-DbcCheck -Checks SuspectPage, LastBackup
What it looks like
Other ways to execute checks against specific servers
Additional Invoke-DbcCheck examples:
Invoke-DbcCheck -Check Backup -SqlInstance sql2016
Invoke-DbcCheck -Check RecoveryModel -SqlInstance sql2017, sqlcluster
$sqlinstance = Get-DbaRegisteredServer -SqlInstance sql2017 -Group Express
Invoke-DbcCheck -Check Backup -SqlInstance $sqlinstance
Invoke-DbcCheck -Check Storage -ComputerName server1, server2
Check and ExcludeCheck
We tag each of our Checks using singular descriptions such as Backup, Database or Storage. You can see all the Pester related Tags using Get-DbcTagCollection or Get-DbcCheck.
Each Check generally has a few Tags but at least one Tag is unique. This allows us to essentially name a Check and using these Tags, you can either include (-Check) or Exclude (-ExcludeCheck) in your results. The Exclude will always take precedence.
For example, the Database Tag runs a number of Checks including Backup Checks. The command below will run all Database Checks except for the Backup Checks.
Invoke-DbcCheck -Check Database -ExcludeCheck Backup -SqlInstance sql2016 -SqlCredential (Get-Credential sqladmin)
Reporting on the data
Since this is just PowerShell and Pester, results can be exported then easily converted to pretty reports. We’ve provided two options: Power BI and SMTP mail.
# Run checks and export its JSON
Invoke-DbcCheck -SqlInstance sql2017 -Checks SuspectPage, LastBackup -Show Summary -PassThru | Update-DbcPowerBiDataSource
# Launch Power BI then hit refresh
Start-DbcPowerBi
Sending mail
Includes a command to make emailing the results easier!
Invoke-DbcCheck -SqlInstance sql2017 -Checks SuspectPage, LastBackup -OutputFormat NUnitXml -PassThru |
Send-DbcMailMessage -To clemaire@dbatools.io -From nobody@dbachecks.io -SmtpServer smtp.ad.local
I’d like to run my checks in SQL Server Agent
Great idea! Remember that this module requires PowerShell version 4.0, which doesn’t always mesh with SQL Server’s PowerShell Job Step. To run dbachecks, we recommend you use CmdExec. You can read more at dbatools.io/agent.
If you do choose to use the PowerShell step, don’t forget to Set-Location somewhere outside of SQLSERVER:, otherwise, you’ll get errors similar to this
I don’t have access to the PowerShell Gallery, how can I download this?
This module has a number of dependencies which makes creating a GitHub-centric installer a bit of a pain. We suggest you use a machine with PowerShellGet installed and Save all the modules you need:
Save-Module -Name dbachecks, dbatools, PSFramework, Pester -Path C:\temp
Then move them to somewhere in your $env:PSModulePath, perhaps Documents\WindowsPowerShell\Modules or C:\Program Files\WindowsPowerShell\Modules.
Database Blog 