The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
In this 34th article on SQL Server Always On Availability Groups, we will explore policy-based management
features for the AG’s.
Introduction to policy-based management
Policy-Based Management feature allows database professional in defining the best practices, standards for SQL
Server and database related configurations. It is available from SQL Server 2008 onwards. Here, you can use
system-defined policy as well as user-defined policies.
The components of the policy-based Management are as follows.
Facets: It is a set of predefined properties to manage and evaluate SQL Server and database
functionality. You can view existing facets in the SSMS -> Management -> Policy Management -> Facets
Conditions: Conditions are the property expressions to evaluate the objects. The condition
evaluates to true or false
You can view existing conditions in SSMS -> Management -> Policy Management -> Conditions
Targets: A target is an entity that policy-based Management manages. It can be a database, index, table
Policies: A policy applies the conditions on the specified targets. For example, in the below
screenshot, we see policy and its specified conditions, targets
EvaluationModes: We can evaluate a policy with defined conditions and targets
on a fixed schedule or on-demand basis. We can specify On Change-Prevent mode to prevent the
change that makes the policy to evaluate to false
In this article, we will explore how the policy-based management interacts with the SQL Server Always On
Availability group.
Environment details
You can follow my earlier article in this series (TOC at the bottom) and configure two-node availability group
replicas as shown below:
Primary Replica: SQLNode1INST1
Secondary Replica: SQLNode2INST1
Database: [MyNewDB]
SQL Server Always On Availability Group monitoring using policy-based management
SQL Server Always On uses the Policy-Based Management(PBM) for determining its health. In the earlier articles, we
discovered AG dashboard features to monitor synchronization status, data loss, replica states. It executes the PBM
policies on availability replicas (primary and secondary), availability group database and organizes the results in
a dashboard.
The primary replica contains information for all replicas, their synchronization states. It has sufficient
information to compute the health for all availability groups. If we launch the AG dashboard from the primary and
secondary replica, we can note the difference in monitoring.
A primary replica shows the status for all secondary replicas in the AG dashboard, as shown below.
On the other hand, if we launch the dashboard from the secondary replica, we cannot see the status of the primary
and other secondary replicas.
Here, the primary replica acts as a hub and the secondary replica as spoke. Therefore, we also called it a hub and
spoke model.
AG health model and PBM facets
AG dashboard uses the system defined PBM policies and facets to determine its status. We can divide the policies
into four categories.
Availability Group Errors or Availability database warnings
It uses the Database Replica State facet and runs against the AG databases. You can open the
database replica state facet and view its properties and availability target types.
Availability group error or warning for any replica role
In this group, we use the following PBM facets on either primary or secondary replica. It also uses server facets to
verify the Windows Server failover cluster properties.
Availability Group State
Target types: Availability Group
Server
Target types: Server
Availability group errors or warnings (Primary replica only)
In these categories, PBM policies were evaluated on the primary replica in SQL Server Always On Availability Groups.
It uses the following PBM facets for the primary replica.
Availability Group State
Target types: Availability Group
Server
Target types: Server
Availability replica errors or warnings
In this group, PBM policies run for the availability replicas. It uses the Availability Replica facet, as shown
below.
Evaluate PBM policies manually and integrate results with the AG dashboard
We can evaluate a PBM policy on-demand(manually) as well. In my training environment, I have replicas in synchronous
commit mode. The synchronous commit supports both automatic and manual failover. Let’s make it a manual failover for
the time being. You can open AG group properties and modify them as shown below.
We have a predefined PBM policy AlwaysOnAgAutomaticFailoverHealthPolicy to check whether AG
supports automatic failover or not.
This policy defines the following conditions to indicate whether the AG group is set for automatic failover mode or
not.
This policy gets successfully in case of the following conditions:
We should have at least one synchronous replica with enabled auto-failover
OR
AG failover type is manual
We have already set the manual failover, and you can verify the failover mode from the AG dashboard as shown below.
Now, right-click on the [AlwaysOnAgAutomaticFailoverHealthPolicy] policy and choose to Evaluate.
Policy evaluates successfully because it satisfies the conditions we specified above.
To get the policy conditions details, click on the View hyperlink.
Let’s re-evaluate the policy. Before evaluating, do the following changes.
Change the failover type to Automatic
Suspend the data movement for the secondary replica database. We have only one synchronous replica, and if we suspend data movement, it fails the first condition
As shown below, we have suspended data movement for the secondary replica.
Now, if we re-evaluate the policy, It fails with an error message.
In the results detailed view, you get the details of conditions to check.
Now, refresh the AG dashboard from the primary replica. It shows errors and warnings.
Click on the critical error on the availability group state, and it gives the details. In the top, you can see it
gives a policy evaluation results for the availability group.
Evaluate AlwaysOnDbrDataSynchronizationState PBM policy for SQL Server Always On Availability Group
As we saw above, the secondary database is not synchronized because we have suspended the data movement. First, open
the PBM policy and view the condition name, it uses for evaluation.
Open the conditions and check the expressions it is evaluating.
In my lab environment, the replica is in synchronous mode, but its state is not synchronized. If we evaluate the
policy, its conditions should fail. Click on view to get more details on Policy Evaluation Results for availability
replica.
It fails the conditions check, as shown below. Here, you can see the expected and actual value for comparison
purposes.
Here, It gives the results of the Policy evaluation result and detects the issue of data synchronization.
Therefore, for an AG dashboard, we can specify the PBM policy as below.
Check Policies for the SQL Server Always On Availability Group dashboard
The availability group section (Number 1) evaluates four PBM policies and displays results in the AG dashboard.
AG group errors and warnings for primary replica only
AG group errors and warnings for any replica role
Availability Replicas AG dashboard for SQL Server Always On Availability Groups and PBM policy
In the 2nd area of the AG dashboard, it uses the Availability replica errors or warnings category group.
In case we launch the dashboard from the secondary replica, and it evaluates policy for the respective instance.
Availability database AG dashboard and PBM policy
In the last (3rd) section of the SQL Server Always On Availability Group dashboard, it evaluates the
Availability Group errors and availability database warning policies.
If you run the dashboard from the primary replica, you get the status of all secondary replica instances
irrespective of the data synchronization method. You get only local database synchronization status in case the
dashboard is launched from the secondary replica.
Conclusion
In this article, we learned about the usage of policy-based Management in SQL Server for evaluating the availability
group health and published in the dashboard. It is interesting to know how the SQL Server Always On Availability
Group dashboard accommodates all information and display in a friendly GUI format.
In the next article, we will learn to configure the custom PBM policy and implement it in the AG dashboard.
Table of contents
Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs. He can be reached at rajendra.gupta16@gmail.com
