How to Use Policy Based Management to Implement Database Settings

Introduction

Database Administrators have always had a tough time to ensuring that all the SQL Servers administered by them are configured according to the policies and standards of organization. Using SQL Server’s  Policy Based Management feature DBAs can now manage one or more instances of SQL Server 2008 and check for policy compliance issues. In this article we will utilize Policy Based Management (aka Declarative Management Framework or DMF) feature of SQL Server to implement and verify database settings on all production databases.

It is best practice to enforce the below settings on each Production database. However, it can be tedious to go through each database and then check whether the below database settings are implemented across databases. In this article I will explain it to you how to utilize the Policy Based Management Feature of SQL Server 2008 to create a policy to verify these settings on all databases and in cases of non-complaince how to bring them back into complaince.

Database setting to enforce on each user database :

• Auto Close and Auto Shrink Properties of database set to False
• Auto Create Statistics and Auto Update Statistics set to True
• Compatibility Level of all the user database set as 100
• Page Verify set as CHECKSUM
• Recovery Model of all user database set to Full
• Restrict Access set as MULTI_USER

Configure a Policy to Verify Database Settings

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio

2. In the Object Explorer, Click on Management > Policy Management and you will be able to see Policies, Conditions & Facets as child nodes

3. Right click Policies and then select New Policy…. from the drop down list as shown in the snippet below to open the  Create New Policy Popup window.

4. In the Create New Policy popup window you need to provide the name of the policy as “Implementing and Verify Database Settings for Production Databases” and then click the drop down list under Check Condition. As highlighted in the snippet below click on the New Condition… option to open up the Create New Condition window.

5. In the Create New Condition popup window you need to provide the name of the condition as “Verify and Change Database Settings”. In the Facet drop down list you need to choose the Facet as Database Options as shown in the snippet below. Under Expression you need to select Field value as @AutoCloseand then choose Operator value as ‘ = ‘ and finally choose Value as False. Now that you have successfully added the first field you can now go ahead and add rest of the fields as shown in the snippet below.

Once you have successfully added all the above shown fields of Database Options Facet, click OK to save the changes and to return to the parent Create New Policy – Implementing and Verify Database Settings for Production Database windows where you will see that the newly created condition “Verify and Change Database Settings” is selected by default.

6. The next option within the Create New Policy Parent window is Against targets, by default EveryDatabase option will be checked as shown in the snippet below.

7. Since, we want to perform this check only against all the online user databases choose Online User Database option under Against target as shown in the snippet below.

Some of the other targets that are available to you are mentioned below for reference:

a) Every Database: – when this option is selected the policy will target all the database including system database
b) Online User Database: – when this option is selected the policy will target only user databases which are online
c) User or Model: – when this option is selected the policy will target only User or Model databases
d) Read – only: – when this option is selected the policy will target only read-only databases
e) Is Master:- when this option is selected the policy will target only master database
f) System Databases Not Including Master:- when this option is selected the policy will target all system databases except master database
g) Databases Not in SQL Server 2008 Compatibility Level:- when this option is selected the policy will target only those database which are not in SQL Server 2008 Compatibility Level

8. Next, choose an appropriate mode of execution for this policy. SQL Server 2008 supports three types of execution modes :

• On demand: – Policy will only be run when you run it from the Evaluate dialog box
• On schedule: – Periodically evaluates the policy, records a log entry for policies that have out-of-compliance, and creates a report.
• On change: log only: – When changes are tried, this option does not prevent out-of-compliance changes, but logs policy violations.

In this example we will choose the Evaluation Mode as On Schedule and then click Pick button to associate this policy with existing schedules. Click New to open up New Job schedule.

9. In the New Job Schedule popup window choose the settings as shown in the snippet below and click OK to save the New Job Schedule and to return to parent Create New Policy Window.

10. Finally, click OK to in the Create New Policy window to create the policy.

Evaluate the Policy

Once you have successfully created Implementing and Verify Database Settings for Production Databases Policy then the next step will be to evaluate the policy.

1. Connect to SQL Server 2008  using SQL Server Management Studio

2. In the Object Explorer, expand Management, expand Policy Management and then expand Policies. Right click Implementing and Verify Database Settings for Production Databases Policy which was created newly and then select Evaluate option from the drop down list as shown below.

 

 

3. In the below snippet you can see that the policy has failed for most of the databases in my local instance of SQL Server 2008.

4. You can check why the Policy has failed by clicking the View… link under details for a particular database. In the below snippet you can see that for the AdventureWorks database the policy has failed because of four issues.

1. The expected value for @AutoShrink was False however, the actual value turned out to be True
2. The expected value for @AutoUpdateStatisticsEnabled was True however, the actual value turned out to be False
3. The expected value for @PageVerify was Checksum however, the actual value turned out to beNone
4. The expected value for @RecoveryModel was Full however, the actual value turned out to beSimple

Click Close to exit the Results Detailed View and to return to Evaluate Policies window.

5. In the Evaluate Policies window, select the check box against the target database and click the Applybutton to get compliance with the policy.

6. When you click the Apply button it will pop up a Policy Evaluation Warning as shown in the snippet below. Click Yes to apply the policy against the database.

7. Once the policy has modified all the selected targets which do not comply, you will be able to see the below screen indicating that all the targets are in compliance now. Click the Close button to exit.

SQL Server 2008 stores all the policies in the MSDB database, so the DBA will need to make sure that the MSDB database is backed up immediately once a policy or a condition is newly added or changed.

Conclusion

In this article you have seen how easily a database administrator can check for policy compliance or non compliance issues for all production databases by leveraging the Policy Based Management Feature of SQL Server 2008. This is an excellent feature which helps database administrators to enforce organizational level policies across SQL Server 2008 database environments.