It's that time of the month again, time for the next round of T-SQL Tuesday posts. This month is being hosted by Jorge Segarra (Blog | Twitter) and we are blogging about new features in SQL Server 2008 and SQL Server 2008 R2. Plenty of scope then as this is quite a wide topic. I have posted previously on file streaming, you can see those posts here but for this post I am going to keep it simple and fresh and look at another new feature of SQL Server 2008, Policy Based Management.

I have been meaning to write something on policy based management for some time. Policy Based Management allows DBAs and administrators to apply policies or enforce rules on all the SQL Server 2008 instances they look after and ensure that a consistent approach is followed on all instances. It can be used by DBAs to enforce a 'standard' configuration across the SQL Server farm.

Where would you use it?

Well, firstly only on SQL Server 2008 and beyond. It won't work on 2005 or 2000.

There are numerous uses, one that I would have used in a previous job had it been available in earlier versions would have been to enforce some sort of naming convention. For example, you want developers to stick to some sort of naming convention, you may want the name of all stored procedures to start 'USP_' which makes identifying developer written stored procedures very easy and straight forward. Naming conventions is big long argument possibly for another day and I don't want to go into a big long discussion about this here, so let's assume you have brand new SQL Server 2008 Server, it's going to be a development instance, and you want to make sure that your developers stick to a naming convention for Stored Procedures so how do you use policy based management to achieve this?

Taken from Book Online (BOL):

Policies are created and managed by using Management Studio. The process includes the following steps:

1. Select a Policy-Based Management facet that contains the properties to be configured.

2. Define a condition that specifies the state of a management facet.

3. Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

4. Check whether an instance of SQL Server is in compliance with the policy.

How do we do this ourselves?

Connect to your SQL Server instance

Expand the management folder

and expand the policy management folder...and you will see three further sub folders: polices, conditions, and facets.

Expand the facet folder and look for a facet called “Stored Procedure”. Right click and select <properties>. You should see a list of properties, we will be using the 'name' property. You can then close this window.

Right click the Stored Procedure facet and select <new condition> give it a meaningful name..."SPName"

The Stored Procedure Facet should be selected.

In the expressions box click to add a clause.

Enter the following information:

Field: @Name
Operator: LIKE
Value: 'USP_%'

After entering this information this your condition should be ready. Click <OK>

Using the stored procedure facet we have created a condition for the Name property which specifies that stored procedure names should start with 'USP_' we now need to create a policy to enforce this condition.

Right Click the <Policies> folder and select <New policy> and we get the Create New Policy window

Give your policy a meaningful name "SPName"

Select the check condition we just created.

Specify the target databases you want enforce the policy on.

Specify the Evaluation Mode. For this we want the policy to prevent Stored Procedures so we select "On Change: Prevent"

Click on the "Description Page" on the left hand side and enter some text in the "test to display" window something like "Sorry guys, you know the rules, nice names for the SPs please."

Click OK. This creates your policy. BUT you are not done there. If you go and try and create an SP that does not follow this policy do you think SQL Server will allow you? Well yes it will, the new policy is currently in a disabled state. I think this is like this because they want you to evaluate your policy before you enable them and you can right-click and run evaluate which shows you current objects which break this policy.

So I will enable the policy by right clicking and selecting <Properties> and the ticking the <Enabled> check box.

go away and a developer tries to create an SP called '[SELECT]' and he gets this

When we change the name to ‘usp_select’ it works just fine.

That just about wraps up my TSQL Tuesday post for this month, my good friend Justin Hostettler-Davies has recently been writing a series all about the new features of SQL Server 2008. You can find his blog and his posts here.