Policy Based Management

1
Policy Based Management

• Jeremy Lowell
• Data Realized
• Jeremy_at_DataRealized.com

2
Agenda

• Policy Based Management Overview
• Why was it included in 2008?
• Who is it for?
• What is it?
• Terminology and concepts around Policy Based
  Management
• In practice
• DDL Change Demo (Prevent)
• Best Practices Demo
• Questions

3
Overview

• Policy Based Management
• Why?
• Increased complexity in database environments
  today
• Security
• Virtualization
• Larger Product Offering
• Proactive vs. Reactive
• Do more with less -- Keeping it simple
• Who?
• Database Administrators
• Database BI Developers
• System Administrators
• Developers

4
Policy Management

• What is it?
• Policy-Based Management is a policy-based system
  for managing one or more instances of SQL Server
  2008. Use this with SQL Server Management Studio
  to create policies that manage entities on the
  server, such as the instance of SQL Server,
  databases, and other SQL Server objects.
  Microsoft
• Components
• Policy Management
• Creation of conditions and policies
• Explicit Administration
• Explicit administration of one to many managed
  targets
• Automated Administration
• Automate enforcement of policies

5
Policy Management

• Terminology
• Facets
• Predetermined groups to create conditions against
• There are currently 74 defined facets with many
  properties to create conditions against
• Conditions
• A self defining attribute which is used in
  conjunction with one or more policies
• Policy
• Checks or enforces the condition when fired
• Target
• An object or entity that is managed by a Policy
• Managed Target
• Entities
• SQL Engine, Database or Object (Table, view etc)
• Hierarchal based. (targets within an instance).
• Set Targets can have a defined set, such as
  schema.

6
Policy Based Management

• Primary Policy Based Management Components
• Policy Management
• Creation of Policies
• Explicit Administration
• Explicitly check policies
• Evaluation modes
• Four ways to evaluate
• On Demand
• On Change Prevent
• On Change Log Only
• On Schedule

7
Policies

• Three primary components to a Policy
• Facets, Conditions Policies
• Facets
• Predefined set of Facets.
• Based on properties of characteristics for
  specified functionality.
• A target type can implement one or more
  management facets, and a management facet can be
  implemented by one or more target types.
• Facet availability are version (SQL Server)
  dependent.
• Facets are stored in MSDB
• syspolicy_management_facets

8
Facets

• Execution mode per facet
• with automatedpolicyexecutionmode (modeid,
  modename)
• as
• (select from (values (0, 'On Demand'),
• (1,'Enforce Compliance'),
• (2, 'Check on Change and Log'),
• (4, 'Check on Schedule and Log'))
• as em(modeid, modename))
• select dmf.management_facet_id as facetid,
  dmf.name as facetname, apemode.modename
• from syspolicy_management_facets as dmf
• inner join automatedpolicyexecutionmode as
  apemode on dmf.execution_mode
  apemode.modeidapemode.modeid
• order by dmf.name, apemode.modename

9
Conditions

• Elements of a condition
• Expression or Boolean
• Based on the attribute of the facet chosen
• Condition can be used for many policies
• Only one facet per condition
• Property (of a facet)
• Operator
• , !, Like, In, NotIn, NotLike, NotIn
• Value
• Multiple expressions can be used with and/or
  logic
• Expressions can be grouped
• Description
• Optional value

10
Policies

• Elements of a policy
• Enabled (yes, no)
• Condition (singular)
• Targets
• Can define or use a defined condition to limit or
  specify Targets
• Evaluation Mode
• On Demand
• On Schedule
• On Change Log Only
• On Change Prevent
• Server Restriction
• Can define or use a defined condition
• Category
• Create or Assign the category for the policy
• Description
• Optional Will display with error message when
  violated.
• Additional Help
• Includes additional text to display and an
  optional URL.

11
Demo

• DDL Naming Convention
• Problem
• Standards are great
• Automatic enforcement is better.
• Auditors requests
• Simple to satisfy
• Complex environments
• Known state
• Developers write perfect code
• Ego boost

12
Demo

• DDL Demo
• Facet
• Multipart Name
• Applicable Targets (One, some, all)
• Stored Procedure, Synonym, Table, User Defined
  Function, User Defined Type, View, Xml Schema
  Collection
• Condition
• Create conditions for the following items
• Two main concepts
• Object MUST contain to comply to xyz (as the
  condition specifies)
• Object MUST NOT contain or comply to xyz
• Policy
• Create Policies
• Assign Targets
• Choose evaluation mode
• On Change Prevent
• Execute DDL statements
• Those that comply
• Those that dont comply

13
Demo

• DDL Demo
• Condition
• Create conditions for the following items
• Object Name not like tbl
• OR
• Object Name not like sp
• OR
• Object Name not like vw
• AND
• Object Name like UserTable
• OR
• Object Name like PRC
• OR
• Object Name like UserView
• First three expressions are Grouped
• Last three expressions are Grouped

14
Demo

• DDL Demo
• USE MSDB
• GO
• SELECT a.execution_date AS 'Date Run , c.name AS
  'Policy ,
• a.target_query_expression AS 'Policy Failure
  Targets , d.name as 'Condition,
• d.description as 'Condition Description, d.facet
  as 'Condition Facet
• FROM syspolicy_policy_execution_history_details_in
  ternal a
• INNER JOIN syspolicy_policy_execution_history_inte
  rnal b ON a.history_id b.history_id
• INNER JOIN syspolicy_policies_internal c ON
  b.policy_id c.policy_id
• INNER JOIN syspolicy_conditions d ON
  c.condition_id d.condition_id
• WHERE a.result 0
• ORDER BY a.execution_date DESC, c.name ASC,
  d.name ASC
• Date Run Policy
  Policy Failure Targets Condition
  Condition Description Condition Facet
• ------------ -------
  ----------------------------
  ------------ --------------------------
  --------------------
• 2008-10-21 Standard Naming Convention
  SQLSERVER\SQL\... Naming .. These character.
  IMultipartNa

15
Demo

• Best Practices
• Problem
• Standards are great
• Automatic enforcement is better
• Auditors requests
• Simple to satisfy
• Complex environments
• Known state
• System Administrators build perfect environments
• Ego boost

16
Demo

• Best Practices
• Microsoft provided policies
• Auto Shrink
• SQL Server Max Degree of Parallelism
• SQL Server Password Policy
• Lightweight pooling
• SQL Server Login Mode
• Version (all)
• Etc

17
Demo

• Best Practices
• Microsoft add-on
• Provides roughly 50 pre-defined policies and 70
  pre-configured conditions
• Microsoft SQL Server 2008 Feature Pack, August
  2008
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyIdC6C3E9EF-BA29-4A43-8D69-A2BED18FE73Cdispla
  ylangen
• Tutorials
• Microsoft provided tutorials.
• http//msdn.microsoft.com/en-us/library/ms167593.a
  spx

18
Management

• Group Management
• Categories
• Policies
• Subscription
• Databases can subscribe to categories
• Server Groups
• Registered Servers
• Manage Server group
• Exporting Policies
• XML
• Import Policies
• Maintains state

19
Conclusion

• Policy Based Management
• Why?
• Increased complexity in database environments
  today
• Security
• Virtualization
• Larger Product Offering
• Proactive vs. Reactive
• Do more with less -- Keeping it simple
• Who?
• Database Administrators
• Database BI Developers
• System Administrators
• Developers

20
Your Feedback is Important

• Please fill out a session evaluation form and
  either put them in the basket near the exit or
  drop them off at the conference registration
  desk.
• Thank you!