Designing Policy Based Management in Microsoft SQL Server 2008

  • 2/18/2009


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.