Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a SQL Server Availability Group Dashboard for All Servers


By:   |   Read Comments (6)   |   Related Tips: More > Availability Groups

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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:

  1. Cluster health information
  2. Availability replica health information
  3. 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.

Cluster Health Information Dashboard - Description: Cluster health information section from Availablity Group Dashboard

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.

Availability Replica Health Information Dashboard - Description: Availability replica health information section from Availablity Group Dashboard

Finally, the database health section shows the health of each database in the Availability Group. 

Database Health Information - Description: Database health information section from Availablity Group Dashboard

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:

  1. Get-AvailabilityGroupStatus
  2. Get-SqlAvailabilityReplicaStatus
  3. 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.

Get-AvailabilityGroupStatus - Description: Get-AvailabilityGroupStatus output
Get-SqlAvailabilityReplicaStatus - Description: Get-SqlAvailabilityReplicaStatus output
Get-SqlDatabaseReplicaStatus - Description: Get-SqlDatabaseReplicaStatus output

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Tracy Boggiano Tracy Boggiano is the Database Superhero for ChannelAdvisor in North Carolina specializing in automation, performance tuning, and high availability/disaster recovery technologies.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 03, 2018 - 8:42:15 AM - Tracy Boggiano Back To Top

Well, get to the dashboards you have two options.  The easiest being copy the code into PowerShell ISE, then put the calls for the functions at the bottom to run the procedure.  The second is to look at the first link under next steps and create your module and save these functions in your own module.  Then you can Import-Module MyModule (Whatever you named it) then call this functions anywhere without having to have all the code copied.

 


Wednesday, March 21, 2018 - 4:22:37 PM - reddy Back To Top

 

Can you please mention the step by step process to get this dashboard? I am not really familier with Poershell and it is kind of difficult to execute these steps.


Tuesday, March 20, 2018 - 8:07:01 AM - Tracy Boggiano Back To Top

 The text file just contains on each line the of name of a server.  Yes, named instances would make a difference has the script is trying to connect using the server name using via remote PowerShell and will not recognize that has a computer name.  

 


Monday, March 19, 2018 - 7:00:52 PM - Richard L. Dawson Back To Top

Phillip Holmes,
     There are tons of articles on the net showing how to do just about anything with Powershell. You can find a decent example on The Scripting Guy's column from 2010. Here's a link to it.

https://blogs.technet.microsoft.com/heyscriptingguy/2010/12/30/learn-how-to-run-powershell-scripts-against-multiple-computers/

 

Richard


Monday, March 19, 2018 - 6:58:06 PM - Richard L. Dawson Back To Top

In the script Get-SqlAvailabilityReplicaStatus, there is a column named "seeding_mode" in your query that doesn't exist for Sql Server 2012 AGs. We don't have any new than 2012 so I just commented that column out and the function works just fine.

Thanks for a well-written article.

Richard

 


Thursday, March 15, 2018 - 10:15:56 AM - Philip Holmes Back To Top

 Tracy,

I like the idea of your "PowerShell Functions for Availability Group Dashboard Sections" but I am not able to get it to work. Can you indicate how the servers.txt file should be laid out. I am using named instances does this make a differance?

Thanks


Learn more about SQL Server tools