Skip to content

T-SQL Tuesday #94 – Automation Through PowerShell Checking the Status of AGs

T-SQL TuesdayThis month Rob Sewell (Twitter | Blog) is hosting T-SQL Tuesday and wants us to automate more with PowerShell or at least learn PowerShell. My favorite thing to automate using PowerShell is checking on the status of things on multiple servers.  For example, after patching your environment running a quick query to make sure the version number is the same.  In this example, we will use a cmdlet my coworker wrote in combination in my cmdlet to check the health of all the Availability Groups across our landscape or you could use it just check one.  After all I do consider myself to be an HA/DR nut.

I’ve blogged about my coworker’s Get-CmsHost cmdlet before but now he has and shared it on github so you can read more about here.

In my cmdlet I use the same code that used in the SSMS AG dashboard to check the status of my Availability Groups.

Different ways this cmdlet can be called are as follows to check on one server’s AG:

To check a list of servers in a text file:

To check all servers in an CMS Host:

To check servers that begin with a certain letters:

Here is the code for the cmdlet:

3 thoughts on “T-SQL Tuesday #94 – Automation Through PowerShell Checking the Status of AGs

  1. Garry Bargsley (@gbargsley) says:

    Can I ask a question. If I download your code. What is the best way to run it from the PowerShell command prompt? Do you import it? I tried calling it with .\Filename.ps1 and still cannot get it to work.

    • Tracy Boggiano says:

      So you can create a file with with function a the top, and the execution of the function at the bottom. You may also save the file in one the paths in your $env:PSModulePath variable and it will now be a function available on your machine. My recommendation though is you create a Module of your own that you accumulate functions that you commonly use into. I’ll see if I can find a good link that demonstrates how to do this.

      • Garry Bargsley (@gbargsley) says:

        Thank you for the info. Yes, anything you can find would be helpful. The more I do in PS the more I think I need to get it organized.


Leave a Comment