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:
- Facets
- Conditions
- Target
- 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.
- The recovery model of the user database must be FULL.
- The Auto close and auto shrink must be disabled.
- 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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
On Create New Policy screen, you can see that the policy evaluation schedule is assigned automatically. See the following image:
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:
Now let us test the policy. To do that, right-click on the policy and select Evaluate. See the following image:
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:
In Results Details View dialog box, you can see the list of the conditions, its expected value and actual value. See the following image:
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022