Policy based management for operational issues with Always On availability groups
Applies to: SQL Server
The Always On availability groups health model evaluates a set of predefined policy based management (PBM) policies. You can use these for viewing the health of an availability group and its availability replicas and databases in SQL Server.
Terms and Definitions
Always On predefined policies
A set of built-in policies that allow a database administrator to check an availability group and its availability replicas and databases for compliance with the states that are defined by the Always On policies.
Always On Availability Groups
A high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.
A container for a discrete set of user databases, known as availability databases, that fail over together.
An instantiation of an availability group that is hosted by a specific instance of SQL Server and that maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to four secondary replicas. The server instances that host the availability replicas for a given availability group must reside on different nodes of a single Windows Server Failover Clustering (WSFC) cluster.
A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).
Always On Dashboard
A SQL Server Management Studio dashboard that provides an at-a-glance view of the health of an availability group. For more information, see Always On Dashboard, later in this topic.
Predefined Policies and Issues
The following table summarizes the predefined policies.
|WSFC Cluster State||WSFC cluster service is offline.||Critical||Instance of SQL Server|
|Availability Group Online State||Availability group is offline.||Critical||Availability group|
|Availability Group Automatic Failover Readiness||Availability group is not ready for automatic failover.||Critical||Availability group|
|Availability Replicas Data Synchronization State||Some availability replicas are not synchronizing data.||Warning||Availability group|
|Synchronous Replicas Data Synchronization State||Some synchronous replicas are not synchronized.||Warning||Availability group|
|Availability Replicas Role State||Some availability replicas do not have a healthy role.||Warning||Availability group|
|Availability Replicas Connection State||Some availability replicas are disconnected.||Warning||Availability group|
|Availability Replica Role State||Availability replica does not have a healthy role.||Critical||Availability replica|
|Availability Replica Connection State||Availability replica is disconnected.||Critical||Availability replica|
|Availability Replica Join State||Availability replica is not joined.||Warning||Availability replica|
|Availability Replica Data Synchronization State||Data synchronization state of some availability database is not healthy.||Warning||Availability replica|
|Availability Database Suspension State||Availability database is suspended.||Warning||Availability database|
|Availability Database Join State||Secondary database is not joined.||Warning||Availability database|
|Availability Database Data Synchronization State||Data synchronization state of availability database is not healthy.||Warning||Availability database|
* For Always On policies, the category names are used as IDs. Changing the name of an Always On category would break its health-evaluation functionality. Therefore, do not modify the names of Always On categories.
Always On Dashboard
The Always On Dashboard gives you an at-a-glance view of the health of an availability group. The Always On Dashboard includes the following features:
Enables you to easily display details about a given availability group, its availability replicas, and its databases.
Displays visual indications of key states to help database administrators make quick operational decisions.
Provides launch points for troubleshooting scenarios.
For a given operational issue, populates the Policy Evaluation Result dialog box with information about specific Always On health policy violations and with links to remediation help.
Provides an health extended event viewer to show previous events for Always On-specific issues.
If failing over the availability group is a possible remediation for an issue, provides a launch point for the linksFail Over Availability Group Wizard. This wizard takes a database administrator through the manual failover process.
Extending the Always On Health Model
Extending the Always On availability groups health model is simply a matter of creating your own user-defined policies and putting them into certain categories based on the type of object that you are monitoring. After you a alter few settings, the Always On dashboard will automatically evaluate your own user-defined policies, as well as the Always On predefined policies.
A user-defined policy can use any of the available PBM facets, including those used by the Always On predefined policies (see [Predefined Policies and Issues](#Always OnPBM), earlier in this topic). The Server facet provides the following properties for monitoring Always On availability groups health: (IsHadrEnabled and HadrManagerStatus). The Server facet also provides properties the following policies for monitoring the WSFC cluster configuration: ClusterQuorumType, and ClusterQuorumState.
For more information, see The Always On Health Model Part 2 -- Extending the Health Model (a SQL Server Always On Team blog).
Use Always On Policies to View the Health of an Availability Group (SQL Server)
Perform a Forced Manual Failover of an Availability Group (SQL Server)
Troubleshoot a Failed Add-File Operation (Always On Availability Groups)
The Always On Health Model Part 1 -- Health Model Architecture
The Always On Health Model Part 2 -- Extending the Health Model
Microsoft SQL Server Always On Solutions Guide for High Availability and Disaster Recovery
Always On Availability Groups (SQL Server)
Overview of Always On Availability Groups (SQL Server)
Administration of an Availability Group (SQL Server)
Monitoring of Availability Groups (SQL Server)
Submit and view feedback for