Policy Based Management in SQL Server 2008/R2

In this post I would like to about a new feature in SQL Server 2008, namely Policy-Based Management.With this new feature DBAs can enforce policies to an instance of SQL Server or many instances of SQL Server they are responsible for.

Most DBAs  have to administer many SQL Server instances.It is not efficient to configure and manage them separately.DBAs try to enforce standards (e.g naming standards) on the instances they manage but it is very difficult to enforce them.

Policy-Based Management (PBM) is available in SQL Server 2008 Enterprise and Standard edition.

As it was mentioned earlier PBM allows you to define and enforce policies on SQL Server.I will show you with a hands-on example  how to achieve that.We will create a simple policy that will enforce a naming convention (All user defined stored procedures for all the databases in the server must begin with the prefix usp )

We need an instance of SQL Server running in our machine. I have SQL Server 2008 R2 Developer edition installed in my machine.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it here . This is a trial version.

There are a number of terms that we need to define in order to begin to understand Policy-Based Management:

1. Target – an entity (a database, a table, an index) that is managed by PΒΜ
2. Facet – a predefined set of properties that can be managed
3. Condition – a property expression that evaluates to True or False, e.g the state of a Facet
4. Policy – a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS).

1) Launch SQL Server Management Studio and log in to the local instance of the SQL Server.

2) Navigate to the Object Explorer and expand the Management node and the Policy Management node.Υou will see the Policies, Conditions, and Facets nodes.

3)Right-click on Policies node and then select New Policy.Give your policy a name.

Have a look at the picture below

Then in the Check Condition dropdown click New Condition,fill in the name and the expression.Have a look in the picture below.

and then Click OK.

4) In the evaluation mode we select “On change:prevent” .This means that if some user makes a change that will cause a policy to evaluate to false, then this action will be prevented.

Have a look at the picture below

Finally we click OK on the policy window.

5)Now we need to test the new policy.In an new query window type

CREATE PROCEDURE test_SP1
 AS

PRINT 'this will violate a policy'

When you try to execute the procedure above (for any database in the server) you will receive an error message from SQL Server since there is a policy violation.

Have a look at the picture below

At this point I hope you have a good understanding of what PBM can do for enforcing policies in an SQL Server instance.

Hope this helps!!!!

About these ads