How To Check Whether Readable Secondary is Enabled on Availability Groups Using Policy Based Management

dbtut July 4, 2019 MSSQL

Policy-Based Management is a feature that is introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to set our standards and to make our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

If you are using Always On, in some cases you can create a policy to check if there is a Readable Secondary Replica.

To comprehend Policy Based Management (PBM), you need to understand some concepts.

Facet

A feature that can be managed by PBM. For example, there is a facet called Availability Replica. And you can use this facet to create a policy that checks whether the readable secondary feature of availability groups is enabled.

You can access all facets via SSMS as follows. To access the facet details, you must double-click on it.

Condition

Checks whether the sub-property of the related facets provide the specified condition. For example, you can create a condition that checks whether the ConnectionModeInSecondaryRole child property of the Availability Replica facet is AllowAllConnections or AllowReadIntentConnectionsOnly. This allows you to check if there is an application that reads from the Secondary Replicas of availability groups. If there is a readable secondary the policy will fail.

Readable Secondary can actually be a very useful function in some cases. This is all about the strategy of the organization you work for. If you want to learn more about readable secondary, I recommend you to read “Readable Secondary On SQL Server Always On Availability Group”.

Create a Policy

Let’s create a policy like we said.

Right click Policies tab as follows from the Management> Policy tab and click New Policy.

We give a name to Policy on the screen that appears. We need to create a condition from Check Condition.

We’re clicking on New Condition.

We give a name to the condition from the screen that appears.

From the Facet section, select the Availability Replica facet.

By clicking “…” in the Expression section, we select @ConnectionModeInSecondaryRole, a sub-feature of the Availability Replica facet.

We select ! = From Operator and AllowAllConnections from value.

We add the second filter using the OR operator.

We select ! = From Operator and AllowReadIntentConnectionsOnly from value.

In this way, we set the policy to fail if there is an AG that allows all connections from the secondary server or the connections connected to Read.

By selecting On Schedule from the Evaluation Mode, we select the interval at which the policy will be checked and then click the Enable check box.

If we choose On Demand, it only checks when we execute policy.

If you have a server-based condition, you can create a server-based condition from the server restriction section.

If you want it to be checked automatically at certain intervals, click New in the Schedule section and determine the frequency that the policy will check the condition. We set it up to work once every day at 12: 00: 000 PM.

We created our policy. We click Evaluate as below to run it manually.

Since I don’t have Always On in my locale, I got the following result. If Always On was installed, there would be a longer list. In the case of a failing, a link in the form of View will also appear. Click on this link to see the cause of the problem.

You can check different parameters using the Availability Replica facet as follows.

With Policy Based Management, you can check many things on SQL Server. And I think a professional database manager should use this feature that SQL Server offers us. You can find more articles on Policy Based Management at the following links.

“How To Check the Compatibility Level of Databases Using Policy Based Management“,

“How To Check Recovery Model of All Databases Using Policy Based Management“,

“How To Check Stored Procedure Names Using Policy Based Management“,

“How To Check VLF Counts in the Databases Using Policy Based Management“,

“How To Check SQL Logins Without Password Policy Enforced or Password Expiration Enabled Using Policy Based Management“,

“How To Check Disabled Audits Using Policy Based Management“,

“How To Check Auto Shrink Enabled Databases Using Policy Based Management“,

“How To Check Whether Availability Groups is Ready To Failover Using Policy Based Management“,

“How To Check Availability Group’s Backup Preference Using Policy Based Management“,

“How To Check Availability Group Automatic Failover Settings Using Policy Based Management“,

“How To Check Whether Data File Sizes is Reached a Specific Size Using Policy Based Management”,

“How To Check Auto Close Option of Databases Using Policy Based Management”,