Rajendra Gupta
SQL Server Always On Availability Group dashboard dashboard

Exploring AG dashboards for monitoring SQL Server Always On Availability Groups

September 11, 2020 by

In this 21st article on SQL Server Always On Availability Group series, we will explore the AG dashboard and its various options for monitoring purposes.

Introduction

Monitoring is an essential and critical aspect of database professionals. You can identify potential issues, rectify them proactively. As you configure SQL Server Always On Availability Group for the high availability and disaster recovery solution in SQL Server, you must monitor them along with SQL Server instance. Suppose you configured Synchronous replicas and it is not healthy, it might cause various issues for you such as transaction log growth, inconsistent data at secondary replica (in case of readable secondary), risk of losing data in case of any issue at the primary replica.

SQL Server management studio provides an in-built AG dashboard to monitor AG replicas, its synchronizations, and various other factors to monitor. In this article, we will explore the AG dashboard.

Requirements

  • You should set up SQL Server Always On Availability Group before going further in this article. You can refer to series articles (TOC at the bottom) for this purpose
  • You should also use the latest version of SQL Server Management Studio to explore all AG dashboard options. Download the latest version 18.6 (at the time of writing this article) using Microsoft docs

Download SSMS

Explore the AG dashboard for SQL Server Always On Availability Groups

Connect to the primary replica, right-click on the availability group folder and click on the Show Dashboard.

Explore AG dashboard for SQL Server Always On Availability Groups

Here, it shows all configured availability groups in your SQL instance. A green tick for each availability group shows it is in a healthy state.

To launch the dashboard for a particular availability group, click on its name and you get the corresponding dashboard.

Dashboard for a particular availability group

The AG dashboard has the following sections:

ag dashboard

Availability group details

This section gives high-level information for the availability group.

  • Availability group name, current owner and replica role: It shows the AG name, the instance from which we launched dashboard and its role (primary or secondary). In this example, it shows the availability group [SQLLogShipAG] launched from [SQLAG1\INST1] instance and it is a primary replica for this AG
  • Availability group state: It shows the state of the AG. Currently, it is showing healthy. In case of any issues, it shows the status like below

    Dashboard errors

  • Failover Mode: We can have manual or automatic failovers in an SQL Server Always On Availability Group. The failover mode also depends on the availability mode
    • Asynchronous mode: Manual failover
    • Synchronous mode: Manual and automatic failover
  • Cluster State: It shows the Windows cluster name and the quorum configuration. In my case, the failover cluster name is [SQLCluster2]

    Cluster State

  • Cluster type: Usually, cluster type is Windows Server Failover Cluster

Availability replicas

In the availability replicas, you get the following details:

  • AG replicas: It shows the configured AG replicas for the availability group. In my example, it shows two AG replicas SQLAG1\INST1 & SQLAG2\INST2
  • Role: It shows the replica role in the AG group. You can have only one primary replica and multiple secondary replicas (depending upon SQL Server versions). Here, SQLAG1\INST1 is the primary replica
  • Availability mode: We can have synchronous or asynchronous availability mode for AG replica. Here, it shows the Synchronous availability mode

    Availability replicas

  • Seeding Mode: We can have manual or automatic seeding modes for an availability group
    • Manual: It is the default behavior. In this mode, we need to manually restore the database copy on the secondary replica before configuring AG
    • Automatic: It is available from SQL Server 2016. We can use automatic seeding for initial data synchronization. Refer to Automatic Seeding in Always On Availability Groups for more details
  • Synchronization State: The synchronization state depends upon the availability mode
    • Synchronous availability mode: Synchronized state
    • Asynchronous availability mode: Synchronizing state

In the above image, we see AG is in the Synchronized state due to the synchronous commit availability mode. In case you change the availability mode to Asynchronous mode (using AG properties), it immediately changes state to the Synchronization state.

Synchronization State

We can add more details to the AG dashboard availability replica section. Click on the Add/Remove columns, and you get a list of options. Currently, it shows a few options in the default view, and you can see a tick for those options.

availability replica section

For example, in the below screenshot, I added the Primary connection mode, secondary connection mode, Quorum votes and member state.

  • Primary Connection Mode: It shows the connections for the primary AG replica
  • Secondary Connection Mode: It shows the connections for the secondary AG replica. We can configure the secondary replica to disallow all connections
  • Quorum votes: It shows the votes held by the replica

Primary Connection Mode

Group by: Availability group replica details

By default, AG dashboard groups the information as per the availability replicas as shown below.

In this section, you get details for the individual AG replicas.

Availability group replica details

You can change the group by selection for availability databases and synchronization state.

Group by: availability databases: It groups the details as per the availability database.

availability databases

Group by: synchronization state: It groups the details as per their synchronization state.

synchronization state

By default, it also gives details for the failover readiness and existing issues. You should monitor the failover readiness before initiating an AG failover to ensure you do not lose any data.

Similar to the availability replica section, we can more details here as well.

AG monitoring parameters

It gives LSN (log sequence number) information for all AG replica using the *LSN options such as End of Log LSN, Recovery LSN, Truncation LSN. You can refer to Data synchronization in SQL Server Always On Availability Groups and Measuring Availability Group synchronization lag for more details.

log sequence number

You can also estimate the recovery time and data loss using this dashboard. In my case, it does not have any data loss.

estimate the recovery time

AG dashboard also gives information a few counters to monitor AG performance. These counters are Log send queue size (KB), Log send rate (KB\sec), Redo queue size (KB), Redo Rate (KB\sec). You can refer to Microsoft docs for this counter information.

Auto-refresh

By default, SSMS refreshes the AG dashboard every 30 seconds. At the top, the dashboard shows the last refresh time.

Auto-refresh

We can pause the auto-refresh from the dashboard. As shown below, the AG dashboard refresh is stopped.

Stopped auto refresh

To change the refresh interval, go to Tools-> Options-> SQL Server Always On-> Dashboard and modify the refresh interval (in seconds) as per your requirements.

refresh interval

SQL Server Always on extended events

SQL Server automatically configures an extended event AlwaysOn_Health to monitor various events in SQL Server Always On Availability Group. The AG dashboard gives a shortcut View always-on health events to open the event file and view the records.

Always on extended events

View Cluster quorum information

We can view failover cluster quorum information such as cluster name, quorum model, member state and vote counts using the AG dashboard Cluster Quorum information for SQL Server Always On Availability Group.

Cluster quorum information

Collect latency data

We can use this option to collect the latency bottleneck information between the AG replicas. Once you click on the collect latency data, it creates a SQL agent job AlwaysOn_Latency_Data_Collection and collects the relevant data for you.

Collect latency data

SQL Agent service should be running so that it can create and execute the job as shown below.

agent jobs

Now, you can navigate to the availability group and view the reports.

  • Primary replica latency ( from primary replica instance)
  • Secondary replica latency( from secondary replica instance)

    latency data

You should launch the AG dashboard from the primary replica. In case you launch it from the secondary replica, you don’t get many details.

As we can see in the below screenshot, it does not give the health status of the availability group. Similarly, you only get details for the secondary replica.

SQL Server Always On Availability Group dashboard dashboard

Conclusion

In this article, we learned the SQL Server Always On Availability Group dashboard and its various option. You can monitor AG using T-SQL, PowerShell scripts along with the third-party tools. You can use any monitoring medium as per your comfort level.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability

Rajendra Gupta
622 Views