How to Evaluate Newly Created Policy
You can evaluate the newly created policy by right clicking the Policies child node under Management | Policy Management as shown in the image below.
You need to select the policy which you need to evaluate and click the Evaluate button which will execute the policy against all the databases in the current instance of SQL Server 2008 and will provide the results found as shown in the image below.
The green check icon indicates that two databases are in compliance with policy and the red cross icon indicates that the other two databases are non compliance with the policy. You can click on the View…. Link in the details panel to know the reason for non compliance
The reason for the failure is that some databases are configured to have the recovery model as SIMPLE where in it should be configured to use FULL recovery mode as per the organizations policy standards. Database Administrator / Policy Administrators can quickly fix the policy violation issue by checking the check box and clicking the Apply button. This will pop up a Policy Evaluation Warning message (if you are executing this policy for the first time) to make the changes; click YES to change the Recovery Model of databases from SIMPLE to FULL.
The below snippet shows that the changes are made and now all the databases on the SQL Server 2008 instance are in compliance to the companies policies.
How to Manage Policies
Polices can be created and managed by using SQL Server Management Studio (SSMS). In brief the DBA / Policy Administrator needs to first select the correct facet that contains the property which needs to be monitored or configured. Secondly you need to create a condition by using the selected facet. Next you need to create a policy that uses the condition and then run the evaluation, which will let you know whether you server is in compliance or non compliance to the organizations policies.
Where the policies are stored in SQL Server 2008
SQL Server 2008 stores all the policies in the MSDB database, so DBA needs to make sure that MSDB database is backed up immediately once a policy or a condition has changed.
Conclusion
Policy Based Management feature helps DBA’s to maintain organizations level policies across SQL Server 2008 databases environments.
]]>