SQL 2008 : Policy Based Management - Building and Testing a Policy

Step by step guide to build and test a policy in SQL 2008's Policy Based Management (PBM) >

1) Locate 'Policy Management' in object explorer >

2) Right click 'Conditions' and select 'New Condition' >

3) Fill in the wizard!

Enter a name, select a Facet from the dropdown and enter expressions to be met for the condition to be met. The conditions available in the expressions box change depending on the Facet chosen >


4) Enter something useful in the description so any fellow administrators know what you've done! >


5) Create a policy to accompany (and audit/enforce) the condition >

6) Complete the New Policy wizard by
i. providing a policy name
ii. linking the policy to the condition you just defined
iii. specify the targets of the policy
iv. set the evaluation mode >



7) Again, something helpful in the description is nice >


8) Test the policy using 'Evaluate' >


9) The server has passed the policy :) >


10) Clicking 'view' under the details column reveals the specifics about the tests performed >

SQL 2008 : Policy Based Management - Basics

Policy Based Management enables you to enforce and audit standards across multiple SQL 2008 instances through user defined rules (policies).

3 new terms are associated with Policy Management.

1. Policy - A group of conditions to be checked/enforced
2. Conditions - State of Facets (true/false).
3. Facet - Manageable properties of SQL object.

Policy Based Mamagement (PBM) is found under 'Management on a SQL 2008 instance >

There are currently 84 'Facets' you can define policies for -


It's easy to list them via TSQL -

use msdb
select name from dbo.syspolicy_management_facets
go

or you can list all the facets and the target types and they can be applied to >

use msdb
select   f.name as facet_name
  ,fe.event_name
  ,fe.target_type
  ,fe.target_type_alias
from dbo.syspolicy_management_facets f
inner join dbo.syspolicy_facet_events fe
on f.management_facet_id = fe.management_facet_id
go

Useful PBM Links:

Getting a list of all Facets and their properties
http://sql-articles.com/blogs/policy-based-management-pbm/
http://blogs.msdn.com/sqlpbm/
http://www.mssqltips.com/tip.asp?tip=1492

sp_syspolicy_purge_history

On new sql 2008 installs there is a SQL Server Agent job called ' sp_syspolicy_purge_history '.


It is a cleanup routine for Policy based Management in SQL 2008 and simply executes msdb.dbo.sp_syspolicy_purge_history .

Nothing to worry about!