It’s been 45 days since we released dbachecks

Announcing dbachecks – Configurable PowerShell Validation For Your SQL Instances https://t.co/2dmUdKtgTQ pic.twitter.com/N8W01KaKo9

— Rob Sewell (@sqldbawithbeard) February 22, 2018

Since then there have been 25 releases to the PowerShell Gallery!! Today release 1.1.119 was released There have been over 2000 downloads of the module already.

In the beginning we had 80 checks and 108 configuration items, today we have 84 checks and 125 configuration items!

If you have already installed dbachecks it is important to make sure that you update regularly. You can do this by running

Update-Module dbachecks

If you want to try dbachecks, you can install it from the PowerShell Gallery by running

Install-Module dbachecks # -Scope CurrentUser # if not running as admin

You can read more about installation and read a number of blog posts about using different parts of dbachecks at this link https://dbatools.io/installing-dbachecks/

HADR Tests

Today we updated the HADR tests to add the capability to test multiple availability groups and fix a couple of bugs

Once you have installed dbachecks you will need to set some configuration so that you can perform the tests. You can see all of the configuration items and their values using

Get-DbcConfig | Out-GridView

You can set the values with the Set-DbcConfig command. It has intellisense to make things easier To set the values for the HADR tests

Set-DbcConfig -Name app.cluster -Value sql1
Set-DbcConfig -Name app.computername -Value sql0,sql1
Set-DbcConfig -Name app.sqlinstance -Value sql0,sql1
Set-DbcConfig -Name domain.name -Value TheBeard.Local
Set-DbcConfig -Name skip.hadr.listener.pingcheck -Value $true

• app.cluster requires one of the nodes of the cluster.
• app.computername requires the windows computer names of the machines to run operating system checks against
• app.sqlinstance requires the instance names of the SQL instances that you want to run SQL checks against (These are default instances but it will accept SERVER\INSTANCE)
• domain.name requires the domain name the machines are part of
• skip.hadr.listener.pingcheck is a boolean value which defines whether to skip the listener ping check or not. As this is in Azure I am skipping the check by setting the value to $true
• policy.hadr.tcpport is set to default to 1433 but you can also set this configuration if your SQL is using a different port

Get-DbcConfig -Name skip*

Invoke-DbcCheck -Check HADR

• Each node on the cluster should be up
• Each resource on the cluster should be online
• Each SQL instance should be enabled for Always On
• Connection check for the listener and each node
  • Should be pingable (unless skip.hadr.listener.pingcheck is set to true)
  • Should be able to run SQL commands
  • Should be the correct domain name
  • Should be using the correct tcpport
• Each replica should not be in unknown state
• Each synchronous replica should be synchronised
• Each asynchronous replica should be synchonising
• Each database should be synchronised (or synchronising) on each replica
• Each database should be failover ready on each replica
• Each database should be joined to the availability group on each replica
• Each database should not be suspended on each replica
• Each node should have the AlwaysOn_Health extended event
• Each node should have the AlwaysOn_Health extended event running
• Each node should have the AlwaysOn_Health extended event set to auto start

(Apologies folk over the pond, I use the Queens English )

This is good for us to be able to run this check at the command line but we can do more.

We can export the results and display them with PowerBi. Note we need to add -PassThru so that the results go through the pipeline and that I used -Show Fails so that only the titles of the Describe and Context blocks and any failing tests are displayed to the screen

Invoke-DbcCheck -Check HADR -Show Fails -PassThru | Update-DbcPowerBiDataSource -Environment HADR-Test
Start-DbcPowerBi

This will create a file at C:\Windows\Temp\dbachecks and open the PowerBi report. You will need to refresh the data in the report and then you will see

Excellent, everything passed

Saving Configuration for reuse

We can save our configuration using Export-DbcConfig which will export the configuration to a json file

Export-DbcConfig -Path Git:\PesterTests\MyHADRTestsForProd.json

so that we can run this particular set of tests with this comfiguration by importing the configuration using Import-DbcConfig

Import-DbcConfig -Path -Path Git:\PesterTests\MyHADRTestsForProd.json
Invoke-DbcCheck -Check HADR

In this way you can set up different check configurations for different use cases. This also enables you to make use of the checks in your CI/CD process. For example, I have a GitHub repository for creating a domain, a cluster and a SQL 2017 availability group using VSTS. I have saved a dbachecks configuration to my repository and as part of my build I can import that configuration, run the checks and output them to XML for consumption by the publish test results task of VSTS

After copying the configuration to the machine, I run

Import-Dbcconfig -Path C:\Windows\Temp\FirstBuild.json
Invoke-DbcCheck-AllChecks -OutputFile PesterTestResultsdbachecks.xml -OutputFormat NUnitXml

in my build step and then use the publish test results task and VSTS does the rest