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

 

 

Attachment: PBM.zip
  • <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 5 and 5 and type the answer here:
  • Post