• Article

Administering Servers by Using Policy-Based Management

Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.

Policy-Based Management has three components:

  • Policy management

    Policy administrators create policies.

  • Explicit administration

    Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

  • Evaluation modes

    There are four evaluation modes, three of which can be automated:

    • On demand. This mode evaluates the policy when directly specified by the user.

    • On change: prevent. This automated mode uses DDL triggers to prevent policy violations.

      Important

      If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.

    • On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.

    • On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

    When automated policies are not enabled, Policy-Based Management will not affect system performance.

Policy-Based Management Terms and Concepts

  • Policy-Based Management managed target
    Entities that are managed by Policy-Based Management, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

  • Policy-Based Management facet
    A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Some properties of a facet can only apply to a specific version. For example, the Database Mail property of the Surface Area Configuration facet only applies to SQL Server 2005 and later versions.

  • Policy-Based Management condition
    A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet.

  • Policy-Based Management policy
    A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.

  • Policy-Based Management policy category
    A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Policy categories apply to databases and servers. At the database level, the following conditions apply:

    • Database owners can subscribe a database to a set of policy categories.

    • Only policies from its subscribed categories can govern a database.

    • All databases implicitly subscribe to the default policy category.

    At the server level, policy categories can be applied to all databases.

  • Effective policy
    The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:

    • The policy is enabled.

    • The target belongs to the target set of the policy.

    • The target or one of the targets ancestors subscribes to the policy group that contains this policy.

Examples of Problems Solved by Using Policy-Based Management

Policy-Based Management would be helpful in resolving the issues presented in the following scenarios:

  • A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.

  • The AdventureWorks database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

Allowed Evaluation Modes

The policy evaluation modes are determined by the characteristics of the Policy-Based Management facet that is used by the policy. All facets support On demand and On schedule. Facets support On change: log only if the change of the facet state can be captured by some events. Facets support On change: prevent if there is transactional support for the DDL statements that change the facet state. Policies that are automated with one of these three execution modes can be enabled and disabled.

In SQL Server Management Studio, the Evaluate Policies dialog box provides two options that you can use to run a policy:

  • Evaluate
    This will evaluate policies against the selected targets.

  • Apply
    This enables you to apply changes to applicable targets that violate policies. Some targets are not reconfigurable through Policy-Based Management. For example, if you are evaluating whether backup and data files exist on separate devices, Policy-Based Management can detect the violations to this condition; however, you cannot apply changes through Policy-Based Management to enforce policy compliance.

Policy Management

Policies are created and managed by using Management Studio. The process includes the following steps:

  1. Select a Policy-Based Management facet that contains the properties to be configured.

  2. Define a condition that specifies the state of a management facet.

  3. Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

  4. Check whether an instance of SQL Server is in compliance with the policy.

For failed policies, Object Explorer indicates a critical health warning as a red icon next to the target and the nodes that are higher in the Object Explorer tree.

Policy Storage

Policies are stored in the msdb database. After a policy or condition is changed, msdb should be backed up. For more information, see Considerations for Backing Up the model and msdb Databases.

SQL Server 2008 includes policies that can be used to monitor an instance of SQL Server. By default, these policies are not installed on the Database Engine; however, they can be imported from the default installation location of C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033. For more information, see How to: Export and Import a Policy-Based Management Policy.

You can directly create policies by using the File/New menu, and then saving them to a file. This enables you to create policies when you are not connected to an instance of the Database Engine.

Policy history for policies evaluated in the current instance of the Database Engine is maintained in msdb system tables. Policy history for policies applied to other instances of the Database Engine or applied to Reporting Services or Analysis Services is not retained. For more information, see Troubleshooting Policy-Based Management Policies.

Configuring Alerts to Notify Policy Administrators of Policy Failures

When Policy-Based Management policies are executed in one of the three automated evaluation modes, if a policy violation occurs, a message is written to the event log. To be notified when this message is written to the event log, you can create an alert to detect the message and perform an action. The alert should detect the messages as shown in the following table.

Execution mode

Message number

On change: prevent

(if automatic)

34050

On change: prevent

(if On demand)

34051

On schedule

34052

On change

34053

To set up an alert to respond to the Policy-Based Management error messages, see the following topics:

Additional Considerations About Alerts

Be aware of the following additional considerations about alerts:

  • Alerts are raised only for policies that are enabled. Because On demand policies cannot be enabled, alerts are not raised for policies that are executed on demand.

  • If the action you want to take includes sending an e-mail message, you must configure a mail account. We recommend that you use Database Mail. For more information about how to set up Database Mail, see How to: Create Database Mail Accounts (Transact-SQL).

  • Alert security:

    When policies are evaluated on demand, they execute in the security context of the user. To write to the error log, the user must have ALTER TRACE permissions or be a member of the sysadmin fixed server role. Policies that are evaluated by a user that has less privileges will not write to the event log, and will not fire an alert.

    The automated execution modes execute as a member of the sysadmin role. This allows the policy to write to the error log and raise an alert.

Security

Administering Policy-Based Management requires membership in the PolicyAdministratorRole role in the msdb database. This role has complete control of all policies on the system. This control includes creating and editing policies and conditions and enabling and disabling policies.

Security noteSecurity Note

Possible elevation of credentials: Users in the PolicyAdministratorRole role can create server triggers and schedule policy executions that can affect the operation of the instance of the Database Engine. For example, the PolicyAdministratorRole can create a policy that can prevent most objects from being created in the Database Engine. Because of this possible elevation of credentials, the PolicyAdministratorRole role should be granted only to users that are trusted with controlling the configuration of the Database Engine.

The following security principles apply:

  • A system administrator or database owner can subscribe a database to a policy or policy group.

  • Members of the PolicyAdministratorRole role can enable or disable policies.

  • Members of the PolicyAdministratorRole can create policies that they do not have permission to execute ad hoc, but which can be successful when the policies are run by other users that have sufficient permission.

    Security noteSecurity Note

    Possible elevation of credentials: Users in the PolicyAdministratorRole role can create policies that contain a condition that uses the ExecuteSql or ExecuteWql functions. If a user that has sysadmin permissions later executes the policy, the Transact-SQL that is provided by the Policy Administrator will be executed with the sysadmin permission of the user that is executing it.

  • Ad hoc policy execution occurs in the security context of the user.

  • Policies that have the On schedule evaluation mode, use SQL Server Agent jobs that are owned by the sa login.

Considerations for Using Policy-Based Management

Be aware that policies can affect how some SQL Server features work. For example, change data capture and transactional replication both use the systranschemas table, which does not have an index. If you enable a policy that all tables must have an index, enforcing compliance of the policy will cause these features to fail.