Introduction

With the introduction of Microsoft SQL Server 2008 a number of new features where added to control your SQL Server instances. One of these new controlling features is Policy Management. Policy Management allows you to define policies (rules) that report and/or enforce how your instance can be set up and used. In this article I will show you how to define policies and use these to better manage your SQL Server 2008 instances.

What is Policy Management?

Policy Management is a new management paradigm where DBAs can define rules for how their SQL Server instances are to be configured, and used. A defined rule is known as a Policy. Each policy identifies a facet and condition to identify the rules that the policy is to report or enforce.Also a DBA can define how each policy should be applied (more on this later).

A facet is a component within SQL Server, like a Stored Procedure, a login, a table, etc. There are lots of different types of facets against which you can write policies. The screen shot below shows a number of these facets, but not all:

A condition is just what it sounds like. It identifies the condition you want to test your facet against to determine whether the policy passes or fails. A condition can only be defined against a single facet. Within a condition you define expressions against one or more properties of a facet. Each expression when tested against a facet property will either be true or false. You can string each expression together with a series of AND's and OR's.Based on the results of the expression tests will determine whether or not your policy check passes or fails.

Now that you have a basic understanding of the components that makeup a policy let me show you a demonstration of creating a simple policy, by first creating a condition.

Creating a Condition

To create new policies expand the "Management" folder within SQL Server 2008. Under this folder you will find a "Policy Management" folder. If you expand this folder you will see three folders title: Policies, Conditions and Facets.

Before you can define a new policy, you need to determine what kind of policy you want to write. So for the purpose of this demo, I'm going to build a real simple policy to control the names of my Stored Procedures. I want my policy to check procedure names and make sure they don't start with "sp_".

The first step to defining a policy is to create a condition against the stored procedure facet. Just to let you know out of the box there are no conditions identified in SQL Server 2008. You will need to define all the conditions you might need for all your different policies. To create a new condition right click on the "Conditions" item and select "New Condition...". This will bring up the following dialog box:

Here you can see the first thing you need to do is define a name. You should define your condition names so they are descriptive. I'm going to define my condition name as "Verify that user Stored Procedures don't start with sp underscore".

The next thing to do is determine which facet the policy will be defined against. If you look at the screen shot above you can see there is a drop down menu on the "Facet:" item. Currently the facet is set to "Application Role". By using the drop down I need to select the "Stored Procedure" facet.

Next I need to define an expression that will test stored procedure names to make sure they don't start with "sp_". The first step in the process is to select the "Field" that I need to test. I do this by clicking on the ellipse (...) next to the "Field" column in the "Expression" section of the "Create New Condition" dialog box. When I click on the ellipse button I have a choice to choose from a number of different properties associated with the "Stored Procedure" facet. For my condition I select the "@Name" facet. The next step is to determine the type of "Operator" I need to use to test the "@Name" field for a valid value. Since I want all my stored procedures to not start with "sp_" I select the "NOT LIKE" operator. The last thing to identify for my condition is the "Value" I want to test against. In my case I enter "'sp_%'" for my value criteria. Below you can see the final definition for my "Verify that user Stored Procedures don't start with sp underscore" condition:

One thing worth noting here is you can have multiple expression identified for your condition. This allows you to create a much more complicated conditions then the one I just defined.

If you want you can click on the "Description" item in the left pane of the above window to enter a description for your conditions. In my case this condition is very straight forward so I will not be entering a description. If your condition is complicated you might consider adding a description to help to document the intent of your condition.

Now to save the definition of my condition I just need to click on the "OK" button at the bottom of the dialog box. Now that my condition has been created I can move on and create my policy.

Creating a Policy

To create a policy I will right click on the "Policies" item under the "Policy Management" item in the Object Explorer tree, and then select the "New Policy..." item. Doing this brings up the following dialog box:

[pilcy4.jpg]

Here I define how I would like my policy to be configured. The first thing I do is enter the name of my policy. For my policy I will call it "Enforce User Stored Procedure Naming Standards".

Next I need to identify my "Check condition". To do this I click on the drop down menu button and select the condition I just created in the prior section. When I do that the "Against target:" section is populated with some default target information. If you look at the screen shot below you can see those default values:

[default5.jpg]

As you can see I can take the default target setting, which says this policy will run against every stored procedure and every database. Or I can specify something different for "StoredProcedures" and/or "Databases". To specify something different for "StoredProcedures", I hover the mouse over the little down arrow that is just left of the phrase "StoredProcedure", than the following menu comes up:

[stored6.jpg]

Here I can select an existing condition, of create a new condition to identify just the stored procedures I want. For my case I want this policy to go across every database and stored procedure. If I want to specify just a specific database then I could also click on the little down arrow next to database to build a condition that would identify just a specific database I wanted my Policy to run against.

The next thing to consider is defining the "Evaluation Mode" for my policy. The options are: On demand, On schedule, On change: Log Only, and On change: Prevent. If you create a policy and set the "Evaluation Mode" to On Demand then you can manually run the policy to verify the facets are configured as specified by the policy. Use the "On Schedule" option means the policy will be evaluated based on a specified schedule. The "On Change: Log" option says just log the changes to the event log and the SQL Server error log. Whereas the "On Change: Prevent" actually prevents people from doing something on your SQL Server instance that would violate the policy.

In my case I want to keep people from creating stored procedures that start with "sp_".Therefore I want to setup my policy to use "On Change: Prevent" for the "Evaluation Mode".

The last setting for a policy is "Server restriction". This property is used to identify which type of servers to which the policy should be applied. For my demo I'll specify none.

To complete my policy I need to determine whether or not to enable my policy. Since I want my policy to prevent people from actually creating stored procedures that start with "sp_" I do want to enable my policy. I do this by check the "Enable" check box.

Here is a screen shot of my completely filled out policy definition:

[enable7.jpg]

Evaluation a Policy against your environment

Evaluating a policy is easy once the policy has been created. Remember there are a number of different evaluation modes that can be identified: On demand, On schedule, and On change. Depending on the evaluation mode will identify when the policy will be evaluated.

When you create a policy that has been setup with "On demand" as the evaluation mode you will need to manually evaluate those policies. To manually evaluate a policy you would right click on the policy name you want to evaluate and then select the "Evaluate" option from the drop down. Note that all policies regardless of how their evaluation mode is set can be evaluated manually using this menu method. Also policies that are not enabled can also be evaluated this way.

If a policy is setup to be evaluated on a schedule then a SQL Server Agent job will be created to evaluate the policy.The agent job will be run based on the schedule you identify when you set the evaluation mode of "On schedule". When this SQL Agent job run it will validate whether or the facet identified in your policy violates the policy.

The "On change" evaluation mode will evaluate a policy any time the facet associated with the policy is changed. If a facet change does not return all true conditions as defined for the policy's "Check condition" then the facet change is enforced. It will either log the change for "On change: log" or log and prevent he change from occurring if the evaluation mode is "On change: Prevent. This will keep people from modifying facets to a state different then allowed by a policy.

Controlling your instance using Policy Management

Policy Management is a great enhancement for DBA. With Policy Management DBAs now have a way control their instances by creating conditions that run against facets that can all be wrapped up into a policy. By defining a series of policies and enabling them you are able to make sure your instance configuration, and objects meet all the defined set of policies. If you are a DBA you should look into Policy Management and start developing those policy that wall allow you to better control your SQL Server instances.