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 AGs in SQL Server 2017 provide the following capabilities:
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 AGs consist of the following components:
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.
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.
With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment.
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.
There are several crucial capabilities you should look for in an Always On AG performance monitoring tool.
"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)
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.
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.
Use the built-in conditions for
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.