You can think of policy-based management as
Active Directory for SQL Server. Active Directory is used in
simplifying the process of administering thousands of domain users and
computers. In a similar manner, policy-based management is the tool of
choice in ensuring consistent SQL Server configuration, and like Active
Directory, its value is magnified in environments with large numbers of
are several new terms used when discussing policy-based management:
targets, facets, conditions, and policies. Let's look at each in turn.
is the entity managed by a policy. Depending on the policy, targets may
be SQL Server instances, databases, tables, and so forth. In the
example in figure 1, the target chosen for a table name policy is every table in every database.
Figure 1. When creating a policy, you choose a target. In this example, the
target for Table Name Policy is "Every Table in Every Database."
is the name given to a group of configurable properties that are
appropriate for a certain number of targets. For example, as shown in figure 2, the Surface Area
Configuration facet, applicable to the Server target, contains properties such as DatabaseMailEnabled, CLRIntegrationEnabled and XPCmdShellEnabled.
Figure 2. Facets, such as Surface Area Configuration, contain a number of
properties that can be used in defining policy conditions.
is created to specify the required state of one or more facet
properties. Continuing our surface area configuration example, the
condition shown in figure 3 contains the required state of ten properties belonging to the Surface Area Configuration facet.
Figure 3. A condition contains the required value of one or more facet properties.
it all together, a policy contains a condition, a target, and an
evaluation mode, which defines how the policy conditions will be
enforced. Evaluation modes, some of which are only available for
certain facets, are as follows:
mode ensures policy violations are prevented through the use of DDL
triggers that roll back changes that violate policy. The mechanism used
for the rollback (DDL trigger) limits the situations in which this
evaluation mode can be used.
On Change-Log Only—This
mode logs violations when a change occurs that violates an enabled
policy. Corresponding alerts can then be set up as appropriate.
SQL Agent jobs, the On Schedule evaluation mode will periodically check
policy compliance, and log violations if appropriate. This mode is
useful in reducing the performance impact of a large number of enabled
evaluation mode is used when creating ad hoc checks. The policies are
created as disabled and, as such, have no performance impact on a
With these terms in mind, let's take a look at the process of importing, creating, and evaluating policies.