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.