Implementing Database Settings Using Policy Based Management
6. The next option within the Create New Policy Parent window is Against targets, by default Every Database 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.