1 / 14

Policy Based Management In SQL Server 2008

Rohan Joackhim Pinnacle Sports. Policy Based Management In SQL Server 2008. 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?

aleda
Download Presentation

Policy Based Management In SQL Server 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Rohan Joackhim Pinnacle Sports Policy Based ManagementIn SQL Server 2008

  2. Agenda Introduction to Policy Based Management and terminology Get to know the implementation process and what is under the hood. Demonstrations of actual implementations

  3. 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 • 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. 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?

  5. 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.

  6. 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.

  7. 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

  8. 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\microsoftsql server\100\Tools\Policies

  9. Evaluation Modes

  10. 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.

  11. 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.

  12. 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.

  13. 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.

  14. Time to implement policies

More Related