dbachecks: how to add your own checks

As a DBA I used more and more the dbachecks at customer place to validate environments. It became part of our DBA toolkit at the same place than its famous brother the dbatools.
My colleague Steven Naudet did a blog-post some weeks ago about it and today I will show you how is it possible to add your own checks if you cannot find the one you need in the quite long list available for the moment and which is continuously growing.

Before to create your own checks you can read this wiki which is dedicated to dbachecks development and available on the dbachecks main page. It will give you some advice and standard development guidelines.

To create my new checks I will first create a new file which will contain those checks, lets name it SelfChecks.Tests.ps1.
I would like to add two checks:

• The first one will check the health of my Availability Groups which have to be synchronized and healthy.
  I will tag this check DBinAG.

• The second one will check that all the user databases of my instance are part of an Availability Group with exception of some specifics ones like our dbi_tools database for example.
  I will tag this one DBHealthyInAG.

The PowerShell script will be the following:

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

$DBinAG = "IF (SERVERPROPERTY ('IsHadrEnabled') = 1)
BEGIN
	SELECT  name, state_desc
	FROM    sys.databases
	WHERE    name    NOT IN
		(   SELECT  DISTINCT
					drcs.database_name              AS name
			FROM                master.sys.availability_groups                      AS ag
				LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states        AS ags
							ON    ag.group_id       = ags.group_id
				INNER JOIN      master.sys.availability_replicas                    AS ar
							ON   ag.group_id        = ar.group_id
				INNER JOIN      master.sys.dm_hadr_availability_replica_states      AS ars
							ON     ar.replica_id    = ars.replica_id
							AND    ars.is_local     = 1
				INNER JOIN      master.sys.dm_hadr_database_replica_cluster_states  AS drcs
							ON     ars.replica_id   = drcs.replica_id
				LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states          AS dbrs
							ON      drcs.replica_id         = dbrs.replica_id
							AND     drcs.group_database_id  = dbrs.group_database_id
			WHERE   ISNULL(ars.role, 3)                 = 1
			OR      ISNULL(drcs.is_database_joined, 0)  = 1
		)
	--AND     dbid  
	AND     name    NOT IN  ('master'
							,'tempdb'
							,'model'
							,'msdb'
							-- add below your exceptions
							,'dbi_tools'
							)
	AND     state_desc  != 'OFFLINE'
	ORDER BY
			name    ASC
END";

(Get-Instance).ForEach{

    Describe –Name "All databases should be in an AG" -Tags DBinAG, $filename {
        Context –Name "All databases should be in an AG on $psitem" {
            It "should have 0 database outide of an AG" {
                Invoke-DbaQuery -SqlInstance $psitem -Query $DBinAG |  
                Measure-Object |
                    Select-Object –ExpandProperty Count |
                    Should -Be 0 –Because "All databases should be in an AG"
            }
        }
    }
}

$DBHealthyInAG = "IF (SERVERPROPERTY ('IsHadrEnabled') = 1)
BEGIN
SELECT  ag.name                             AS [AGName]
        ,adc.[database_name]                AS [DBName]
        ,ar.replica_server_name             AS [ReplicaServerName]
        ,drs.synchronization_state_desc     AS [SynchronizationState]
        ,drs.synchronization_health_desc    AS [SynchronizationHealth]
        ,drs.is_local                       AS [IsLocal]
        ,ar.availability_mode_desc          AS [AvailabilityMode]
FROM    sys.dm_hadr_database_replica_states                 AS drs WITH (NOLOCK)
        INNER JOIN sys.availability_databases_cluster       AS adc WITH (NOLOCK)
            ON  drs.group_id            = adc.group_id
            AND drs.group_database_id   = adc.group_database_id
        INNER JOIN sys.availability_groups                  AS ag WITH (NOLOCK)
            ON  ag.group_id             = drs.group_id
        INNER JOIN sys.availability_replicas                AS ar WITH (NOLOCK)
            ON  drs.group_id            = ar.group_id
            AND drs.replica_id          = ar.replica_id
WHERE   1=1
AND NOT (   drs.synchronization_state_desc  = 'SYNCHRONIZED'
        AND drs.synchronization_health_desc = 'HEALTHY'
        )
ORDER BY
        ag.name
        ,ar.replica_server_name
        ,adc.[database_name]        OPTION (RECOMPILE)
END";

(Get-Instance).ForEach{

    Describe –Name "All databases should be synchronized and healthy in an AG" –Tags DBHealthyInAG, $filename {
        Context –Name "All databases should be synchronized and healthy in an AG on $psitem" {
            It "should have 0 unsynchronized and unhealthy databases" {
                Invoke-DbaQuery -SqlInstance $psitem -Query $DBHealthyInAG |  
                Measure-Object |
                    Select-Object –ExpandProperty Count |
                    Should -Be 0 –Because "All databases need to be synchronized and healthy"
            }
        }
    }
}

When we install the dbachecks, Pester tests & checks are stored in a particular folder and to find where we can run the cmdlet:

Get-DbcConfig -Name app.checkrepos

Result is:

To add my checks to the list of the available checks I need to add the location of my file to this configuration value.
I can do it with the cmdlet:

Set-DbcConfig -Name app.checkrepos -Value 'C:\dbachecks\OwnChecks\' -Append

Now, my folder has been added to the check repo:

I’m able to call my two new tags as I would do for “existing” ones and the result is:

All my databases are part of an Always On Availability Group and my AG are healthy & synchronized 😉

I was able to add my two new checks and if I need another ones, I can add them to my file.

Quite easy no?