Declarative Management: Behind Enforce

The new policy-based admin feature (Declarative Management) in SQL Server 2008 (code-named Katmai) can keep many aspects of the system from falling out of compliance. Some questions that come up are: "how does this work?" and "how come everything can't be enforced?" Let me tackle the first one, well, first. This is sort of a 300-level discussion.

How does this work?

The fundamental magic behind this is the DDL eventing in the engine. In SQL2K8 we've introduced a new type of DDL trigger that listens for all server and database scoped events. This is a public trigger and you'll be able to use it as well. The power in this is we don't have to sprinkle triggers around the system. We create a single trigger and update it with the events we care about. The list of interesting events is driven by the active policies. This means if you don't have any active policies we don't listen to any events. When a policy is activated we grab the events from the facet (which comes from the condition associated with the policy) and update the trigger to listen to those events. Each facet identifies which objects (table, view, database, etc) it works against and which events for those objects it cares about (create_table, alter_table, etc).

Great, now when an action happens on the system we can respond to it. But what do we do with the event once we get it? The main declarative management "engine" runs inside the SQLCLR. We have a stored procedure that executes some logic (I'll define in a minute) and based on the results of that execution it calls into the declarative mgmt engine in the SQLCLR. Before calling the SQLCLR the stored procedure determines if there are any policies that might apply to the object in the event. For example, the policy may filter out certain databases; therefore, we shouldn't run that policy against tables created in a filtered database. This allows us to reduce the chances of unnecessarily executing a policy. Note that even if we pass the pre-check and execute the policy, the policy will only be evaluated against objects that are in its scope. In essence we do the check twice. We do this due to the bit of overhead of the SQLCLR.

If the policy is checked and the object passes the policy we either move on to check the next policy or we return control back to the engine by passing success to the trigger. If the object fails the policy check we pass failure to the trigger (we stop executing any additional policies) and the engine eventing mechanism cancels the transaction and returns the system to its original state.

Declarative management uses the SQLCLR but this doesn't mean you have to enable the SQLCLR. The SQLCLR has two modes "On" and "Off". The "Off" mode still allows assemblies signed by Microsoft and installed in the Resource Database to execute. User assemblies, however, only run when the SQLCLR is "On". If your shop has a "policy" to keep the SQLCLR turned "off" you will still be able to use Declarative Management.

How come everything can't be enforced?

Since Declarative Management is dependent on the DDL eventing in the database engine we can only enforce state for those objects that are true transactions. For example, create table is a true transaction and can be rolled back with the system returned to its original state. However, something like create database is not a transaction. Can you put a trigger on create database and fail it? Yes. But this doesn't cleanly return the system to its original state. When a create database transaction is failed there is "residue" left behind, namely the data files. Because we cannot return the system to its original state we opted to turn off enforce for this class of objects. We're working with the database engine team to support true transactions for all DDL events but we won't get all the way there in SQL2K8.

I hope this brief overview of "Enforce" helps you understand what's going on under the covers.