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 } }
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.
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.
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.