Policy Based Management in SQL Server 2008

Introduction

How many of us like RULES? Imagine standing in a queue to buy movie tickets, wouldn’t it be much better if we could simply jump to the first place near the ticket window? As a kid, I remember my dad saying “Go to bed at 10, no late nights”, and I felt awful and questioned why I couldn’t watch a late night movie. What about the policy of your organization to wear casuals on Fridays? Thank God, we can work in jeans at least for a day in the office! If I look up the word policy in the dictionary, it says “The set of basic principles and associated guidelines, formulated and enforced by the governing body of an organization, to direct and limit its actions in pursuit of long-term goals.” Why does the governing body (country/organization/even dad) need to enforce rules and guidelines? To this, they’d easily say, “To bring discipline into our lives”. To progress, one needs discipline, and policies make sure that people are disciplined.

What about discipline amongst databases hosted on a server? MS SQL Server 2008 provides a very important feature for policy based administration. In this article, we shall look into configuring, monitoring, and evaluating policies. Let’s assume that a DBA in an organization sets up the guidelines for developers; he says that every user defined Stored Procedure should be prefixed with ‘usp_’ (e.g., usp_TestProc), or that a user defined table should be prefixed with ‘tbl_’ (e.g., tbl_TestTable). Till SQL Server 2005, DBAs had to manually check that a developer’s code met his prerequisites, but SQL Server 2008 has made things easier. SQL Server 2008 provides a very efficient SQL policy administrator which constantly monitors the server and reports errors appropriately. Administrators can define policies and ensure that they are being adhered to by evaluating the policies:

• On demand
• On schedule
• On change: Log only
• On change: Prevent

Policy Management: A closer look

On opening SSMS under the management folder, we can find Policy Management. Policy Management allows creating policies for various facets with a specified condition.

• Facets: Facets is the property of SQL Server which the policy will consider managing. There are several facets on which policies could be implemented. For example, we will use the “Database Option” facet to implement a policy which will ensure that the AutoShrink option should be TRUE for all hosted databases on the server. Similarly, we will be creating policies on the Stored Procedure facet.



• Conditions: It is the criteria upon which the facet is evaluated. While designing a policy for the server, the first step is to create a condition which is to be evaluated by the policy for the facet.
• Policies: As the dictionary says, I reform, a SQL Server policy is a set of basic principles and associated guidelines, formulated and enforced by the Policy Manager of a server, for the desired server facets to conform with, which in the long run shall maintain the server consistent and help the DBA achieve organizational level IT norms.

Example 1

Scenario: We will create an on demand policy to ensure that all the databases have the Auto Shrink option set to True. By default, a database that is created has Auto Shrink set to False, as shown in the figure below.

Step 1: Creating a Condition

Right click on Conditions and select New Condition…

Next, provide a name to the Condition: “Check Auto Shrink”, and select the facet from the Facets drop down as “database option”. In the Expression Editor, choose the field from the drop down “@AutoShrink”, select operator as “=”, and value as “True”.

The condition will check all databases for their auto shrink properties to be true.

Click OK.

Step 2: Create a Policy

Right click on Policies and select New Policy…

Provide a name as “AutoShrinkPolicy”; from the Check condition drop down, select the Condition we just created. And from Targets, check every database as we want every database to conform to this policy.

Next is the evaluation mode. Let’s keep it “On demand” for this example. On demand means we will evaluate the policy at our will instead of at a predefined schedule.

Click OK.

We are all set, the policy is in place.

Step 3: Evaluation

We have been able to create the policy; now we will let the Policy Manager evaluate the policy. To evaluate, right click the Policy “AutoShrinkPolicy” and click Evaluate. SQL Server evaluates and lists the result as shown in the screenshot below. Since for none of my databases Auto Shrink is True, there are non-conformations for each one of the hosted databases on my server.

For conforming the results as per the Policy, check against the database and click on the Apply button.

This will set the Auto Shrink property for TestDB to True and a green sign will denote its conformance.

Example 2

Scenario: The scenario is ensuring that each user defined Stored Procedure created on the server is prefixed with ‘usp_%’. Let’s design a policy for this.

Step 1: Creating a Condition

Right click on Conditions and select New Condition…

Next, provide a name to the condition “CheckProcName”, and select the facet from the Facets drop down as “Stored Procedure”. In the Expression Editor, choose the field from the drop down “@Name”, select operator as “LIKE”, and value as ‘usp_%’.

The condition will check the names of all the Stored Procedure to be prefixed with usp_.

Click OK.

Step 2: Create a Policy

Right click on Policies and select New Policy…

Provide the name as “ProcPolicy”; from the Check condition drop down, select the condition we just created, i.e., CheckProcName. And from Targets, check every Stored Procedure in every database as we want every Stored Procedure to conform to this policy.

Next is the evaluation mode. Let’s keep it “On Change: Prevent” for this example. On Change: Prevent will evaluate the policy for any further procedure creation, and will prevent it from being created unless it is as per the policy.

Also, do remember to check the Enabled check box, and click OK.

Step 3: Evaluation

We have been able to create the policy, now let the Policy Manager evaluate the policy. To evaluate this policy, open the SSMS query analyzer and try to create an SP under any database.

CREATE PROCEDURE TestProcPolicy
AS
BEGIN
PRINT 'HELLO WORLD !'
END

On executing the procedure, we receive the below error:

Policy 'ProcPolicy' has been violated by 
   'SQLSERVER:\SQL\Tk99-1342-311\DEFAULT\Databases\
   master\StoredProcedures\dbo.TestProcPolicy'.
   
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp_%''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE PROCEDURE TestProcPolicy
AS
BEGIN
PRINT 'HELLO WORLD !'
END'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

Let’s try to re-execute the SP with:

CREATE PROCEDURE usp_TestProcPolicy
AS
BEGIN
PRINT 'HELLO WORLD !'
END
----
Command(s) completed successfully.

With these two examples, I hope I have been able to explain Policy Management at the beginner’s level.

Summary

To summarize, policies are made to ensure that rules are followed and guidelines are adhered to. Till SQL Server 2005, policy implementation was a mammoth task which DBAs had to do manually (with the help of triggers etc.), but the introduction of SQL Server 2008 has made the the lives of DBA’s much easier (literally). Creating and implementing policies has become much easier, faster, and more reliable. All that’s required to do is to first create a ‘Condition’ which is nothing but the guideline or rule that has to be followed, and then create the policy to adhere to this condition, and voila we have our Policy. Policies can be created on various ‘Facets’ of the database, e.g., database, table, Stored Procedure, etc.

Policies can be on demand, or scheduled, or on change (log only or prevent).

Before I end this article, one FAQ:

Someone asked, using policy management we can restrict users to follow a particular naming convention for procedures, that’s awesome. But we can’t do the same thing for table names. I tried to set up this one but only got two evaluation modes (on schedule and on demand). I didn’t get on change: prevent, is there a way to implement this in Policy Management?

Yes sir, there is :)

For example, if I want to create a Policy to restrict table naming, I could use facets like:

1. Table
2. Name
3. MultiPart Name etc.

While the first two don’t provide the option of “on change: prevent”, the last one does, giving the option to prevent the naming of the table which doesn’t adhere to the policy in place. The key is to explore; there are a lot of facets in place but figuring out which suits the bill needs a piece of thought.