Transcript Policy categories - The Curacao SQL Server Users Group

Policy Based Management
In SQL Server 2008
Rohan Joackhim
Pinnacle Sports
Agenda

Introduction to Policy Based Management
and terminology

Get to know the implementation process
and what is under the hood.

Demonstrations of actual
implementations
What ? Why ? Who ?
What?
It’s a management feature available in SQL Server2008. Database administrators
declare their administrative intent through policies that are then applied by the
system.
Why?
 Increased complexity in database environments today





Who?




Security
Virtualization
Larger Product Offering
Proactive vs. Reactive
“Do more with less” -- “Keeping it simple”
Database Administrators
Database & BI Developers
System Administrators
Developers
Solutions for problems

Have backups been completed?

Are we in compliance with security
mandates?

Are we in compliance with best practices?

Are database management objects meeting
schema and database generation
requirements?
Basic Terminology
 Policy
 Checks or enforces the condition when fired
 Conditions
 a property expression that evaluates to True or False; i.e. the state of a Facet
 Facets
 Predetermined set of database properties we are using to manage in SQL Server 2008.
 There are 74 defined facets with different properties to create conditions.
 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.
What is a Facet ?
◦ Facets
 Facets are stored in MSDB:
 syspolicy_management_facets (management_facet_id, name,
execution_mode)
 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.
What are 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
What are Policies ?
◦ Elements of a policy
 Enabled (yes, no)
 Check Condition (singular)
 Against 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.
 Pre defined policies
 C:\Program Files\microsoft sql server\100\Tools\Policies
Evaluation Modes
Steps to implement

Select a Policy-Based Management facet that contains the
properties to be configured.

Define a condition that specifies the state of a
management facet.

Define a policy that contains the condition, additional
conditions that filter the target sets, and the evaluation
mode.

Check whether an instance of SQL Server is in
compliance with the policy.
What else important?

Policy categories
Policy categories are user defined it can organize the policies
for easy policy administration. A policy belongs to one and
only one policy category.

Permissions Required to access PBM
Users who need to access policy based management should
have PolicyAdministratorRole in MSDB database. Users in
this role have complete privilege over all the policies exists.
Users have privilege to create or edit policies and conditions,
in addition to this they will be allowed to enable or disable
the policies.
Good

Policy-Based Management provides much more control over your
database procedures as a DBA.

You will have the ability to implement your paper policies at the
database level. Paper polices are great for defining database standards
and guidelines. To strictly enforce them, you need to go over your
database with a fine-toothed comb.

Policy-Based Management, lets you define your policies and automate
them assuring that they will be enforced.
Bad

Policies can affect how some SQL Server features work.
For example, change data capture and transactional
replication both use the systranschemas table, which does
not have an index. If you enable a policy that all tables
must have an index, enforcing compliance of the policy will
cause these features to fail.
 Time
to implement policies