Designing Policy Based Management in Microsoft SQL Server 2008
- 2/18/2009
Policies
Policies are created for a single condition and set to either enforce or check compliance. The execution mode can be set as follows:
On demand Evaluates the policy when directly executed by a user
On change, prevent Creates data definition language (DDL) triggers to prevent a change that violates the policy
On change, log only Checks the policy automatically when a change is made using the event notification infrastructure
On schedule Creates a SQL Server Agent job to check the policy on a defined schedule
If a policy contains a condition that was defined using the advanced editor, the only available execution mode is On Demand.
To use the On change, prevent and On change, log only execution modes, the policy must target instances running SQL Server 2005 and above. The On change, log only execution mode uses the event notification infrastructure that is available only for SQL Server 2005 and later. The On change, prevent execution mode depends on DDL triggers to prevent a change that is not in compliance with the policy and are available only for SQL Server 2005 and later. In addition, you can set a policy to On change, prevent only if it is possible for a DDL trigger to prevent the change. For example, you could prevent the creation of an object that violated your naming conventions, but you could not enforce a policy that all databases have to be in the Full recovery model because the ALTER DATABASE command executes outside the context of a transaction.