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.

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

 

Visualize your SQL Server Always On environment in a whole new way with SentryOne SQL Sentry.

With three different ways to analyze your environment, you can stay on top of health status as well as configuration information in easy to read layouts.

"The [SentryOne] visualization of AlwaysOn Availability Groups is quite helpful and being able to see the volume of data helps determine hot servers."

- DBA (TrustRadius reviewer)

AlwaysOn Monitoring with SentryOne

 

Availability Groups

 

SentryOne SQL Sentry can automatically monitor your AlwaysOn Availability Groups, giving rapid insight into the state of your environment. You have the ability to switch views into how Availability Groups are laid out—for example, by Windows Server Failover Cluster node, by SQL Server instances, and by Availability Group. Powerful visualizations of data flow and obvious color highlighting allow you to identify health issues quickly.

Tracking Key Metrics

 

SQL Sentry also tracks key metrics related to data being sent to and from replicas, as well as the send and redo queue so that you know how your database synchronization process is performing. You can also track replica state changes and errors in the event of an Availability Group failover between replicas.

Alerting

 

Use the built-in conditions for alerting to notify you when replicas become unhealthy and Availability Groups failover. Alternatively, you can write your own alerts and responses using the powerful Advisory Conditions™ framework; for example, calculating whether all of the databases in an Availability Group meet your Recovery Time Objective (RTO).

SQL Server Monitoring

 

The SQL Sentry Performance Analysis dashboard gives you the ability to see both high-level and detail-level metrics about your environment, right inside the client. You can see exactly what is happening in your environment in real time. With clear symbols marking which replicas are configured for automatic failover, read-only connections, or synchronous synchronization, you can answer both SQL Server performance and configuration questions as soon as you open SQL Sentry.

Always On Performance Monitoring Resources

Want to learn more about Always On Availability Group monitoring? Check out the following resources: