The AlwaysOn Availability Groups view allows you to monitor availability groups, availability databases, and availability replicas.
If you have configured the AlwaysOn Availability Groups feature on your SQL 2012 Server, SQL Diagnostic Manager automatically recognizes the availability databases and starts to monitor them.
AlwaysOn Availability Groups are part of an integrated solution, introduced in SQL Server 2012 with the goal of achieving the highest level of data availability and disaster recovery for organizations. Availability Groups grant DBAs the ability to automatically or manually failover a group of databases as a single unit with support for up to four secondary replicas. For additional information on availability groups, see the Microsoft document, Overview of AlwaysOn Availability Groups (SQL Server).
Access the AlwaysOn Availability Groups view
To open the AlwaysOn Availability Groups view click the appropriate monitored SQL Server instance, select Databases, and click Availability Group.
Database AlwaysOn Availability Groups statistics
In this view, you can see the health of all your availability groups, availability databases and monitored availability replicas.The database table provides the following information for each availability group (as seen in the view by default):
Database Name
Displays the name of the availability database.
Group Name
Displays the name of the availability group for which the connected server instance hosts a replica.
Replica Name
Displays the name of the server instance that hosts the availability replica.
Failover Mode
Displays the failover mode for which the availability replica is configured. The possible failover modes are automatic or manual.
Availability Mode
Describes if data synchronization will use either the synchronous-commit or asynchronous-commit availability mode.
Replica Role
Indicates the current role of the availability replica. Possible values are primary or secondary.
Synchronization Health
Displays database synchronization state in the availability group. Possible values include: not healthy, partially healthy, and healthy.
Redo Queue Size (KB)
Displays the amount of log records from log files in KB that need redoing in the secondary replica to complete synchronization.
Redo Rate (KB/s)
Rate in KB per second at which log records are redone in the secondary database to complete synchronization.
Log Send Queue Size (KB)
Amount of log records in KB needed to ship to the secondary replica to complete synchronization.
Log Send Rate (KB/s)
Indicates the rate in KB per second at which log records are being sent to the secondary replica to complete synchronization.
Listener DNS Name
The network name of the availability group listener.
Listener IP Address
The IP address reserved for the availability group listener. If the nodes of your Windows cluster are on different subnets you will need an IP address for each of those subnets.
Failover Readiness
Indicates whether the secondary database is synchronized with the corresponding primary database. Possible values include: True (the database is marked as synchronized and is ready for a failover) and False (the database is not synchronized and not ready for a failover).
Synchronization Database Status
Indicates whether the availability database is currently synchronized with the primary replica. Possible synchronization states are: not synchronizing, synchronizing, synchronized, reverting, and initializing.
Database Status
Description of the database state of the availability replica. Possible values include: online, restoring, recovering, recovery_pending, suspect, emergency, and offline.
Suspended Status
An AlwaysOn availability database is suspended. Possible values are true or false.
Last Hardened Time
Indicates the time when the log-block identifier was received for the last hardened LSN on the secondary replica.
Operational Status
Indicates the current operational state of the secondary replica. Possible state values include: pending failover, pending, online, offline, failed, failed no quorum, and NULL.
Connection Status
Indicates whether a secondary replica is currently connected to the primary replica. Possible values include: connected and disconnected.
Synchronization Performance (s)
Indicates the time in seconds it takes to synchronize between the primary and secondary replicas.
Estimated Data Loss (s)
Indicates the time difference in seconds of the last transaction log record in the primary replica and secondary replica. If the primary replica fails, all transaction log records within the time window will be lost.
Estimated Recovery Time (s)
Indicates the time in seconds it takes to redo the catch-up time. The catch-up time is the time it will take for the secondary replica to catch up with the primary replica.
Additional columns available through the Column Chooser:
Database ID
Identifier of the database, unique within an instance of SQL Server.
FileStream Send Rate (KB/s)
Indicates the rate of the FileStream in KB per second at which transactions are being sent to the secondary replicas.
Group ID
Unique identifier (GUID) of the availability group.
Last Connect Error#
Number of the last connection error.
Last Connect Error Description
Description of the last connection error.
Last Connect Error Time
Timestamp of the last connection error.
Listener Port
The TPC port used by the availability group listener.
Replica ID
Identifier of the availability replica within the availability group.
You can hide and unhide additional columns in the Availability Groups table by right clicking on any header in the grid and selecting a column from the Column Chooser dialog.
Availability Groups charts
The AlwaysOn Availability Groups view includes charts that display the queue size and transfer rates of different availability groups.
Queue Size chart
Provides users with graphical details about queues in the AlwaysOn Availability Groups feature. This chart is a stacked bar chart for the "Log Send Queue Size" and "Redo Queue Size."
Transfer Rates chart
Provides users with graphical details about data transfer rates for redo and logs. This chart is a line chart for the "Log Send Rate" and "Redo Rate."
Availability Groups alerts
To view a list of all available alerts, see Metric alerts.
Understand the view colors
The AlwaysOn Availability Groups view uses a color-based alert system. The following colors are associated with a status and action within SQL Diagnostic Manager:
Color | Alert Status | Action |
|---|---|---|
Green | OK | Acceptable threshold where SQL Diagnostic Manager does not generate an alert. |
Blue | Informational | Informational threshold where SQL Diagnostic Manager generates an informational alert. |
Yellow | Warning | Warning threshold where SQL Diagnostic Manager generates a warning alert. |
Red | Critical | Critical threshold where SQL Diagnostic Manager generates a critical alert. |