The Policy Based Management feature we described in the series of articles (see below) provides an efficient method to declare and enforce policies related to SQL Server instances and their objects. The main purpose of the feature is to facilitate maintenance of a single and, what’s more important, multiple SQL Server instances across the enterprise. Policies can be created on one SQL Server instance, and applied to other SQL Server instances afterwards, as explained in details in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances article.

Policy Based Management provides DBAs the possibility to declare a certain SQL Server system component (e.g. instances and objects) state and evaluate its compliance status with the declared state. There are different evaluation modes that can be used by a Policy Based Management declared policy. The modes differ by the policy evaluation execution type and the action that follows based on the evaluation result. There are four evaluation modes currently supported by the SQL Server Policy Based Management feature:

1. The On demand evaluation mode that’s used per explicit administrator’s request. This is the only non-automated evaluation mode.
2. The On schedule evaluation mode automatically checks policy violations on the scheduled time using SQL Server Agent jobs and logs violations as event notifications.
3. The On change: prevent automated evaluation mode rolls back unwanted changes using DDL triggers to prevent policy violations.
4. Similar to the previous evaluation mode, the On change: log only evaluation mode evaluates whether applied changes comply with the declared policy, but does not roll back the changes in case of a violation. It creates only event notifications instead.

The following diagram depicts the range of supported evaluation modes in SQL Server available facets referred by policies:

Both automated and non-automated evaluation methods have their advantages, disadvantages and purposes. While automated evaluation modes are recommended in scenarios where unwanted user actions can violate declared policies, the non-automated is usually used by DBAs to verify a SQL Server instance and its objects status in search for omissions after regular and/or documented changes.

SQL Server system performance can be affected, depending on the number of target objects being evaluated against the specific policy, if an automated policy evaluation mode is used. On the other hand, the automated evaluation modes are recommended as a method to ensure consistent policy compliance of SQL Server instances and their objects.

All SQL Server editions, starting from SQL Server 2008 support the described policy evaluation modes, while the SQL Server Express editions support only the On demand evaluation mode.

The On change evaluation mode

In previous articles (see links below), we used the On demand and On Schedule policy evaluation modes to provide Policy Based Management feature examples. The On change evaluation mode is specific comparing to these two. First of all, it requires the enabled nested triggers feature on SQL Server. If the feature is not enabled, the policy evaluation will not provide valid results. In such case, evaluation will fail or report no violations at all. The nested triggers feature can be enabled via the Properties dialog of a SQL Server instance, on the Advanced page, by setting the Allow Triggers to Fire Others property to True.

There are two necessary policy requirements which declare whether a policy supports the On change evaluation mode:

1. The facet referred by the policy must support the On change mode (e.g. the Stored Procedure facet)
2. Target filters, if used by the policy condition, must be related to the target’s Name value (e.g. the name of a stored procedure), and without server restrictions. The Server restriction option determines whether the particular policy is restricted to particular SQL Server instances or not.

To check supported evaluation modes by the SQL Server available facets and filter out only the ones that support On change: prevent mode, use the following query:

This will provide the list of facets with the odd execution_mode value

These are the facets that support the On change: prevent evaluation mode as the bit mask that defines the execution_mode value consists of:

4 – On schedule
2 – On change: log only
1 – On change: prevent

In the following example, we’ll show how to create a policy that evaluates names of stored procedures and prevents their (re)naming based on a declared rule (e.g. we want the names of all stored procedures to start with the ‘ACME_’ string).

To define the policy, we need to create an appropriate condition first:

1. In Object Explorer, select the New Condition option in the context menu of the Conditions node.
2. Type in the name for the new condition (e.g. My_SP_Naming_Policy_Condition) in the Name text box.
3. Select the Stored Procedure facet from the facets dropdown menu.

4. Set the condition rule using the Expression grid by specifying the following:

   @Name LIKE ‘ACME_%’

   

5. Click OK to confirm the new policy creation

   After the condition is created, we can continue to declare the policy that will use the condition in order to evaluate and prevent unwanted renaming of stored procedures.

To create the policy:

6. In Object Explorer, select the New Policy context menu option on the Policies node.
7. Type in the policy name (e.g. My_SP_Naming_Policy.)
8. Enable the policy using the Enabled checkbox option.
9. Select the condition (My_SP_Naming_Policy_Condition) we previously declared using the Check condition dropdown.

10. Change the Evaluation Mode option to On change: prevent value.

    

11. By default, the Stored Procedure facet we used for declaring the condition will cause the Against targets values to be set on every stored procedure and on every database as shown in the picture above. To change this, we will change the Every Database target to a specific (e.g. ACMEDB) database.

12. Click the Every dropdown menu next to Database and select the New condition option. This will open the same dialog we used when creating the My_SP_Naming_Policy_Condition condition. Use it to declare a new condition (e.g. ACMEdb) by specifying the following:

    @Name = ‘ACMEDB’

    The appropriate Database facet will be already selected, since we are creating a database related condition. Click OK to confirm the ACMEdb condition creation and to return to the policy creation dialog. After the ACMEdb condition is created, the Against targets box of the policy creation dialog will be changed:

    

13. Click OK to complete the new policy (My_SP_Naming_Policy) creation.

After the process of declaring the policy is finished, both the policy and newly created conditions are available in Object Explorer under appropriate sub-nodes of the Policy Management node.

To test the policy, try to create (or rename) a stored procedure in a specified database. The following message will be shown:

Note that the policy we declared can be manually evaluated against the database and its existing stored procedures also. In case there are stored procedures that do not comply with the policy, these will be reported. So, the On change: prevent evaluation mode proves to be efficient both in preventing and identifying existing policy violations. Unfortunately, the number of facets referred by policies that support the On change: prevent evaluation mode is very limited by their number and by the necessary policy requirements as we described.