SQL Server 2008 Policy Management

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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read