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.

  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 be None
  4. 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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Implementing Database Settings Using Policy Based Management”

  1. In step 7 you say – 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.

    I do not get this option, all that is available to me is Every database. I do get other options if I try to set a different kind of policy. Do I need to add these options? If so, how do I do that? I can’t find where I can add the options you suggest.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |