Configure and Manage Policy Based Management in SQL Server 2008

SQL Server 2008 introduces a new feature called Policy Based Management (aka Declarative Management Framework (DMF)). Database Administrators have always had a tough time to make sure that all the SQL Servers they administer are configured according to the policies and standards of organization. Using the Policy Based Management feature DBA’s can now manage one or more instances of SQL Server 2008 an check for policy compliance or non compliance issues. The policies can be created to manage all the entities of SQL Server 2008, such as SQL Server Configurations, Databases, Tables, Users, Roles, or any other SQL Server objects etc.

How to Configure and Manage Policy Based Management

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In the Object Explorer, Click on Management ? Policy Management and you will be able to see Policies, Conditions & Facets as child nodes



3. Click on Facets Node and expand it to see 74 predefined set of SQL Server Facets which are useful when you want to create policies for SQL Server 2008



4. Double Click on Database Maintenance Facet to see all the properties that can be managed when we create a policy using this facet



How to Create a Condition
In Policy Based Management the initial step is to create a condition. New conditions can be created by right clicking the Conditions child node under Management | Policy Management.


Once the Create New Condition popup window opens, you need to provide the Name, Facet and the Expression values as shown in the image below. In this condition we are setting an expression that all the databases on the current SQL Server 2008 Instance should have the Recovery Model configured as FULL


How to Create a Policy
In Policy Based Management the next step is to create a policy which uses the newly added condition. A New Policy can be created by right clicking the Policies child node under Management | Policy Management.



Once the Create New Policy popup window opens you need to provide Name, Check Condition and Against targets values as shown in the below snippet.



The policy evaluation modes are determined by the characteristics of the Policy Based Management facet that are used by the policy. All facets support On Demand and On Schedule (Using this you can schedule policies to run as a SQL Server Agent job on a specific schedule). Facets support On Change: log only if the change of the facet state can be captured by some events. Facets support On change: prevent if there is transactional support for the DDL statements that change the facets state. Policies that are automated with one of these three execution modes can be enabled and disabled.
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.