By: Tracy Boggiano | Updated: 2018-03-14 | Comments (9) | Related: > Availability Groups
Problem
In most shops you don't have just one Availability Group, you probably have several Availability Groups to monitor or check the status. Opening up SQL Server Management Studio and drilling down to look at the dashboard for each and every Availability Group to see the status is very inefficient. What if we could run a PowerShell script and return the same information in a grid that we can even sort and filter?
Solution
Our solution involves three PowerShell functions that can be run that pull back information from the three sections of the Availability Groups Dashboard:
- Cluster health information
- Availability replica health information
- Database health information
The sections of the dashboard look like the following. The first section I named the “Cluster Health Information” looks like this. As you can see it shows you the health and the properties of the current Availability Group cluster.
The next section shows you the health of the current availability replicas. By default, it only shows you four columns, but you have the ability to show more columns by clicking on the Add/Remove Columns link. Our PowerShell script will show all relevant columns.
Finally, the database health section shows the health of each database in the Availability Group.
Behind each one of these sections is T-SQL code that is ran to return data to you to display the data. So now we will look at how to wrap that T-SQL into a PowerShell function and return into a grid so you can check multiple servers at once.
PowerShell Functions for Availability Group Dashboard Sections
Each PowerShell function has the same basic layout and takes the same parameter $ServerInstanceList. $ServerInstanceList takes a path to a text containing a list of Availability Group servers you want to check. After that it reads the content of that file into a variable, then opens a New-PSSession to each server. Then in the process section of the function we load our T-SQL that corresponds to each dashboard and execute and combine into our grid.
The three functions we are creating are:
- Get-AvailabilityGroupStatus
- Get-SqlAvailabilityReplicaStatus
- Get-SqlDatabaseReplicaStatus
To call each function you load the function into PowerShell, then execute it with a path for example:
Get-AvailabilityGroupStatus -ServerInstanceList "C:\temp\servers.txt" Get-SqlAvailabilityReplicaStatus -ServerInstanceList "C:\temp\servers.txt" Get-SqlDatabaseReplicaStatus -ServerInstanceList "C:\temp\servers.txt"
For each one of the functions you get a different grid, that you are able to apply filters to and sort.
PowerShell Scripts to Produce Dashboards
You can download the entire script here or review the code below.
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 -ServerInstanceList "c:\temp\servers.txt" Gets the status Availabiliy Groups on all servers where their name in teh specified text file.. #> [CmdletBinding()] Param ( [string] $ServerInstanceList ) begin { $SQLInstance = Get-Content $ServerInstanceList $SQLInstance | % { New-PSSession -ComputerName $_ | out-null} } process { $sessions = Get-PSSession $scriptblock = { $sql = " 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 $sql } Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView $sessions | Remove-PSSession } }
Function Get-SqlAvailabilityReplicaStatus { <# .SYNOPSIS Get the status the availability group replicas for each server. .DESCRIPTION Displays the status for availability groups replicas 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 -ServerInstanceList "c:\temp\servers.txt" Gets the status Availabiliy Groups on all servers where their name in teh specified text file.. #> [CmdletBinding()] Param ( [string] $ServerInstanceList ) begin { $SQLInstance = Get-Content $ServerInstanceList $SQLInstance | % { New-PSSession -ComputerName $_ | out-null} } process { $sessions = Get-PSSession $scriptblock = { $SQL = " IF SERVERPROPERTY(N'IsHadrEnabled') = 1 BEGIN SELECT arrc.replica_server_name , COUNT(cm.member_name) AS node_count , cm.member_state_desc AS member_state_desc , SUM(cm.number_of_quorum_votes) AS quorum_vote_sum INTO #tmpar_availability_replica_cluster_info FROM ( SELECT DISTINCT replica_server_name , node_name FROM master.sys.dm_hadr_availability_replica_cluster_nodes ) AS arrc LEFT OUTER JOIN master.sys.dm_hadr_cluster_members AS cm ON UPPER(arrc.node_name) = UPPER(cm.member_name) GROUP BY arrc.replica_server_name, cm.member_state_desc; SELECT * INTO #tmpar_ags FROM master.sys.dm_hadr_availability_group_states SELECT ar.group_id , ar.replica_id , ar.replica_server_name , ar.availability_mode , ( CASE WHEN UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) THEN 1 ELSE 0 END ) AS role , ars.synchronization_health INTO #tmpar_availabilty_mode FROM master.sys.availability_replicas AS ar LEFT JOIN #tmpar_ags AS ags ON ags.group_id = ar.group_id LEFT JOIN master.sys.dm_hadr_availability_replica_states AS ars ON ar.group_id = ars.group_id AND ar.replica_id = ars.replica_id SELECT am1.replica_id , am1.role , ( CASE WHEN ( am1.synchronization_health IS NULL ) THEN 3 ELSE am1.synchronization_health END ) AS sync_state , ( CASE WHEN ( am1.availability_mode IS NULL ) OR ( am3.availability_mode IS NULL ) THEN NULL WHEN ( am1.role = 1 ) THEN 1 WHEN ( am1.availability_mode = 0 OR am3.availability_mode = 0 ) THEN 0 ELSE 1 END ) AS effective_availability_mode INTO #tmpar_replica_rollupstate FROM #tmpar_availabilty_mode AS am1 LEFT JOIN ( SELECT group_id , role , availability_mode FROM #tmpar_availabilty_mode AS am2 WHERE am2.role = 1 ) AS am3 ON am1.group_id = am3.group_id SELECT AR.replica_server_name AS [Name] , AR.availability_mode_desc AS [AvailabilityMode] , AR.backup_priority AS [BackupPriority] , AR.primary_role_allow_connections_desc AS [ConnectionModeInPrimaryRole] , AR.secondary_role_allow_connections_desc AS [ConnectionModeInSecondaryRole] , arstates.connected_state_desc AS [ConnectionState] , ISNULL(AR.create_date, 0) AS [CreateDate] , ISNULL(AR.modify_date, 0) AS [DateLastModified] , ISNULL(AR.endpoint_url, N'''') AS [EndpointUrl] , AR.failover_mode AS [FailoverMode] , arcs.join_state_desc AS [JoinState] , ISNULL(arstates.last_connect_error_description, N'') AS [LastConnectErrorDescription] , ISNULL(arstates.last_connect_error_number, '') AS [LastConnectErrorNumber] , ISNULL(arstates.last_connect_error_timestamp, '') AS [LastConnectErrorTimestamp] , member_state_desc AS [MemberState] , arstates.operational_state_desc AS [OperationalState] , SUSER_SNAME(AR.owner_sid) AS [Owner] , ISNULL(arci.quorum_vote_sum, -1) AS [QuorumVoteCount] , ISNULL(AR.read_only_routing_url, '') AS [ReadonlyRoutingConnectionUrl] , arstates.role_desc AS [Role] , arstates.recovery_health_desc AS [RollupRecoveryState] , ISNULL(AR.session_timeout, -1) AS [SessionTimeout] , ISNULL(AR.seeding_mode, 1) AS [SeedingMode] FROM master.sys.availability_groups AS AG INNER JOIN master.sys.availability_replicas AS AR ON ( AR.replica_server_name IS NOT NULL ) AND ( AR.group_id = AG.group_id ) LEFT OUTER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id LEFT OUTER JOIN master.sys.dm_hadr_availability_replica_cluster_states AS arcs ON AR.replica_id = arcs.replica_id LEFT OUTER JOIN #tmpar_availability_replica_cluster_info AS arci ON UPPER(AR.replica_server_name) = UPPER(arci.replica_server_name) LEFT OUTER JOIN #tmpar_replica_rollupstate AS arrollupstates ON AR.replica_id = arrollupstates.replica_id ORDER BY [Name] ASC DROP TABLE #tmpar_availabilty_mode DROP TABLE #tmpar_ags DROP TABLE #tmpar_availability_replica_cluster_info DROP TABLE #tmpar_replica_rollupstate END" Invoke-Sqlcmd -Query $sql } Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView $sessions | Remove-PSSession } }
Function Get-SqlDatabaseReplicaStatus { <# .SYNOPSIS Get the status the databases in every availability group for each servers. .DESCRIPTION Displays the status databaswes in every availability group 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-SqlDatabaseReplicaStatus -ServerInstanceList "c:\temp\servers.txt" Gets the status Availabiliy Groups on all servers where their name in teh specified text file.. #> [CmdletBinding()] Param ( [string] $ServerInstanceList ) begin { $SQLInstance = Get-Content $ServerInstanceList $SQLInstance | % { New-PSSession -ComputerName $_ | out-null} } process { $sessions = Get-PSSession $scriptblock = { $sql = " IF SERVERPROPERTY(N'IsHadrEnabled') = 1 BEGIN SELECT ars.role , drs.database_id , drs.replica_id , drs.last_commit_time INTO #tmpdbr_database_replica_states_primary_LCT FROM master.sys.dm_hadr_database_replica_states AS drs LEFT JOIN master.sys.dm_hadr_availability_replica_states ars ON drs.replica_id = ars.replica_id WHERE ars.role = 1 SELECT AR.replica_server_name AS [AvailabilityReplicaServerName] , dbcs.database_name AS [AvailabilityDatabaseName] , AG.name AS [AvailabilityGroupName] , ISNULL(dbr.database_id, 0) AS [DatabaseId] , CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL( DATEDIFF( ss , dbr.last_commit_time, dbrp.last_commit_time ) , 0 ) END AS [EstimatedDataLoss] , ISNULL( CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate END , -1 ) AS [EstimatedRecoveryTime] , ISNULL(dbr.filestream_send_rate, -1) AS [FileStreamSendRate] , ISNULL(dbcs.is_failover_ready, 0) AS [IsFailoverReady] , ISNULL(dbcs.is_database_joined, 0) AS [IsJoined] , arstates.is_local AS [IsLocal] , ISNULL(dbr.is_suspended, 0) AS [IsSuspended] , ISNULL(dbr.last_commit_time, 0) AS [LastCommitTime] , ISNULL(dbr.last_hardened_time, 0) AS [LastHardenedTime] , ISNULL(dbr.last_received_time, 0) AS [LastReceivedTime] , ISNULL(dbr.last_redone_time, 0) AS [LastRedoneTime] , ISNULL(dbr.last_sent_time, 0) AS [LastSentTime] , ISNULL(dbr.log_send_queue_size, -1) AS [LogSendQueueSize] , ISNULL(dbr.log_send_rate, -1) AS [LogSendRate] , ISNULL(dbr.redo_queue_size, -1) AS [RedoQueueSize] , ISNULL(dbr.redo_rate, -1) AS [RedoRate] , ISNULL(AR.availability_mode, 2) AS [ReplicaAvailabilityMode] , arstates.role_desc AS [ReplicaRole] , dbr.suspend_reason_desc AS [SuspendReason] , ISNULL( CASE dbr.log_send_rate WHEN 0 THEN -1 ELSE CAST(dbr.log_send_queue_size AS FLOAT) / dbr.log_send_rate END , -1 ) AS [SynchronizationPerformance] , dbr.synchronization_state_desc AS [SynchronizationState] FROM master.sys.availability_groups AS AG INNER JOIN master.sys.availability_replicas AS AR ON AR.group_id = AG.group_id INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON dbcs.replica_id = AR.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbr ON dbcs.replica_id = dbr.replica_id AND dbcs.group_database_id = dbr.group_database_id LEFT OUTER JOIN #tmpdbr_database_replica_states_primary_LCT AS dbrp ON dbr.database_id = dbrp.database_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON arstates.replica_id = AR.replica_id ORDER BY [AvailabilityReplicaServerName] ASC , [AvailabilityDatabaseName] ASC; DROP TABLE #tmpdbr_database_replica_states_primary_LCT END" Invoke-Sqlcmd -Query $sql } Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView $sessions | Remove-PSSession } }
Next Steps
- Create a PowerShell Module to load the functions so you can easily call them. Take a look at this for more information.
- Review the data on the Availability Group dashboards.
- Become familiar with how to use the PowerShell grid.
About the author
Tracy Boggiano is the Database Superhero for ChannelAdvisor in North Carolina specializing in automation, performance tuning, and high availability/disaster recovery technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-03-14
