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 [SQL Sentry] visualization of AlwaysOn Availability Groups is quite helpful and being able to see the volume of data helps determine hot servers."
- DBA (TrustRadius reviewer)
Want to learn more about Always On Availability Group monitoring? Check out the following resources: