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.
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.
]]>