Pétur Grétarsson, here. Long time dbatools fan, first time blogger. I really love the new Invoke-DbaSqlCmd command and wanted to share how I use it.
I have well over 100 Availability Groups to look after in different domains so it can be a bit difficult to check things when needed. We have opened required ports allowing connections from one SQL management server to those for monitoring purposes including using Idera SQL Inventory Manager for basic health checks.
Having said that, there’s some additional monitoring I like to be able to do such as checking the health status of Availability Groups to see if there are any potential resource issues.
Invoke-DbaSqlCmd makes monitoring easy
I’ve used Invoke-DbaSqlCmd for so many things. Here’s one example: how I used the new function in my latest Availability Group check.
- I created and stored a SQL Server Credential on the SQL management server using Credential Manager (found from Control Panel) using the account that executes the PowerShell scripts in schedule.
- I scheduled the script below to run via Visual Studio Team Services online (or the Agent on my SQL management server)
- I executed a procedure from PowerShell script that for example checks if any Availability Group is unhealthy and if so then sends email to our support team
- Gets a list of servers using Get-DbaRegisteredServer
- Pipes that list to Invoke-DbaSqlCmd which executes a script against each of the servers
- Converts the results of the script to a datatable
- Writes the converted output to a table in my management database
And here is the simple T-SQL script I use in the above example. It is now so nice to be able to see version, max memory, PLE, logical CPU count, tempdb files, CPU signal wait, listener name, IP Address, failover mode, synchronization health etc. for all of the availability groups from one table which gets updated frequently values.
We have thousands of databases in these Availability Groups so it is important to ensure that they are healthy. We have monitoring systems for the most important ones but you can imagine the cost. Therefore, it is nice to be able to build some basic checks like these. Running customized scripts and using the Get-DbaRegisteredServer is just fantastic 😊.
Additional uses for dbatools
I also use other dbatools commands for getting a daily summary email report for all our SQL Agent jobs which are either failing or not having failure notifications configured from all our environments. We have many people adding SQL Agent Jobs and sometimes the notifications get forgotten so this allows me to catch that immediately and get daily status report of all our SQL Agent failed jobs.
I scheduled the script below to run via Visual Studio Team Services online (or the Agent on my SQL management server). This script runs just before office hours, so we can have the most recent status in the morning.
Then I have a job that runs soon after the above check. This job is also PowerShell script that just executes a procedure in the database management repository and if the procedure has result set then it gets sent to the database team.
The procedure is just very basic and simple check from the table that is created in the above check.
Alternatively, I could use some dbatools commands and Send-MailMessage but I appreciated the fact that I could so easily integrate with my existing SQL solutions.
I also use the command for many of our functions which run against all our +300 instances and +12.000 databases and store the result in central management database. Other validations I use dbatools for: file allocation unit size is incorrect, proper power plan is being used, SPN is configured, transaction log VLF count does not exceed threshold, SQL orphaned files do not exist, invalid SQL logins do not exist and much more. All those checks are done with dbatools and visible in one central database repository.
Thanks for reading!