Wednesday, May 6, 2009

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

No comments: