Administering SQL 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.
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.
Head on over to
to learn the details.
Thomas W Shinder, M.D., MCSE
Sr. Consultant / Technical Writer