SQL Server Always On Availability Group Monitoring

What Are Always On Availability Groups?

First introduced with Server 2012 Enterprise Edition, Always On Availability Groups (AGs) provide database-level high availability (HA) with automatic failover for multiple SQL Server user databases. Always On AGs are SQL Server’s premier HA and disaster recovery (DR) technology and essentially replace the older Database Mirroring technology, which had several significant limitations. Always On AGs overcome these limitations and extend SQL Server’s HA and DR capabilities.

An AG protects a set of user databases, supporting up to eight secondary replicas of the primary database. In the event of a failure on the primary database, all of the databases within an AG will fail over together and become active on the designated failover partner. Additionally, you can configure secondary replicas to allow read-only activity and most backup operations, potentially taking some of the load away from the primary.

Always On Availability Group Capabilities

Always On AGs in SQL Server 2017 provide the following capabilities:

• Protection for multiple user databases
• Support for up to eight secondary replicas
• Support for up to three synchronous replicas
• Automatic failover
• Can combine both synchronous replicas for HA and asynchronous replicas for DR
• Readable secondaries

Deploying Always On AGs for HA on Windows requires a Windows Server Failover Cluster (WSFC). Each availability replica resides on a different node of the WSFC. On Linux, Always On AGs require PaceMaker.

You can mix and match asynchronous and synchronous replicas within the same AG. There’s no limit to the number of databases that can be included in an AG, and all the databases within an AG will fail over together. You can optionally configure one or more secondary replicas to support read-only access for tasks such as backup and reporting.

Always On Availability Group Components

Always On AGs consist of the following components:

• Availability Group—The collection of user databases that's being protected
• Primary replica—The principle SQL Server instance that hosts the protected databases
• Secondary replica(s)—The target SQL Server instances, which host a set of secondary replicated databases and is a potential failover target
• Listener—Provides client connectivity to the AG and enables automatic client reconnection in the event of a failover
• Asynchronous-commit mode—Used mainly for DR; changes from the primary replica are sent asynchronously to the secondary replicas
• Synchronous-commit mode—Used mainly for HA; changes from the primary replica are kept in sync with the secondary replica as updates are applied to the secondary replica
• Basic Availability Group—Provides SQL Server 2017 Standard Edition support for a two-node AG

An Always On AG primary replica sends transaction log records from each protected database to the target databases on the secondary replicas. With asynchronous-commit mode, the primary replica commits transactions without waiting for an acknowledgment that the secondary replica has hardened its transaction log. With synchronous-commit mode, the primary replica waits for the secondary replica to acknowledge that it has hardened its log before the transaction is committed on the primary.

The Benefits of Monitoring Always On Availability Groups

Monitoring the status of your Always On AGs provides immediate insight into the current state of your AGs—it’s essential to ensure that you maintain healthy synchronization between your primary and secondary replicas. Always On AG monitoring enables you to track key AG performance metrics and identify any areas that might impact your database performance or the synchronization process.

Monitoring also enables you to set up conditions for alerts that can notify you about important changes in your AGs. For example, alerts can notify you when replicas become unhealthy or when an AG performs a failover, enabling you to take any necessary remedial actions.

SolarWinds SQL Sentry

With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment. 

Getting Started Monitoring Always On Availability Groups

The most basic way to get started monitoring AGs is by using the built-in dashboard in SQL Server Management Studio (SSMS). Once an AG has been created using either T-SQL or SSMS, the Always On Availability Group Dashboard can be opened in SMSS by right-clicking the Availability Databases node. The SSMS AG dashboard provides a basic monitoring console showing the replicas, their roles, the failover mode, and their synchronization status.

The SSMS dashboard can also be customized to show additional information by using the Add/Remove Columns link on the dashboard. As an alternative, you can monitor Always On AGs by querying SQL Server’s Dynamic Management Views (DMVs). (Learn more about the AG DMVs—Always On Availability Groups Dynamic Management Views - Functions.) Windows Performance Monitor also provides the SQLServer: Availability Replica and SQLServer: Database Replica counters.

You can also leverage third-party tools for monitoring SQL Server Always On AGs. Third-party tools provide deeper performance metrics and more advanced capabilities than the basic SSMS AG dashboard.

Essential Always On Availability Group Performance Monitoring Tool Capabilities

There are several crucial capabilities you should look for in an Always On AG performance monitoring tool.

• A single pane of glass that displays the AG name, server status, databases, replica names, failover mode, availability mode, replica role, and synchronization health
• The ability to display vital synchronization performance metrics, including the Log Send Queue Size, Log Send Rate, Estimated Data Loss, and Estimated Recovery Time
• The ability to generate alerts for AG error conditions and failover events, including custom alerts
• History of replica failover events and errors