SQL Server 2008 – Getting tough with Policy Management

Policy management can work in a number of ways from being prescriptive to warning you either on demand or by schedule, that all is not as it should be.

However only certain policies can be enforced i.e. they will have the option “On Change Prevent” applied to them and so you won’t get the option in evaluation Mode drop down at the bottom of the UI like this one.

This is because to enforce policies there needs to be a ddl trigger for the thing (fact) you are trying to enforce policy on. For example this policy to prevent any tables being created in the dbo schema does have this option..

So how do you find out what you can’t enforce? use the syspolicy_management_facets dmv:

SELECT facet.name as 'Facet Name',
OnDemand = CASE facet.execution_mode & 4
WHEN 4 THEN 'On Demand; On Schedule'
END,
OnChangeLogOnly = CASE facet.execution_mode & 2
WHEN 2 THEN 'On Change, Log Only'
END,
OnChangePrevent = CASE facet.execution_mode & 1
WHEN 1 THEN 'On Change, Prevent'
END
FROM msdb.dbo.syspolicy_management_facets facet

the execution mode is also a property of the policy as well so you could also substitute the dmv for them, syspolicy_policies.

Bear in mind that running these on demand will have a small performance hit for each one, so it might be appropriate to apply policies like the dbo one above to your dev servers, on the theory that test and production will only inherit code from dev.

Technorati Tags: SQL Server 2008,Policy Based Management