SQL Server 2008 : Policy-Based Management

Policy-Based Management is a new feature in SQL Server 2008 that allows users to define and implement policies across SQL Server infrastructure. Group Policy offers centralized management and configuration of systems, applications, and users via administrator or system-controlled policies, which can then be applied at various levels of managed directory structure. Policy-Based Management adheres to those same principles as Group Policy, in that you can apply a policy against a target (such as a database, table, or stored procedure) and evaluate whether the target complies with your policy. If your target does not adhere to your policy, you can either enforce compliance with that policy or trigger an alert to let an administrator know about the policy violation. You can set up your policy to actively deny any nonconforming actions, or choose to simply log such actions.

Policy-Based Management Components

    There are mainly three components : Policies, Conditions, and Facets. Facets are required in order to create conditions, and conditions are required in order to create policies. Additionally, policies are applied to the targets you specify.

Facets

A facet is a group of logical properties that are related to each other within the context of the specified target. SQL Server 2008 exposes 74 facets, each with one or more properties. This allows you to leverage hundreds of properties in order to create policies.

Conditions

A condition is a specified required state for the policy or facet being evaluated. Basically, a policy checks the condition of a target. If the target does not comply with the specified condition, the policy fails. A policy can evaluate only one condition, but user can evaluate one or more properties within a single condition.

Policies

A policy is a complete package that includes conditions, facets, targets, evaluation modes, and server restrictions. Policies are stored within the msdb system database. Policies can be exported from database and stored in XML format. This portability allows administrators to easily share and compare custom policies.

Targets

Targets are the objects that are managed by a policy. Targets can refer to many objects: servers, databases, instances, stored procedures, and so on. Policies can contain multiple targets. The available targets change depending on the context of the policy.