jump to navigation

Policy Based Management in SQL Server 2008/R2 June 5, 2012

Posted by fofo in Sql Server, SQL Server 2008.
Tags: , , ,
trackback

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

Comments»

1. Dot Net Rules : Policy Based Management in SQL Server 2008/R2 - June 5, 2012

[...] (e.g naming standards) on the instances they manage but it is very difficult to enforce them. (read more) Share Posted: Τρίτη, 5 Ιουνίου 2012 3:14 μμ από το μέλος [...]

2. dinesh - January 1, 2013

great .It is working fine.but one policy condition is not then email should fired then what will email sending procedure ?.

3. low cost business - March 27, 2013

Hmm it appears like your website ate my first comment (it was
extremely long) so I guess I’ll just sum it up what I wrote and say, I’m thoroughly
enjoying your blog. I as well am an aspiring blog writer but I’m still new to everything. Do you have any suggestions for rookie blog writers? I’d definitely appreciate it.

4. outsourcing - January 27, 2014

I blog frequently and I really appreciate your information.
This great article has really peaked my interest. I am going to bookmark your
website and keep checking for new details about once
per week. I opted in for your RSS feed as well.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,787 other followers

%d bloggers like this: