SQL Server 2008 : Policy-based management - Policy-based management terms

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 server instances.

There are several new terms used when discussing policy-based management: targets, facets, conditions, and policies. Let's look at each in turn.

1. Targets

A target 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."
 

2. Facets

A facet 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.
 

3. Conditions

A condition 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.
 

4. Policies

Putting 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:

• On Change-Prevent—This 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.

• On Schedule—Using 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 policies.

• On Demand—This evaluation mode is used when creating ad hoc checks. The policies are created as disabled and, as such, have no performance impact on a running instance.

With these terms in mind, let's take a look at the process of importing, creating, and evaluating policies.