dbachecks - Automate checks using SQL Agent

It is that time again to rave about dbachecks! This week I put in to action automated checks that run via SQL Agent for different scenarios.

Issue

In my environment I have 300+ servers from development to production. I do not just want to run all checks on all servers as that would take forever and be hard to interpret the output. So my plan is to break down the checks in to functional areas and split server lists out by different environments. So my first go at this is going to be to check all of my clusters that run Availability Groups and also to run backup checks against all my production servers.

Solution

Prep Work – Module Configuration

So to get started I prepared my server to run dbachecks. Unfortunately, our servers do not have access to the internet by default. So luckily dbachecks has a command to help with this.

Save-DbcRequiredModules -Path C:\1-DbaChecks

1	Save-DbcRequiredModules -Path C:\1-DbaChecks

What this does is takes the dbatools, psframework, pester and dbachecks modules and saves all the necessary files needed to run in to the output path you specify. Now I can take those folders that were created and copy over to my server in the \Program Files\WindowsPowerShell\Modules folder.

dbacheck – Set Configs

My process for running dbatools or dbachecks for that matter with SQL Agent has been to create a ps1 file with all of my code in it and just call that ps1 file from SQL Agent.

I have been playing with a couple different scenarios. Depending on your skill level or environment you can choose one over the other.

SQL Agent runs the job under a specific account context. Whether it is the service account running SQL Agent or a Proxy account you have setup and assigned to the job. Each one will have its own set of registry keys and thus settings that dbachecks will read. So you can either assign your configuration values in each of your ps1 scripts like this snippet.

# Set Config Values
Unregister-PSFConfig -Module dbachecks -Name app.checkrepos
Set-DbcConfig -Name app.cluster CLUSTER1, CLUSTER2, CLUSTER3, CLUSTER4, CLUSTER5, CLUSTER6, CLUSTER7, CLUSTER8
Set-DbcConfig -Name app.computername SERVER1, SERVER2, SERVER3, SERVER4, SERVER5, SERVER6, SERVER7, SERVER8
Set-DbcConfig -Name app.sqlinstance SERVER1, SERVER2, SERVER3, SERVER4, SERVER5, SERVER6, SERVER7, SERVER8
Set-DbcConfig -Name domain.name domain-prod.com

# Set Config Values

Unregister-PSFConfig -Module dbachecks -Name app.checkrepos

Set-DbcConfig -Name app.cluster CLUSTER1, CLUSTER2, CLUSTER3, CLUSTER4, CLUSTER5, CLUSTER6, CLUSTER7, CLUSTER8

Set-DbcConfig -Name app.computername SERVER1, SERVER2, SERVER3, SERVER4, SERVER5, SERVER6, SERVER7, SERVER8

Set-DbcConfig -Name app.sqlinstance SERVER1, SERVER2, SERVER3, SERVER4, SERVER5, SERVER6, SERVER7, SERVER8

Set-DbcConfig -Name domain.name domain-prod.com

And that way your configs are incorporated in to your ps1 file for ease of access and lower complexity.

The second option is to load all the settings to the correct values you want in your PS environment of choice. Then use the Export-DbcConfig command to extract all the settings changed to a json file. Then in your ps1 file you can make a call like this to load the json file to the session running under SQL Agent.

Import-DbcConfig -Path "\\networkshare\data\DBA\dbachecks\Configs\CustomConfig.json" -Temporary

1	Import-DbcConfig -Path "\\networkshare\data\DBA\dbachecks\Configs\CustomConfig.json" -Temporary

This will execute the check with the settings stored in the json file and imported in to the registry during run time.

dbachecks – Invoking

Now that we have all the prerequisite configurations out of the way we are ready to run some checks.

The magic all happens right here.

# Run Check
Invoke-DbcCheck -Check HADR -ExcludeDatabase ReportServer, ReportServerTempDB -PassThru | Update-DbcPowerBiDataSource -Environment PRD-HADR-Hourly -Path "\\networkshare\data\DBA\dbachecks\Output\PROD"

1 2	# Run Check Invoke-DbcCheck -Check HADR -ExcludeDatabase ReportServer, ReportServerTempDB -PassThru \| Update-DbcPowerBiDataSource -Environment PRD-HADR-Hourly -Path "\\networkshare\data\DBA\dbachecks\Output\PROD"

This check is going to run all the checks for the group HADR, and then exclude databases ReportServer and ReportServerTempDB as we keep those offline in our environment.

Then we are going to pipe the output from the test over to the Update-DbcPowerBiDataSource command. We are setting a custom -environment for each check and then send the json output to a network share.

Now I can move back over to my laptop or any of my team members can run this command.

Start-DbcPowerBi -DataSource "\\networkshare\data\DBA\dbachecks\Output\PROD"

1	Start-DbcPowerBi -DataSource "\\networkshare\data\DBA\dbachecks\Output\PROD"

Once the PowerBi dashboard loads and the user hits refresh, they will see the latest and greatest output from the checks.

SQL Agent – Job Configuration

Oh yeah, the SQL Agent piece.

Create a new SQL Agent job
Setup a new Step Name that is a Operating System(CmdExec)
Put code similar to mine in the step command
Your Run As account my vary depending on your environment

From this particular admin server I can connect to all SQL servers in our environment. You may have to setup a proxy account to gain access from your source server. See this great article that walks you through the different options.

Special Note

It is a good idea to check your ps1 and json config files into a reliable source control system for safe keeping.

Conclusion

I urge you to get dbachecks in to your environment. The ease and flexibility of setup is great. And you can configure your checks in importance to you.

So run out and get the module loaded.

dbachecks – Automate checks using SQL Agent4 min read