SQL Server 2008 Policy Based Management

1. SQL Server 2008 Policy Based Management A primer for keeping your environment in shape. By Scott Abrants

2. Let Me Introduce Myself Database Administrator / Developer with RSI 15 years immersed in database technologies Specialize in database architecture, performance improvements, automation, and providing useful Business Intelligence solutions within various industries. scottabrants@gmail.com @Finnalist on twitter By Scott Abrants

3. Goals Understand the overall concept and benefits of Policy Based Management Learn some key terms and definitions How to create and run a policy scan and understand the output What to do when a policy scan detects a difference By Scott Abrants

4. What is Policy Based Management • A fully integrated system within SQL Server for managing entities and settings on SQL Server instances. • Manage pre-approved settings across your SQL environment • Database Mail Settings • Enforcing naming conventions • Manage Microsoft Best Practices for SQL Server • Default Trace Settings • Guest Permissions Settings By Scott Abrants

5. Some Key Terms – The Foundation • Managed Target: An instance of the SQL Server Database Engine, a database, a table, or an index. Can be a hierarchy, for example, all the tables in the database owned by the HumanResources schema. • Facet: A set of logical properties that model the behavior or characteristics for certain types of managed targets. • Policy-Based Management condition: A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet. • Policy-Based Management policy: A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled. • Policy-Based Management policy category: A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Policy categories apply to databases and servers. At the database level, the following conditions apply: • Effective policy: The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied: • The policy is enabled. • The target belongs to the target set of the policy. • The target or one of the targets ancestors subscribes to the policy group that contains this policy. By Scott Abrants

6. Modes • There are four types of evaluation modes • On Demand • As the name says – runs when needed / invoked • On Change Prevent • Intercepts a change and reverts that change back to a known state • On Change Log Only • Intercepts a change allows change through yet logs that change • Automated • Check process can be scheduled though SQL Server Agent Job By Scott Abrants

7. Create the process • First Step – Create the condition Conditions are items you wish to check For example – Names of database files or Database names • Name the condition • Select the facet • Create the expression By Scott Abrants

8. Create the process - continued • Create the policy that holds the facet • Select and refine the check condition • Select the evaluation role • On-demand • On-schedule • On-change prevent • On-change log only • Select server restriction • Enable the policy By Scott Abrants

9. What Next? Scripting Policies for multiple environments Robust Alerting Solutions Refining your policies Importing Best Practices Facets Questions? By Scott Abrants

10. What Have We Learned? Discussed what Policy Based Management is and how you can benefit from it. Learned the core terms and functions of Policy Based Management. Created our own policies, conditions, and categories. Reviewed how differences are alerted. By Scott Abrants