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: SQLNode1\INST1
• Secondary Replica: SQLNode2\INST1
• 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

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023