Implementing Database Settings Using Policy Based Management
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.
- The expected value for @AutoShrink was False however, the actual value turned out to be True
- The expected value for @AutoUpdateStatisticsEnabled was True however, the actual value turned out to be False
- The expected value for @PageVerify was Checksum however, the actual value turned out to be None
- The expected value for @RecoveryModel was Full however, the actual value turned out to be Simple
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 Apply button 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.
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.