Policy Based Management

1. Policy Based Management Jeremy Lowell Data Realized Jeremy@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.modeid=apemode.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 don’t 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_internal a INNER JOIN syspolicy_policy_execution_history_internal 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?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en • Tutorials • Microsoft provided tutorials. • http://msdn.microsoft.com/en-us/library/ms167593.aspx

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!