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.

SQL Server 2008 Policy Management

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.

Comments

There are no comments yet. Be the first to comment!

You must have javascript enabled in order to post comments.

Leave a Comment

Your email address will not be published. All fields are required.

Name

Email

Title

Comment

Top White Papers and Webcasts

The 2014 State of DevOps Report — based on a survey of 9,200+ people in IT operations, software development and technology management roles in 110 countries — reveals:
Companies with high-performing IT organizations are twice as likely to exceed their profitability, market share and productivity goals.
IT performance improves with DevOps maturity, and strongly correlates with well-known DevOps practices.
Job satisfaction is the No. 1 predictor of performance against organizational …

Today, users, applications, and data exist in more places than ever before, creating an unprecedented challenge for IT. How can IT achieve the flexibility and agility it needs to offer multiple types of applications in multiple locations? To better serve business demands for information everywhere, enterprises must develop new strategies for optimizing multiple kinds of networks. Read this white paper to learn how hybrid networks provide an unprecedented level of network dynamism, enterprise agility, and the …