THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Lara Rubbelke

Interesting Things in the World of SQL Server

Policy Evaluation on a Single SQL Server 2008 Instance

SQL Server 2008 Books Online and the Policy Based Management (PBM) blog have documented how to create a policy.  Please review these valuable resources if you are looking for an introduction to creating policies in SQL Server 2008.  Through the remainder of this series, I will be focusing on the different methods of evaluating a policy against a target (server, database, database object).  This first part will focus on policy evaluation on a single SQL Server 2008 instance.  As we journey through the series, I will deliver methods to evaluate policies across multiple servers and against down versions of SQL Server (2000 and 2005). 

As a brief introduction, SQL Server 2008 includes a new Policy Based Management (PBM) framework.  This new framework allows you to specify intent in a policy, store the policy on a server and have the server managed by that policy (note: a policy may also be stored in a file and I will touch on this later in the series).  Intent may be defined as a server configuration, database configuration, connectivity requirements, security requirements, business rules, naming conventions, and more.  A policy will identify the targets (servers, databases, database objects) that must be managed by this intent and how they are managed by the intent.  How the target is managed by the policy is dictated by the evaluation mode specified on the policy. 

I think it is very important that we set expectations:  Policy Based Management provides a level of prevention and enforcement, but the current version should be more strongly considered a framework to report and in some cases prevent and enforce compliance.  This is a tool to more easily manage deployed servers, report on the state of SQL Server, and maintain consistency and compliance to your business and regulatory requirements.

SQL Server 2008 policies can be evaluated, or invoked, in a number of ways on a single instance.  Not all evaluation modes are available to all policies.  The facet will determine the evaluation modes available for your policy.  I have pointed out the Policy Based Management blog in the past, and please spend a few minutes to review the blog on Facets.  This blog is particularly enlightening as it points directly to the relationship between each facet and the supported evaluation modes.  The team has developed a chart (hint: print this out and keep it nearby) which will be very useful when you are designing policies.

The following are available evaluation mode options when you save the policy on a SQL Server 2008 instance:

On Schedule

On Schedule will create a SQL Server agent job to execute the policy at scheduled intervals.  This option is available for all policies created for the database engine.  The results of the policy evaluation are available by viewing the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB outlined here.

Consider the following scenario:  Many organizations are challenged with maintaining a consistent deployment of SQL Server.  The process to build a new SQL Server may specify the appropriate settings, but over time these settings may be inadvertently updated.  A DBA may change an AWE setting, alter configuration settings, or alter a backup schedule - any of which can increase the time managing the server and identifying the cause of an issue.  As a result the organization either spends energy managing these configuration settings (manually reviewing periodically or building a heavily architected process to manage this process) or the enterprise has many different deployed builds in production (which leads to issues when standardizing the management of the database tier).

A policy can be deployed on these servers with an evaluation mode On Schedule.  The schedule may run once per day (for example) to determine the status of the deployed instance.  This mode will provide an ideal method for automating the evaluation of deployment standards and reporting on instances which violate these standards. 

Check on Change: Log

Check on Change: Log will write output to the event log and to MSDB when an action is executed against one of the properties that are controlled by the policy. 

For example, you may have a policy that requires xp_cmdshell is disabled.  If a DBA were to enable xp_cmdshell an event is logged indicating that the policy has been violated.  These logs may be viewed in the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB. 

Rounding out this picture, consider an alerting mechanism for violated policies that are critical.  In some cases, you may want to be immediately notified of a violation to your policy.  You may set up an alert triggered by these event logs through SQL Server or System Center which will send an email to a manager or lead administrator that an action was executed against your server that violates the policy.

Check on Change: Prevent

Check on Change: Prevent will disallow any changes that violate the policy.  When a policy is created with this option, a DDL trigger is generated on the server.  If you have multiple policies which are set to Check on Change:Prevent, they will all use the same DDL trigger. 

As an example, you may have a business rule that requires all stored procedures in a particular application database follow a consistent naming convention.  A policy is set up with this requirement and enforced on the server with the Check on Change: Prevent evaluation mode.  When a developer issues a CREATE PROCEDURE statement, the server DDL trigger is started.  SQL Server will evaluate the procedure against the conditions specified in the policy.  If the procedure violates the condition, the CREATE PROCEDURE statement is rolled back and an error is returned to the developer.  The policy will also write an event to the SQL Server event log, Windows NT Application log and the policy views in MSDB. 

On Demand

On Demand is an option where you manually run the policy.  Policies which are stored on SQL Server 2008 and evaluated On Demand are disabled and cannot be enabled.  This option is available for all policies stored on a SQL Server 2008 instance or stored as an XML file.  The results of policies executed On Demand are not written to the SQL Server Event Log, but are available in the policy views in MSDB.

Evaluating a policy On Demand on a single instance can include multiple flavors.  The most obvious is manually running the policy in SQL Server Management Studio, as in the following examples. 

  • Right-click on any policy stored in SQL Server 2008 and select Evaluate to manually evaluate the policy.
  • Right click the Policies folder in SQL Server Management Studio (SQL Server Instance>Management>Policy Based Management>Policies) and select Evaluate. The Evaluate Policies dialog will appear. By default, the policies stored on the SQL Server 2008 instance are available and you can check which policies you would like to evaluate. If the policies are stored as XML files, select the Browse(...) button. Navigate to the folder where the policy xml files are stored and select one or more policies (hold the shift key to select more than one policy).

In my next post in this series I will detail one of the lesser known (but very cool and useful) methods of evaluating a policy on demand. 

Published Friday, June 13, 2008 1:52 PM by Lara Rubbelke

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Greg Low said:

Hi Lara,

I *really* don't like the terminology "on change - prevent" when it doesn't *prevent* anything. At best, it attempts to rollback the action after it has occurred. I think the naming of this option is lousy and have posted a connect item on it a while back.

Hope you're well.



June 14, 2008 9:26 AM

Lara Rubbelke said:

In my previous blog I reviewed the different policy evaluation modes available in SQL Server 2008. In

June 19, 2008 12:51 AM

frank lee said:

hi this is a nice blog and i read some other blogs where examples are there for how to create naming convention policies.. i want to create a naming convention policy for my all SP in daatabase like SP name should start with p_NOR where NOR =Northwind means first 3 letters of database

 i use EXECUTESQL task like executesql('string','select 'p_'+substring(db_name(),1,3)+'_'')

  but still gic\ving me error..

can u give any hint abt how to use executesql or other way to create this kind of policy? THanks

March 9, 2009 10:04 PM

Leave a Comment

Privacy Statement