Introduction to Policy Based Management

Thanks to Lara Rubbelke for teaching me about PBM! See the attached PBM.zip for a full slide deck and demos of the "on change" functionality.

 

Install

•       Get updated policies from the feature pack item “Microsoft SQL Server 2008 Policies”

•       In Management Studio expand your instance then Management.Policy Management.Policies.Import Policy

•       Import from C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

 

Policy-Based Management

•       Combines prior features such as

−     Agent for schedules and alerts

−     DDL triggers as event handlers

−     Best Practices Analyzer

−     Surface Area Configuration Tool

 

PBM Components

•       Facets

−     Groupings of properties that encapsulate a target such as Surface Area Configuration or Table

•       Conditions

−     States such as true/false or on/off for given settings

−     Almost anything that can be verified programmatically

•       Policies

−     Verification of a condition and the required state for a defined target(s)

 

PBM - Facets

•       Contains properties that can have conditions set

•       Example: Table facet has properties such as Name, HasClusteredIndex, IsPartitioned, CreateDate, etc.

•       Cannot be modified or added

•       Are not executed directly

 

PBM - Conditions

•       Are set for facet properties

•       Can be tested programmatically

•       Can be limited to targets such as certain databases or objects

•       Example: For the Table facet you can set @HasClusteredIndex = True

            AND @Owner = dbo

 

PBM - Policies

•       Applies a check condition against targets such as “every table”

•       Can be on demand or scheduled

−     Scheduling is through SQL Agent jobs

−     On demand has the option of "apply" for some conditions to correct exceptions

•       Can be exported then imported to other servers

•       Or execute against a group of servers in SSMS

 

PBM – "On Change " Enforcement

•       Not available for all conditions

•       On Change – Log Only

−     Allows a change away from the policy to occur but logs the change to SQL Server error log and Windows application log

•       On Change – Prevent

−     Prevents changes from occurring if they are against policy

−     Enforced through DDL triggers

 

Permissions

•       To set/change policies, add users to role PolicyAdministratorRole in msdb – note that this an attack vector as a possible elevation of privilege

•       On Schedule evaluation mode uses SQL Server agent jobs owned by SA

 

References

•       List of evaluation modes for each facet

•       Evaluating Policies On Demand Through PowerShell

•       Administering Servers by Using Policy-Based Management

•       Take Control of the Enterprise: Effective Solutions for Governing your Environment With Policy Based Management

 

 

PBM.zip

Comments

• Anonymous
  March 12, 2009
  <p class="MsoNormal" style="text-indent: -0.25in; margin: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1; tab-stops: list .5in"><font face="Calibri"><font siz ...
• Anonymous
  November 14, 2011
  Good post with a little problem : the 2nd link "Evaluating Policies On Demand Through PowerShell" is broken ( unreachable page )
• Anonymous
  November 14, 2011
  I fixed the link. Thanks for pointing it out!