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

This 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:

Get-AvailabiliytGroupStatus -ServerInstanceList "Servername"

To check a list of servers in a text file:

Get-AvailabiliytGroupStatus -ServerInstanceList "c:\temp\servers\.txt"

To check all servers in an CMS Host:

Get-AvailabiliytGroupStatus -ServerInstanceList ""

To check servers that begin with a certain letters:

Get-AvailabiliytGroupStatus -ServerInstanceList "Dev"

Here is the code for the cmdlet:

Function Get-AvailabilityGroupStatus
{
<#
		.SYNOPSIS
			Get the status of the Availability Groups on the servers.

		.DESCRIPTION
			Displays the status for availabiliyt groups on the servers in a grid. 

		.PARAMETER ServerSearchPattern
			The Search Pattern to be used for server names for the call against Get-CMSHosts.

		.PARAMETER ServerInstanceList
			The Instanace List to be used for server names for the call to Get-CMSHosts.

		.NOTES
			Tags: AvailabilityGroups
			Original Author: Tracy Boggiano (@TracyBoggiano), tracyboggiano.com
			License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0

		.EXAMPLE 
			Get-AvailabiliytGroupStatus -ServerSearchPattern "D2"

			Gets the status Availabiliy Groups on all servers where their name starts with D2.
			
		.EXAMPLE
			Get-AvailabiliytGroupStatus -ServerInstanceList "c:\temp\servers.txt"

			Gets the status Availabiliy Groups on all servers where their name in teh specified text file..
#>
    [CmdletBinding(DefaultParameterSetName = "Default")]
    Param
    (
    # The name of the server instance that hosts the availability group
    [Alias("ServerSearchPattern")]
    [Parameter(Mandatory = $true)]
    [string] $ServerInstanceList
    )

    begin {
        If ($ServerSearchPattern -eq $null) {
            $ServerSearchPattern = $ServerInstanceList
        }
        If ($ServerSearchPattern -ne $null) {
            Get-CmsHosts -SqlInstance $ServerSearchPattern | % { New-PSSession -ComputerName $_ | out-null} 
        }
        Else {
            Write-Host "Error you need specify a list of servers to query" -ForegroundColor Red
            break
        }
    }

    process {
        $sessions = Get-PSSession
 
        $scriptblock = {
        $query = @"
            IF SERVERPROPERTY(N'IsHadrEnabled') = 1
            BEGIN
	            DECLARE @cluster_name NVARCHAR(128)
	            DECLARE @quorum_type VARCHAR(50)
	            DECLARE @quorum_state VARCHAR(50)
	            DECLARE @Healthy INT
	            DECLARE @Primary sysname

	            SELECT @cluster_name = cluster_name ,
			            @quorum_type = quorum_type_desc ,
			            @quorum_state = quorum_state_desc
	            FROM   sys.dm_hadr_cluster
	
	            SELECT @Healthy = COUNT(*) 
	            FROM master.sys.dm_hadr_availability_replica_states 
	            WHERE recovery_health_desc <> 'ONLINE'
		            OR synchronization_health_desc <> 'HEALTHY'

	            SELECT @primary = r.replica_server_name
	            FROM master.sys.dm_hadr_availability_replica_states s
		            INNER JOIN master.sys.availability_replicas r ON s.replica_id = r.replica_id
	            WHERE role_desc = 'PRIMARY'

	            IF @Primary IS NULL 
		            SELECT ISNULL(@cluster_name, '') AS [ClusterName] ,
				            ag.name,
			               CAST(SERVERPROPERTY(N'Servername') AS sysname) AS [Name] ,
			               ISNULL(@Primary, '') AS PrimaryServer ,
			               @quorum_type AS [ClusterQuorumType] ,
			               @quorum_state AS [ClusterQuorumState] ,
			               CAST(ISNULL(SERVERPROPERTY(N'instancename'), N'') AS sysname) AS [InstanceName] ,
			               CASE @Healthy
					            WHEN 0 THEN 'Healthy'
					            ELSE 'Unhealthly'
			               END AS AvailavaiblityGroupState
		            FROM MASTER.sys.availability_groups ag  
			            INNER JOIN master.sys.dm_hadr_availability_replica_states s ON AG.group_id = s.group_id
			            INNER JOIN master.sys.availability_replicas r ON s.replica_id = r.replica_id
	            ELSE
		            SELECT ISNULL(@cluster_name, '') AS [ClusterName] ,
				            ag.name,
			               CAST(SERVERPROPERTY(N'Servername') AS sysname) AS [Name] ,
			               ISNULL(@Primary, '') AS PrimaryServer ,
			               @quorum_type AS [ClusterQuorumType] ,
			               @quorum_state AS [ClusterQuorumState] ,
			               CAST(ISNULL(SERVERPROPERTY(N'instancename'), N'') AS sysname) AS [InstanceName] ,
			               CASE @Healthy
					            WHEN 0 THEN 'Healthy'
					            ELSE 'Unhealthly'
			               END AS AvailavaiblityGroupState
		            FROM MASTER.sys.availability_groups ag  
			            INNER JOIN master.sys.dm_hadr_availability_replica_states s ON AG.group_id = s.group_id
			            INNER JOIN master.sys.availability_replicas r ON s.replica_id = r.replica_id
		            WHERE s.role_desc = 'PRIMARY'
            END
"@
        Invoke-Sqlcmd -Query $query
        }
 
        Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView
        $sessions | Remove-PSSession
    }
}