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 FilesMicrosoft SQL Server100ToolsPoliciesDatabaseEngine1033
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