dbachecks is a new PowerShell module from the SQL Server Community! For more information, read introducing dbachecks .

If you don’t know dbachecks, we have released a good amount of blog posts that will help you: Announcing dbachecks – Configurable PowerShell Validation For Your SQL Instances by Rob Sewell introducing dbachecks - a new module from the dbatools team! by Chrissy LeMaire install dbachecks by Chrissy LeMaire dbachecks commands by Chrissy LeMaire dbachecks – Using Power BI dashboards to analyse results by Cláudio Silva My wrapper for dbachecks by Tony Wilhelm Checking backups with dbachecks by Jess Promfret dbachecks please! by Garry Bargsley dbachecks – Configuration Deep Dive by Rob Sewell Test Log Shipping with dbachecks Checking your backup strategy with dbachecks by Joshua Corrick Enterprise-level reporting with dbachecks by Jason Squires Adding your own checks to dbachecks by Shane O’Neill dbachecks - A different approach for an in-progress and incremental validation by Cláudio Silva

Other documentation: dbachecks readme dbachecks wiki (for developers)

I will share one of the ways I like to use dbachecks when I’m knocking down the problems in order to increase the green percentage and lower the red one!

Output files

How do you save the results? Do you save one file per instance (all tests included)? Using -Append? Alternatively, one per check/environment?

There is not a single way of doing this. Neither a “correct way”. Here you can find another different way grouping your results per application.

I will share the way I like to use it, when using the PowerBI dashboards to analyze the results, and explain the advantages I get from it.

Choosing a road

My personal choice is to have one file per check and environment. This means that if I’m running a check for SuspectPage I run for all instances/databases belonging to the development environment, I will end with a file named dbachecks_1_SuspectPage_DEV.json. Keeping the same line, I will get a filename dbachecks_1_SuspectPage_PRD.json if I run it for production.

$sqlInstances = "dev1", "dev2"

$checks = (Get-DbcCheck).UniqueTag
$checks.ForEach{

Invoke-DbcCheck -SqlInstance $sqlInstances -Checks $_ -PassThru -Show Fails | Update-DbcPowerBiDataSource -Environment "DEV" -Path "C:\windows\temp\dbachecks"

}

This will output:

quickerrefresh_11

Total number of files

“This will create a lot of files…”

Let’s do some math

Let’s imagine for a moment that we have to manage 3 different environments (DEV, QA, PRD): Currently, we have 80 checks if your approach is 1 file per environment you will end up with 3 files. The way I like to do it, I will end up with 240 files.

WOW! Big difference right?

Fear nothing

Yes, it is a big difference but that is no problem at all! The Power BI file will deal with this increase flawlessly as I have mentioned before on dbachecks – Using Power BI dashboards to analyse results blog post.

Advantages

The biggest advantage, for me, is the possibility I have to re-run a single test for a single environment and with it, only touch just one of the files. It’s an update of that file. By doing it, for the same destination folder, I will overwrite the existing file then I literally just need to go and hit “Refresh” button on PowerBI dashboards. This way it took just the time of that test and not all of them. Quick and easily, I’m able to confirm that the fix I have run actually worked and my red values are lower! :D

Real scenario

  • You run, overnight, all your tests.
  • In the morning you open the Power BI dashboard and hit “Refresh”
  • You look to your red values.
  • You pick one (for this examples purpose let’s say “Auto-Close”)
  • You run a query to fix all databases with the wrong value
  • Re-run just this test just for one environment (run multiple times for various environment)
  • Go to your Power BI and hit “Refresh” again.
  • Repeat from point 3.

The point 6 is where you will save huge amounts of time because if you have just one file for all tests for one environment, you would need to rerun ALL the tests in order to refresh your environment.

Hope this helps!

Thanks for reading!