Policy-Based Management in SQL Server 2008

As we discussed earlier that we can use DDL triggers to enforce developers to follow some guidelines, but this is restricted only to DDL commands. What if we need to create policies to check other things or take corrective/preventive actions?

Policy-Based Management is a new feature in SQL Server 2008. It allows to define and enforce policies for configuring and managing SQL Server across the enterprise. Initially this was called Declarative Management Framework but was renamed.

Let's understand Policy-Based Management and its components :

Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
Facet - a predefined set of properties that can be managed
Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
Policy - a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes:

Click on Facets to see available facets:

There is a comprehensive collection of facets in SQL Server 2008, allowing us to manage just about every aspect of SQL Server. Let's look at Database facet:

These facet properties are used to specify a condition; A policy specifies an expression that evaluates to True or False. The expression can be made up of one or more conditions logically joined by And / Or operators.

SCENARIO:

The DBA team has released a checklist that none of the procedure names will start with 'sp_'. Now we need to create a policy to satisfy this criterion.

Let's Create a Condition

The starting point in Policy-Based Management is to create a Condition. Right click on Conditions in the SSMS Object Explorer (under the Management | Policy Management node) then select New Condition from the menu. Fill in the dialog as follows:

Select a single Facet for a Condition, then enter an Expression. The Expression evaluates to either True or False. This is the essence of Policy-Based Management which will test whether the Condition is True or False.

Let's create a policy now:

Right click Policies in the SSMS Object Explorer (under the Management | Policy Management node) then select New Policy from the menu. Fill in the dialog as follows:

The Check Condition drop down will include the list of conditions that are defined. We can check Every Database in the Against targets list and define a condition. Execution Mode can have one of the following values:

On Demand (this is the default)

On Schedule

On Change - Log Only

On Change - Prevent

The On Demand option only evaluates the policy when a user right clicks on the policy in the SSMS Object Explorer and selects Evaluate from the menu.

The On Schedule option takes advantage of SQL Agent to execute a job on a particular schedule to check the policy. After selecting On Schedule from the Execution Mode drop down list, you can click either the Pick or New button.

To pick an existing schedule, make a selection from the available options:

To create a new schedule, click New:

When policy evaluation is scheduled, any violations are logged to the Windows Event Log.

The On Change - Log Only option evaluates the policy whenever the property in the facet is changed and any violation is logged to the Windows Event Log. The On Change - Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this option uses DDL triggers to enforce the policy. Not all changes can be detected and rolled back by DDL triggers; the Execution Mode drop down list will include the On Change - Prevent option only when it is available.

What is Enabled check box? When the Execution Mode is On Demand, the Enabled check box must be unchecked; for all other options you must check the Enabled check box in order for the policy to be evaluated.

How to evaluate a Policy:

To evaluate a policy on demand, right click on the policy in the SSMS Object Explorer and select Evaluate from the menu. The following is a partial screen shot of the output from evaluating a policy on demand:

The green check icon signifies that the policy evaluated to True. Red indicate false. To view the details of error, click on VIEW link and following window will open with complete details:

Edit the policy and change the Execution Mode to On Change - Log Only. Try to create a procedure which starts with sp_. It will create the procedure, but will log the error in Event Viewer. Open Windows Event Viewer, click on Application and you will see an event that was written when the policy evaluation detected the violation:

To test the On Change - Prevent Execution Mode for a policy, create a new condition and a new policy. Follow the same steps to create the policy. The trick is that the policy must be ENABLED, only then it can stop user from violating conditions. There are 2 ways to enable a policy, one is when you create a policy and selected On Change - Prevent Execute Mode, a checkbox appears at top, just below policy name, check it. The other way is from SSMS, Right Click on Policy and select Enable.

Now whenever the user will try to create a procedure that starts with sp_, it will roll it back and shows policy violation error as follows:

This way we can have many policies running for many databases and can be used to control / monitor violations.

Do let me know if there is any feedback.

Happy SQL Coding.