Automating DMF Policy Enforcement

SQL Server's Declarative Management Framework allows you to create policies with which databases on your SQL Server 2008 system must comply. In previous articles, we examined how to create DMF conditions, create DMF policies, verify policy compliance and manually enforce policies. In this final installment, we look at ways you can automatically manage DMF policy compliance with Microsoft SQL Server.

There are three execution modes that you may use for automated policy compliance:

• On Schedule evaluation mode uses SQL Server Agent jobs to verify that targets satisfy conditions and uses the application log to record instances of policy noncompliance. The conditions are checked on whatever schedule is configured for the SQL Server Agent job.
• On Change - Log Only evaluation mode monitors event notifications to identify any events that conflict with the DMF condition and then records the noncompliance in the log
• On Change - Prevent evaluation mode creates DDL triggers that enforce compliance by blocking any changes that would bring the target out of compliance.

Configuring a policy for automated compliance is quite simple: you just need to change the policy's evaluation mode. Open SSMS and navigate your way to the policy in question. Double-click on it, bringing up the Open Policy window and use the drop-down menu to select the appropriate evaluation mode.

When choosing an automated policy evaluation mode, you must ensure that the policy is enabled. Do this either by checking the Enabled box on the policy's General tab. Alternatively, you may right-click the policy in SSMS and choose Enable from the pop-up menu that appears.

It's likely that you'll want to select a variety of execution modes for your DMF policies depending upon your business requirements. You may have some hard-and-fast requirements that you definitely want to comply with at all times; On Change - Prevent mode is likely appropriate for those situations. On the other hand, you may wish to use On Change - Log Only mode or On Schedule modes to alert administrators to changes that are less urgent and don't require automated remediation.