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.

Full article...