Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Using Policy Based Management in SQL Server 2008

MSSQLTips author Ray Barley By:   |   Read Comments (11)   |   Related Tips: More > Policy Based Management

Problem
As we are reviewing the new features in SQL Server 2008, we found one that looks really interesting - Policy-Based Management.  Could you help us to understand how this works and provide some examples?  Can you please explain each of the components and how to manage them in the interface and with commands?

Solution
Policy-Based Management is indeed a new feature in SQL Server 2008.  It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.  Originally this feature was called the Declarative Management Framework but has since been renamed.  There are a number of terms that we need to define in order to begin to understand Policy-Based Management:

  • Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
  • Facet - a predefined set of properties that can be managed
  • Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
  • Policy - a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS).  Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes:

Expand the Facet node to see the list of facets:

As you can see there is a rather comprehensive collection of facets predefined in SQL Server 2008, allowing you to manage just about every aspect of SQL Server.  Double click on a facet to see the actual list of properties in the facet; e.g. double click the Database facet:

These facet properties are used to specify a condition; e.g. AutoShrink = False means that you do not want to automatically shrink database files.  A policy specifies an expression that evaluates to True or False.  The expression can be made up of one or more conditions logically joined by And / Or.

In this tip we are going to gain an understanding of Policy-Based Management by walking through the following demonstration:

  • Create a Condition
  • Create a Policy
  • Evaluate a Policy

The demo steps below were only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Create a Condition

The starting point in Policy-Based Management is to create a Condition.  Right click on Conditions in the SSMS Object Explorer (under the Management | Policy Management node) then select New Condition from the menu.  Fill in the dialog as follows:

You select a single Facet for a Condition, then enter an Expression.  The Expression evaluates to either True or False.  This is the essence of Policy-Based Management which will test whether the Condition is True.

Create a Policy

Right click Policies in the SSMS Object Explorer (under the Management | Policy Management node) then select New Policy from the menu.  Fill in the dialog as follows:

The Check Condition drop down will include the list of conditions that you have defined.  You can check Every Database in the Against targets list, or you can click the glyph (between Every and Database) and define a condition.   Execution Mode can have one of the following values:

  • On Demand (this is the default)
  • On Schedule
  • On Change - Log Only
  • On Change - Prevent

The On Demand option only evaluates the policy when a user right clicks on the policy in the SSMS Object Explorer and selects Evaluate from the menu. 

The On Schedule option takes advantage of SQL Agent to execute a job on a particular schedule to check the policy.  After selecting On Schedule from the Execution Mode drop down list, you can click either the Pick or New button.

To pick an existing schedule, make a selection from the available options:

To create a new schedule, fill in the familiar schedule dialog:

When policy evaluation is scheduled, any violations are logged to the Windows Event Log.

The On Change - Log Only option evaluates the policy whenever the property in the facet is changed and any violation is logged to the Windows Event Log.  The On Change - Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this option uses DDL triggers to enforce the policy.  Not all changes can be detected and rolled back by DDL triggers; the Execution Mode drop down list will include the On Change - Prevent option only when it is available.

One final note on the policy setup concerns the Enabled check box.  When the Execution Mode is On Demand, the Enabled check box must be unchecked; for all other options you must check the Enabled check box in order for the policy to be evaluated.

Evaluate a Policy

To evaluate a policy on demand, right click on the policy in the SSMS Object Explorer and select Evaluate from the menu.  The following is a partial screen shot of the output from evaluating a policy on demand:

 

The green check icon signifies that the policy evaluated to True for the databases shown.  Not shown above is a Configure button that allows the user to automatically fix a target where the policy evaluates to False.

Right click on a database in the SSMS Object Explorer and select Properties from the menu.  Click the Options page and change the AutoShrink property to True.  Evaluate the policy again and you will see the following output:

Note the red icon with the X indicating that policy evaluation failed for a particular database.  Not shown above is the Configure button which you can click to automatically change the AutoShrink property to comply with the policy.

Edit the policy and change the Execution Mode to On Change - Log Only.  Select a database and change the AutoShrink property to True.  Open the Windows Event Viewer, click on Application and you will see an event that was written when the policy evaluation detected the violation:

To test the On Change - Prevent Execution Mode for a policy, create a new condition and a new policy.  Create a new condition as follows:

Now create a new policy as follows:

This policy will prevent a table from being created if the table name does not begin with 'tbl_'.  Open a New Query window in SSMS and enter a create table script.  When you execute the CREATE TABLE script you will get the following error message and the table will not be created:

CREATE TABLE sample (
message varchar(256)
)
Policy 'Table Prefix Must Be tbl_' has been violated by 
'/Server/(local)/Database/demo/Table/dbo.sample'.This 
transaction will be rolled back. Policy description: 
''Additional help: '' : ''. Msg 3609, Level 16, State 1, 
Procedure sp_syspolicy_dispatch_event, Line 50
The transaction ended in the trigger. The batch has been aborted.

Next Steps

  • Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site.  The above examples were created using the February, 2008 CTP.
  • Review the SQL Server 2008 Books on Line content for Policy-Based Management for additional information.


Last Update: 5/6/2008


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, July 23, 2012 - 2:19:41 AM - hamedYekta Read The Tip

Hi dear

thanks you for this article .

please explain me how to create policy that user can not any change on table definition .

 

 


Monday, July 23, 2012 - 7:32:25 AM - Ray Barley Read The Tip

I don't think you can do it with policy; policy only deals with database-level options.

You can use a DDL trigger; here's an example from SQL Server documentation that you could use to prohibit drop table and alter table:

CREATE TRIGGER safety 

ON DATABASE 

FOR DROP_TABLE, ALTER_TABLE 

AS 

   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 

   ROLLBACK

 

Take a look at this tip if you want to examine the details of the event that fired the trigger:

http://www.mssqltips.com/sqlservertip/1571/using-the-eventdata-function-with-ddl-triggers-in-sql-server-2005/


Monday, August 06, 2012 - 11:38:51 AM - Mike Dyson Read The Tip

Is there a way to prevent certain applications from connecting to a database? For example, if we want our reporting users to connect only using SSMS and not MS Access, can we set a condition where application name is not like '@office@'  ?

I have seen system triggers that could handle this, but I thought this would be a cleaner way to do the same task.

Thanks!!


Monday, August 06, 2012 - 2:17:08 PM - Ray Barley Read The Tip

I don't see anything in policy based management that allows you to check the application name in the connection string.  

 


Wednesday, August 08, 2012 - 11:41:07 AM - Mark Ganci Read The Tip

Thanks Ray for the informative article.

I have checked SQL 2008 R2 and SQL 2012 and found that the PMB excution option "On Change-Prevent" does not exist for Facet: Table.

Documentation and the  table msdb..syspolicy_management_facets also seem to confirm this observation.

How were you able to set yours as such in the above screen shots?

Thanks

Mark

 

 


Thursday, August 09, 2012 - 6:36:03 AM - Ray Barley Read The Tip

This tip was written based on a CTP version of SQL Server 2008.  It looks like the on change-prevent no longer exists.


Tuesday, December 11, 2012 - 4:01:38 AM - Shoaib Khot Read The Tip

Hi Ray,

What is the alternative for 'On Change' conditions in SQL Server 2008R2 edition? I'm trying to add a condition for stored prc. prefix and On Demand and On-Schedule conditionn would be of no use as users would still be able to bypass the policy.

Please advise.


Tuesday, December 11, 2012 - 4:35:44 AM - Shoaib Khot Read The Tip

I just found out that 'On Change: Prevent' condition is available only for 'Table Options' facet and not for 'Table' :)

Couldn't find much stuff on this over the web..


Monday, April 22, 2013 - 6:01:01 AM - Rasika Ogale Read The Tip

Am Trying to crate conditions and policies, with above ref document. Same steps i floowed but while creating new policy

Enabled button is disable. Any how it is not get enabled. As result my policy is showing as Enabled as False

Please help mi so sole this issue


Monday, April 22, 2013 - 7:12:14 AM - Rasika Ogale Read The Tip

With the following above steps , policy and conditions are created and try to create table in Management Studio 

I got following error : 

 

 

Msg 6522, Level 16, State 1, Procedure sp_execute_policy, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_execute_policy": 

System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Diagnostics.STrace, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

System.IO.FileNotFoundException: 

   at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper..cctor()

System.TypeInitializationException: 

   at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper.EvaluateAutomatedPolicy(String policy, SqlXml eventData, Int64& historyId)

   at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationWrapper.EvaluateAutomatedPolicy(String policy, SqlXml eventData, Int64& historyId)


Monday, April 22, 2013 - 8:34:54 AM - Raymond Barley Read The Tip

Check this: http://support.microsoft.com/kb/2640110 See if the workaround resolves your issue



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.