Extending Policy Based Management to earlier editions
SQL Server 2008 introduced the policy based management (PBM) feature which allows DBAs to create policies in SQL Server Management Studio (SSMS) to manage database entities. These entities can be the SQL instance, databases, and SQL objects. These set of policies can be evaluated in four different modes:
- On Demand - where the is evaluated directly by the user
- On Change: prevent - where the Data Definition Language Triggers are used to prevent a policy violation
- On Change: log – where the policy allows the change but logs the change
- On Schedule – where the SQL Agent job is used to periodically evaluate the policy
The PBM feature is designed for SQL Server 2008 or subsequent releases, but with the use of the Enterprise Policy Management Frameworks solution available on codeplex.com policies can be evaluated against SQL 2000 and SQL 2005 environments. The On-Demand evaluation mode is the only one of the four modes that allows for evaluation against the earlier editions. This evaluation in conjunction with a powershell script enable the evaluation of policies against the earlier editions.
The Enterprise Policy Management Framework solution also provides canned reports that display the desired state of the managed instances.
More info on the Enterprise Policy Management Frameworks can be found at: