DDL Triggers as a Work Around for Policy Based Management Limitations

A college recently asked for my help in solving a problem with SQL Server Policy Based Management. He had a requirement to enforce a policy that prevent the auto update and auto create statistics settings being changed on production databases during code deployments. The problem is, that these settings are in the Database facet and this facet does not support On Chage:Prevent or On Change Log.

You can determine which actions are supported by which facets, by using the below query:

SELECT
    name ,
    'Yes' AS on_demand,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 4)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_schedule,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 2)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_change_log,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 1)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_change_prevent
FROM msdb.dbo.syspolicy_management_facets ;

Instead, I suggested that he use a DDL trigger to ensure that these settings are not changed, and helped him write the below trigger definition.

CREATE TRIGGER DBA_StatisticsManagement 

ON ALL SERVER

AFTER ALTER_DATABASE

AS

BEGIN

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_UPDATE_STATISTICS_ASYNC OFF%'

       BEGIN

             PRINT 'Auto Update Stats Async MUST be configured as ON'

             ROLLBACK ;

       END

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_CREATE_STATISTICS OFF%'

       BEGIN

             PRINT 'Auto Create Stats MUST be configured as ON'

             ROLLBACK ;

       END

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_UPDATE_STATISTICS OFF%'

       BEGIN

             PRINT 'Auto Update Stats MUST be configured as ON'

             ROLLBACK ;

       END

END 

This trigger has been created on the instance, which means that it will reside in the Master database and is configured to fire when an ALTER DATABASE statement is executed. There is no need for it to fire when a CREATE DATABASE statement is executed, because even when using the GUI to create a database, the auto update and auto create statistics settings are configured with ALTER DATABASE statements, after the initial database creation.

The trigger pulls the statement executed from the EVENTDATA() function and then uses the LIKE predicate to pattern match the strings that would turn off the auto update and auto create statistics options. The event data is returned as an XML document and the schema can be found at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd. Therefore, we need to use the value() Xquery method, to extract the data.

After writting this trigger, it played on my mind. Whilst this trigger will always work for changes made through the GUI, there is a chance that it will not work, if the change is made through a script. This is because of the way the LIKE predicate pattern matches the statement. For example, what is a script included an additional space before OFF?

In my next post, I will talk about how I resolved the this, by using a CLR function.

I talk more about DDL triggers and Policy Based Management, in my book, Pro SQL Server Administration, available from apress.com, Amazon and most book shops.