Transcript sql-connections-conference-slides-policy - Data Realized

Policy Based Management
Jeremy Lowell
Data Realized
[email protected]
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
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
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
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.
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
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
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
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
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.
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
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
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
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…
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
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…
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=C6C3E9EFBA29-4A43-8D69-A2BED18FE73C&displaylang=en
●
Tutorials
• Microsoft provided tutorials.
– http://msdn.microsoft.com/en-us/library/ms167593.aspx
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
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
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!