Nisarg Upadhyay
PageVerify is Checksum

Enforce SQL database best practices using SQL Server Policy-Based Management

July 24, 2020 by

In this article, I am going to explain how we can enforce the SQL database best practices using Policy-Based Management. The policy-based management feature of SQL Server was introduced in SQL Server 2005. This feature was useful because it helps database administrators to define and enforce the database policies based on the organizations’ requirements.

The SQL Server policy-based management has the below components:

  1. Facets
  2. Conditions
  3. Target
  4. Evaluation Mode

Facets are the predefined policies that describe the functionality of the SQL Objects. Facets are evaluated and manage the SQL Object. For example, Database Options is called a facet. There are 74 predefined facets.

Conditions are the state of the facets’ properties, which is evaluated. For example, 140 is the value of the compatibility level property of the SQL Database.

Targets are the type of objects that are managed and evaluated.

Evaluation mode determines how the policy will be evaluated by comparing the value defined in the setting with the actual value of a setting. If condition value matches the actual values of the setting, then the policy evaluated as true, or it will be evaluated as false. You can perform the evaluation manually (OnDemand), or it can be scheduled.

You can read more about policy-based management in this article, SQL Server policy-based management.

Business Requirements

Before a few months, my manager asked me to define a specific database policy. The purpose was to make the configuration of the SQL database uniform across all the servers. The following configuration parameters of the database must be uniform across all the servers.

  1. The recovery model of the user database must be FULL.
  2. The Auto close and auto shrink must be disabled.
  3. The page verification option must be CHECKSUM.

The policy must be evaluated every Monday at 7:00 AM.

To configure the policy based on the business requirements, Open SSMS and connect to the SQL Server instance Expand Management Expand Policy Management Right-click on Policy Select New Policy. See the following image:

New Policy

On Create New Policy dialog box, provide the appropriate database policy name in the Name text box. To configure the condition, click on Check Condition drop-down box and select New Condition. See the following image:

New condition

On Create New Condition dialog box, enter the appropriate name of the condition in the Name text box. As I mentioned, SQL Server has 74 predefined facets. We want to configure the properties of the SQL database hence choose Database from Facets drop-down box. See the following image:

Choose Facets

Now, in the expression grid view, we will define the conditions. It is compared with the actual value of the target. Now, the first condition is that the recovery model of the SQL database should be FULL, so choose the @recoverymodel from the field column of the grid view. The operator should be equal to (=) and from the value column, choose FULL. See the following image:

Recovery model is full

The next condition is that Auto Close and Auto Shrink must be disabled on the SQL database. To configure the condition, choose AND in the AndOr column, choose @AutoShrink from Field column, choose equal to (=) from the operator column and choose False in the value column. Similarly, choose @AutoClose from Field column, choose equal to (=) from the operator column and choose False in the value column. See the following image:

Auto shrink and auto close is false

The next condition is that Page verification of the database must be CHECKSUM; hence choose @Pageverify from Field column, choose equal to (=) from the operator column and choose CHECKSUM in the value column. See the following image:

PageVerify is Checksum

We want to evaluate the condition only on the customer database; hence choose the database to choose @id from field column, choose greater than (>) in the Operator column and enter 5 in the Value column. See the following image:

SQL Database id >5

Once all conditions are defined, click OK to save the condition and close the dialog box.

On Create New Policy dialog box, choose Check configuration parameter from Check condition drop-down box. Now, we want to run this policy against the databases hence choose the Check configuration parameters in the “Against targets” textbox. See the following image:

Check condition against the target

As per business requirements, the policy should be evaluated at 7 AM every Monday. To configure the schedule, accordingly, choose On schedule from the evaluation mode. To define the schedule, click on New. See the following image:

Select evaluation mode

On the New Job schedule dialog box, enter the appropriate name of the schedule, From the occurs drop-down box, select Weekly and From the list of the days click on Monday. In text box named Occurs once at, enter 7:00:00. The schedule has been created, click on OK to save the schedule and close the dialog box. See the following image:

New schedule

On Create New Policy screen, you can see that the policy evaluation schedule is assigned automatically. See the following image:

Choose schedule

Click OK to save the policy and close the dialog box. In the SQL Server management studio, you can view the policy under Policy Management and conditions under conditions. See the following image:

View policy and condition

Now let us test the policy. To do that, right-click on the policy and select Evaluate. See the following image:

Evaluate policy

In the Evaluate Policies dialog box, you can see the result of the execution of the policy. To view detailed information on policy evaluation, click on View in Details column of the grid. See the following image:

Policy evaluated successfully

In Results Details View dialog box, you can see the list of the conditions, its expected value and actual value. See the following image:

Result details

As you can see, all the conditions defined in the policy are satisfied, hence the policy evaluation is successful.

Summary

In this article, I have explained about policy-based management and how it can be used to define and enforce the SQL database policies based on the organizations’ requirement.

Nisarg Upadhyay
244 Views