SQL SERVER – Introduction to Policy Management

As March comes, most of us are trying to chase our dreams and hope the much awaited yearend review comes good for us. I know many pray and don’t realize it is just an assessment of what was done the whole of last year. It is not a myopic view, but a view that is based of rules on how consistent we have been the complete year. These rules and policy decisions are generally governed by how our organizations work and how policy decisions are made in each of the region where you work. Though we understand how these policies work, we never look at the policy as a guiding rail for our superiors to work. In the same context, I wish all of us also understand the nuances of policy management with SQL Server too.

SQL Server 2008 introduced this neat feature which is lesser appreciated by many in the SQL Server world. This blog is to set some context into what are the building blocks of SQL Server Policy Management. If we understand these, then in the future we will build on some real world examples.

Target Set

Targets are entities that can be managed by the Policy Based Management. All similar type targets within an instance of SQL Server form a target hierarchy. A target filter is then applied to all targets within the target hierarchy to form a target set. The Policy Based Management engine, which is implemented in SQL CLR, will traverse the target set and compare the policy’s condition to each target in the target set to determine compliance or a violation.

Facet

A facet has properties that model the behavior or characteristics of managed targets. The properties are built into the facet and are determined by the facet developer. SQL Server currently supports Microsoft built-in facets.

Condition

A condition is a Boolean expression that sets allowed states by the managed target for a particular facet property. A condition must evaluate to either true or false. A condition can include multiple properties of a facet that are and\or together.

Policy

A policy includes a condition based on facet properties that are evaluated against a set of targets. A policy can only have one condition. The policy is executed with one of the evaluation modes which determines how it is implemented and if it logs or prevents out of compliance violations.

Evaluation Mode

Either the On Demand evaluation mode can check the validity of the policy or it can configure the target to adhere to the policy. Not all facet properties are settable. Some are read-only. These read-only properties cannot be configured.

The Check On Change (prevent out of compliance) evaluation mode uses a DDL Server trigger to enforce a policy. When the policy is created, a DDL Server trigger is updated to fire on create, drop or alter activity that starts the policy execution. If the policy is violated, it will roll back the transaction which prevents the change.

The Check On Change (log out of compliance) evaluation mode uses event notification and service broker to log a policy violation. When the policy is created, an event notification is updated to capture create, drop or alter activity which starts the policy execution. If the policy is violated, it will log the policy violation along with information to the Policy Based Management execution history and health state tables in msdb.

The Check on Schedule (log out of compliance) evaluation mode uses a SQL Server Agent job and schedule to start the policy execution. If the policy is violated, it will log the policy violation along with information to the Policy Based Management execution history and health state tables in msdb.

Subscriptions

A subscription allows a database to subscribe to a policy group. All of the policies within the policy group will be applied to a single database. Policy groups and subscriptions make it easier to manage a large group of policies for a database.

Now that we have got the basics cleared out. In subsequent blog posts, we will take examples of each of above building blocks and bring some real world use cases. As readers, I would like to know how many of you have been using Policy Management in your environments today. Can you share few examples where you found it useful?

Reference: Pinal Dave (https://blog.sqlauthority.com)