Rajendra Gupta

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

November 9, 2020 by

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

    Facets

  • Conditions: Conditions are the property expressions to evaluate the objects. The condition evaluates to true or false

    Conditions

    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

    View policy and 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 Groups

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.

SQL Server Always On Availability Group dashboard

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.

launch the dashboard from the secondary replica

Here, the primary replica acts as a hub and the secondary replica as spoke. Therefore, we also called it a hub and spoke model.

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 Errors or Availability database warnings

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

      Availability Group State

  2. Server
    • Target types: Server

      Target types

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

      Availability group errors or warnings (Primary replica only)

  2. Server
    • Target types: 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.

Availability replica errors or warnings

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.

Evaluate PBM policies manually

We have a predefined PBM policy AlwaysOnAgAutomaticFailoverHealthPolicy to check whether AG supports automatic failover or not.

predefined PBM policy

This policy defines the following conditions to indicate whether the AG group is set for automatic failover mode or not.

AG group is set for automatic failover mode

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.

set the manual failover

Now, right-click on the [AlwaysOnAgAutomaticFailoverHealthPolicy] policy and choose to Evaluate.

choose to Evaluate

Policy evaluates successfully because it satisfies the conditions we specified above.

Policy evaluates successfully

To get the policy conditions details, click on the View hyperlink.

policy conditions details

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.

suspended data movement

Now, if we re-evaluate the policy, It fails with an error message.

evaluate the policy

In the results detailed view, you get the details of conditions to check. results detailed view

Now, refresh the AG dashboard from the primary replica. It shows errors and warnings.

refresh the AG dashboard

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.

error on 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.

Evaluate AlwaysOnDbrDataSynchronizationState PBM policy for SQL Server Always On Availability Group

Open the conditions and check the expressions it is evaluating.

check the expressions

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.

Policy details results

It fails the conditions check, as shown below. Here, you can see the expected and actual value for comparison purposes.

expected and actual value

Here, It gives the results of the Policy evaluation result and detects the issue of data synchronization.

Policy evaluation result

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 Group dashboard

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

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views