Cindy Gross: Small Bites of Big Data, Small Data, All Data

Small Bites of Big Data, Small Data, All Data for Hadoop, SQL Server, Hive, Distributed Systems, Scale Out....

Introduction to Policy Based Management

Introduction to Policy Based Management

Rate This
  • Comments 3

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



       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


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


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

     Almost anything that can be verified programmatically


     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



       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



       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



  • <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 ...

  • Good post with a little problem : the 2nd link "Evaluating Policies On Demand Through PowerShell" is broken ( unreachable page )

  • I fixed the link. Thanks for pointing it out!

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post