Menu
Menu
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:
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.
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/introducing-dbachecks/
© 2023 Kohera
Crafted by 
© 2022 Kohera
Crafted by 
