Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Archive for the ‘Policy Management’ Category

Creating Policies with Policy Management

leave a comment »

One thing I really like about SQL Server 2008 is the newly introduced Policy based Management. This is a central place where policies on the server can be enforced and examined for compliance of corporate business rules – either for the current instance or across multiple servers. Examples of policies could be

  • No table can be created without a clustered index
  • Database object naming must follow development standards
  • Logins must have password policies enforced

These are just a few examples of the many facets that can be evaluated in SQL Server. Let’s review Policy based management and how it works.
Policy Management can be found within SQL Server Management Studio under the Management > Policy Management folder within the Object Explorer pane.

Figure 1 – Policy Management Folder in SSMS

Before diving directly into Policies for SQL Server 2008 there are some key terms and concepts to understand before proceeding:

  • Facet – Managed targets that have exposed properties to the user and can be evaluated within conditions. Examples of facets would be Table, Database, Server, User, Application Role, etc. In the case of a table, just a few of the facet properties exposed (and therefore can be evaluated in a condition) would be:
    – Filegroup
    – HasClusteredIndex
    – IndexSpaceUsed
    – RowCount
    – Schema
  • Conditions – These are evaluations that take place on one or more properties of a single facet. So you could have a condition that checks the “User Defined Function” facet, but within the defined condition you can be checking more than 1 property of the user defined function – such as:
    – @FunctionType = Scalar
    – @ImplimentationType = TransactSQL
  • Policies – these are the implementation of a defined Condition (contains 1 or more evaluations of a facet) applied to Facets in order to make an evaluation. So in the case of “No table can be created without a clustered index” you would create a Condition (see figure below) on the “Table” Facet which evaluates the @HasClusteredIndex property to make sure the property’s value is true.

Policies are defined using conditions, and conditions are defined using facets. So a policy is simply the “container” of a condition and the condition is the evaluations of a single facet’s properties.

Figure 2 – Creating a condition using the Table Facet

As far as I can tell you cannot create a policy that contains more than 1 defined condition – so you could not create a policy that checks more than 1 facet (and I’m not sure how helpful checking more than 1 facet at a time would be). Keep in mind the defined condition can evaluate more than 1 property of a facet – so while you can’t add many conditions to the policy, the condition you have defined can check more than 1 property of a Facet.
Using the clustered index example if you wanted to check if a table has a clustered index using a condition as well as check the naming convention of that table – both of those evaluations would have to exist in the same defined condition – rather than having a condition for “Has Clustered Index” as well as a condition for “Table Meets Naming Conventions”. They would both be defined within the same condition and used in a policy created called “Is Table in Compliance”.

Figure 3 – Multiple checks within 1 defined condition

Once the condition has been created with the properties exposed that you are interested in evaluating – you create the policy in much the same way as the condition.
By right clicking on the “Policies” folder in SSMS and then clicking “New Policy…” you are presented with a dialog box for defining the policies characteristics.
Give the new policy a name that makes sense and if you want the policy to be enabled make sure the “Enabled” checkbox has a checkmark in it. Enabling a policy in this way uses policy automation and will be reviewed in a moment. See execution mode description below for more details.

Figure 4 – New Policy dialog box

Figure 4 shows the various components of the General tab, below is a list of each component.

  1. General tab indicator – used to navigate through general setup of the policy.
  2. Description – Used to define categories of the policy, descriptions of the policy, and text to display when policies are violated, as well as created and modified meta data.
  3. Name – pretty self explanitory.
  4. Check Condition – This drop down menu contains the defined conditions on the server and also allows for creating new conditions inside the policy editor.
  5. Against Targets – used to define the targets that the policy will evalulate. In the case of our example “Is Table In Compliance”, the targets would be All Tables in All Databases.
  6. Execution Mode – On Demand, On Schedule, On Change – Log Only, On Change – Prevent.
  7. Server Restriction – This allows the policy to be evaluated against a condition defined at the server level facet. So if you were interested in only applying the policy against Development or Enterprise editions you can define a Condition on the server facet that evaluates the @EngineEdition property and set the condition evaluation value to EnterpriseOrDeveloper.

A little more about Execution Mode. As stated, there are 4 execution modes that can be assigned to the policy you create. Here is what each one does:

  • On Demand – Policy does not run unless specifically caused to do so by executing the “Evaluate” option for the policy.
  • On Schedule – The policy is run on a pre-defined schedule (defined in SQL Agent) and logs the results.
  • On Change – Log Only – Allows changes to be made – such as an out of compliance table creation, but logs the action.
  • On Change – Prevent – Any changes that violate a policy are not allowed by the server. Error messages are delivered to the client making the changes.

Once you have the General tab components configured to your liking – move to the Description tab to configure the category of the policy, description of what the policy is enforcing, and additionally add help text to display to the client when in violation of the policy. You may also enter an address that will take the user to a web site if the link is clicked after policy evaluation.

Figure 5 – Policy dialog box Description tab

If the “Execution Mode” for the policy is set to “On Change – Prevent”, the text you enter in the “Text to display” area will be returned to the client – such as Figure 6.

Figure 6 – Text to display returned to query editor

In the case of our Table In Compliance example, one thing I didn’t like was when I tried to create a table that was out of compliance using the GUI table designer, I just got an error message saying “‘tbl_testTable’ table – Unable to create table. The transaction ended in the trigger. The batch has been aborted.”.
This isn’t nearly as helpful as when I tried to create the table using the query editor – where the help text we defined was returned to me to let me know exactly why the table could not be created. In the case of the GUI editor, I have no idea why the table could not be created, all I know is it was not created and that a transaction ended in the trigger. I won’t however dwell on that as I am still using the Feb CTP and things could change in the next release.

Now that both the General tab and the Description tab have been configured we hit OK to create the policy on the server. Now that it is created it will show up in the Policies folder in SSMS. If you right click the policy you created you can click evaluate to see what objects on the server are out of compliance and try to create tables that do not meet the standard.

That’s all for now on Policy based management in SQL Server 2008.

Written by Simon Worth

June 2, 2008 at 6:45 am