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
- 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.
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.
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 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
- 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.
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
- Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021
- Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021
- Writing U-SQL scripts using Visual Studio for Azure Data Lake Analytics - February 11, 2021