Validate your SQL environment the easy way

Every DBA probably knows and should even be using DBAtools stuff. Lately Chrissy Lemaire and her team also added DBAchecks, a validation tool, to it. A separate powershell module , based mainly on pester checks.

And what is even more to be liked : there is also a wonderful PowerBI dashboard included ! All for free

Now , what does this module actually do?

It’s a list of checks on what is supposed to be best practices in our little SQL world in order to get a healthy SQL Server Environment. Stuff you should be asking yourself like :

Is Max Memory setup correctly on all of my servers?  Do we have the correct TempDB configurations?  How about DAC, is it enabled on all servers in the event of an emergency?

Well there are checks for all of these items, +80 or so more standard built in checks. Not only are there checks, but these are repeatable checks that you can run daily to make sure your environment is healthy or to show you what changes can be done after ,for example, the introduction of a new SQL server, database or whatever.

How does this thing work?

There are three pre-requisites that are required to load the module. Those are Pester 4.3.1, PS Framework 0.9.10.23, and currently as of this post dbatools 0.9.207.

Run directly from the command line

As simple as ‘Invoke-DbcCheck -SqlInstance sqlprod01 -Checks SuspectPage, LastBackup’

Schedule checks

Command line execution is good in a pinch, but ongoing checks are the ultimate goal. In order do this, you can do the following:

• Set your desired configuration
  Configs can be set for specific environments like Production, Test or Development or for an application, like SharePoint or a custom-built app
• Export your configuration
  Export your environment or application configuration so that it can be easily imported by your scheduled task
• Schedule checks using Task Scheduler or SQL Server Agent
• Get notified via email or load up in Power BI

How to install

PowerShell

While only v4 is required, it is recommended to use PowerShell v5.1 which runs on a variety of Windows Platforms including Windows 7 SP1. PowerShell v5.1 is faster and more secure since it has superior logging capabilities. (Keep in mind that you only need a higher version of PowerShell on the workstation or server where you’ll be performing your checks !)

PowerShell is installed as part of the “Windows Management Framework” or WMF.

Install and Configure WMF 5.1

Access to PowerShell Gallery

Online

Access to the PowerShell Gallery is required. Installing dbachecks will automatically install the required modules: dbatools, Pester and PSFramework.

Install-Module -Name dbachecks

For a step-by-step tutorial, check out :  walk-thru: installing modules from the powershell gallery.

Offline

If you are in a locked down environment, consider using an online workstation to save the required modules then copy them to the PowerShell paths of your final destination.

Save-Module -Name dbachecks -Path C:\temp

Additional Modules

Required modules are automatically installed when you execute Install-Module dbachecks.

How to use

There are about 80 Checks (view them by ‘Get-DbcCheck’ )

More the a hundred configurations ( Get-DbcConfig )

Set configuration items at run-time or for your session and enables you to export and import them so you can create different configs for different use cases

Running A Check (using Powershell or Powershell ISE and the dbachecks module)

You can quickly run a single check by calling ‘ Invoke-DbcCheck ’ .

e.g. : Invoke-DbcCheck -SqlInstance localhost -Check FailedJob

Setting a Configuration

Example : To save you from having to specify the instance you want to run tests against – set the app.sqlinstance config to the instances you want to check

Set-DbcConfig -Name app.sqlinstance -Value localhost, ‘localhost\PROD1’

The configurations are stored in the registry but you can export them and then import them for re-use easily.

Get the results into PowerBi

Results are written into Json files on default location  c:\windows\tep\dbachecks

Run the powerbi dashboard by running ‘ Start-DbcPowerBi ‘

Usage Example

While you can create different configuration files for several validation cases and call them as you invoke a dbacheck (eg : Invoke-DbcCheck -AllChecks -Show Fails -PassThru |Update-DbcPowerBiDataSource -Environment Production ), I preferred to use a powershell wrap for this blog example, based on the nice work of Tony Wilhelm.

So let’s check it out :

# Generic variable initialization: 
$ExcludeServer = @()
$ExcludeGroup = @()
$timeData = @()

$PowerBiDataPath = 'c:\windows\temp\dbachecks\data'
$cmsServer = "mycms" 

Our wrapper will use the Central Management server to gather all SQL instances, except for those which are excluded in the next step. Dbachecks will create json output files that are written to the path as stated in the variable above.

# Defining the exclusions for the tests. We can exclude complete groups in CMS
Class Exclusion
{
    [ValidateNotNullOrEmpty()][String]$Name
    [string[]]$Tags
}

$ExcludeGroup += [Exclusion]@{name="Dev"; tags=@("Backup")} 
# eg Exclude backup checks on the Dev group
$ExcludeGroup += [Exclusion]@{name="SQL Express 2005"} #Exclude entire group
"} 

You can exclude certain validation checks on a cms group or exclude a complete group entirely from all checks.

# Getting the list of groups to test from the CMS server 
$groups = ((Get-DbaRegisteredServersStore -SqlInstance $cmsServer).DatabaseEngineServerGroup.ServerGroups).name | 
    Where-Object {$PSItem -notin ($ExcludeGroup | where Tags -eq $null).name} |
    Sort-Object -Unique   

This step is rather self-explainatory, no?

# Define Configuration Values
Set-DbcConfig -Name policy.backup.fullmaxdays 1
Set-DbcConfig -Name policy.backup.logmaxminutes 60
Set-DbcConfig -Name policy.certificateexpiration.excludedb 'master','model','msdb','tempdb','mngdb'
Set-DbcConfig -Name policy.connection.authscheme 'NTLM'
Set-DbcConfig -Name policy.database.filegrowthfreespacethreshold 15 

After we ‘ve set our targets, we can configure a lot of settings on various checks. What is a good base that needs to be met when we run checks to validate our sql instances?

# Define the tags that you want to test SQL Instances for 

$tagsInstance = 'AgentServiceAccount','FailedJob','AgentAlert','SuspectPage',
    'LastGoodCheckDb','IdentityUsage','RecoveryModel','DuplicateIndex','UnusedIndex','DisabledIndex' 
# Define the tags that you want to test SQL Hostserver for 

$tagsserver = 'SPN','DiskCapacity','PowerPlan' 

Most tests will be running against the sql instance, a smaller part against the hosting servers. So first we define what checks we want to run by listing the referential tags. You can get a complete overview of checks and their tags by hitting the command : ‘Get-DbcTagCollection’ .
Now for the part in the script that actually will do the testing

# Tests on the instances
$groups |    
    ForEach-Object {
        $groupName = $PSItem

   

     # Get the list of SQL instances from the CMS sever, except those that are excluded
        $sqlinstances = Get-DbaRegisteredServer -SqlInstance $cmsServer -Group $groupName | 
            where name -NotIn ($ExcludeServer | where Tag -eq $null ).name    
 
        $tagsinstance | ForEach-Object {
            $tag = $PSItem

            $obj =  [pscustomobject]@{            
                ServerGroup = $groupName
                NumServers = $sqlinstances.Count
                Tag = $tag
                InvokeStartTime = Get-Date
                InvokeCompleteTime = $null
                WriteResultsTime = $null
                TestExecution = $null
                InvokeDuration = $null
                ResultsDuration = $null
                PassedCount       = $null
                FailedCount       = $null
                SkippedCount      = $null
                PendingCount      = $null
                InconclusiveCount = $null               
            }
             
            $results = Invoke-DbcCheck -SqlInstance $sqlinstances -tags $tag -PassThru -Show Fails
            #$results = Invoke-DbcCheck -ComputerName $sqlinstances.servername -tags $tag -PassThru -Show Fails
            $obj.TestExecution     = $results.time
            $obj.PassedCount       = $results.PassedCount      
            $obj.FailedCount       = $results.FailedCount      
            $obj.SkippedCount      = $results.SkippedCount     
            $obj.PendingCount      = $results.PendingCount     
            $obj.InconclusiveCount = $results.InconclusiveCount

            $obj.InvokeCompleteTime = Get-Date

            $results | Update-DbcPowerBiDataSource -Environment $groupName -Append -Path $PowerBiDataPath
            $obj.WriteResultsTime = Get-Date      
 
            $obj.InvokeDuration = New-TimeSpan -Start $obj.InvokeStartTime -End $obj.InvokeCompleteTime
            $obj.ResultsDuration = New-TimeSpan -Start $obj.InvokeCompleteTime -End $obj.WriteResultsTime 
 
            $timeData += $obj       
        }                 
}  

# Tests on the hostservers
$groups |    
    ForEach-Object {
        $groupName = $PSItem  

        # Get the list of SQL instances from the CMS sever, except those that are excluded
        $sqlinstances = Get-DbaRegisteredServer -SqlInstance $cmsServer -Group $groupName | 
            where name -NotIn ($ExcludeServer | where Tag -eq $null ).name
     if ($SplitedVar = $sqlinstances.Name | ConvertFrom-String -Delimiter "\\" -PropertyNames "ServerName", "InstanceName" = $null) 
     {$instancename = $sqlinstances.Name+'\'}
     else {$instancename = $sqlinstances.Name}
        $SplitedVar = $instancename | ConvertFrom-String -Delimiter "\\" -PropertyNames "ServerName", "InstanceName"
        $computername = $SplitedVar.ServerName
        $tagsserver | ForEach-Object {
            $tag = $PSItem

      

      $obj =  [pscustomobject]@{            
                ServerGroup = $groupName
                NumServers = $sqlinstances.Count
                Tag = $tag
                InvokeStartTime = Get-Date
                InvokeCompleteTime = $null
                WriteResultsTime = $null
                TestExecution = $null
                InvokeDuration = $null
                ResultsDuration = $null
                PassedCount       = $null
                FailedCount       = $null
                SkippedCount      = $null
                PendingCount      = $null
                InconclusiveCount = $null               
            }
             
           $results = Invoke-DbcCheck -computername $computername -tags $tag -PassThru -Show Fails
            $obj.TestExecution     = $results.time
            $obj.PassedCount       = $results.PassedCount      
            $obj.FailedCount       = $results.FailedCount      
            $obj.SkippedCount      = $results.SkippedCount     
            $obj.PendingCount      = $results.PendingCount     
            $obj.InconclusiveCount = $results.InconclusiveCount

            $obj.InvokeCompleteTime = Get-Date

            $results | Update-DbcPowerBiDataSource -Environment $groupName -Append -Path $PowerBiDataPath
            $obj.WriteResultsTime = Get-Date      
 
            $obj.InvokeDuration = New-TimeSpan -Start $obj.InvokeStartTime -End $obj.InvokeCompleteTime
            $obj.ResultsDuration = New-TimeSpan -Start $obj.InvokeCompleteTime -End $obj.WriteResultsTime 
 
            $timeData += $obj       
        }                 
}  

For the second part I added some crappy lines, ‘cause we can get sql instances from our cms, but not the host servernames. But we can derive these from the instance resultset. Okay, it’s not ideal, but hey – it’s getting the job done for now.

Let’s check the resultset in our dashboard.
Run the command ‘Start-DbcPowerBi’ and first of all hit the refresh data button in PowerBI.

What a great dashboard, no? We can see our groups from CMS server in ‘Environment’ , individual instances and use these to zoom in on the resultsets.

Within the test results, we have a nice success/failure rate on tested baselines/best practices.

The main window is showing us where possible issues can be found and what situations may need the DBA’s attention. For sure when you installed new sql servers this can point you to stuff you forgot about or still have to configure. In a new environment you can at a glance get yourself an idea of the sql server health situation and where quick wins are hiding.

Just plan the validation script to run at night from a sql server job and you’re off to go !

Useful links :

https://dbatools.io/

https://dbatools.io/introducing-dbachecks/