SQL Server 2008 Declarative Management Framework: be careful with subscriptions

Before we get to the bad part, I first want to stress the importance of the new declarative management framework (DMF). It allows administrators to define policies (naming conventions, security settings, force index creation, avoid fragmented data,...) and then map these policies onto servers or databases. This will simplify the administration of a SQL server instance a lot! For more info, check e.g. this webcast.

Now for the bad news. Policies are grouped into policy groups (quite straight forward), and a database subscribes to zero, one or more policy groups. However, when we drop a database, the subscriptions are not dropped. This can cause some very nasty side effects:

  • When dropping a subscription group, this operation can fail because of a subscription by a database you dropped already a while ago. In this case, manually dropping the subscriptions will solve the problem.
  • Imagine you drop a database X which subscribed to a bunch of policies. Some months later you create a new database which accidently has the same name X. Then automatically all policies which applied to your old database X will now also apply to your new database X: Mapping happens on database name, not id!

What do we do about this? This behaviour is in the July CTP, maybe next releases will no longer suffer from this problem (vote for the bug report here). Else, the solution is to manually clean up these subscriptions. In the msdb database, you find a view syspolicy_policy_group_subscriptions and a stored procedure sp_syspolicy_delete_policy_group_subscription with wich you can manually fix all problems.