The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups

In this 34^th 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

  

• Evaluation Modes: 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.

1. Availability Group State

   • Target types: Availability Group

     

2. 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.

1. Availability Group State

   • Target types: Availability Group

     

2. 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 2^nd 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 (3^rd) 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