THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Lara Rubbelke

Interesting Things in the World of SQL Server

Automating SQL Server 2005/2000 Policy Evaluation

The Enterprise Policy Management Framework version 3.0, a new version of the framework to support policy automated policy evaluation for SQL Server 2000 and 2005, has been posted to codeplex.

For those who are not familiar with the tool, the Enterprise Policy Management Framework is a reporting solution on the state of the SQL Server enterprise against a desired state defined in a policy. The key capabilities are to extend Policy-Based Management to all SQL Server instances in the enterprise, including SQL Server 2000 and SQL Server 2005.  The EPM Framework will automate a scheduled evaluation of a set of policies against a group of servers, and provide reports for DBAs to understand where they have instances and database objects which are not complying with an organization’s defined standards. 

The new 3.0 release includes the following enhancements:

Supports nested server groups in the Central Management Server

The previous versions did not support Central Management Server groups that were nested in parent groups.  This restriction has been removed and you may now design CMS groups to fit your organization, and leverage these groups for the EPM Framework.

A new parameterized PowerShell execution

The PowerShell script has been updated with parameters.  This enhancement will significantly ease how you may deploy the solution, so you only have a single script to manage.  The previous versions would have required multiple versions of the PowerShell script you were to design the execution strategy by server group and policy category.

Policy results are stored in a table format

The new version 3.0 will shred the policy result XML document to a PolicyHistoryDetail table during the evaluation.  The previous version only stored the XML data and issued queries against XML results stored in a SQL Server table named PolicyHistory.  This update will greatly improve performance during reporting and provides a better platform for the community to build customized views and reports. This could also improve storage – you can purge the data in the PolicyHistory table if you do not require the XML results.

New Report Parameters

Based on feedback from the community, the new version includes parameters in the reports to support filtering by Central Management Server group.  This will be a very important criteria for large organizations who would like to focus on specific groups of instances.

Fixes to error reporting logic

Not much to say, other than the logic that identifies errors stored in the tables is fixed.

Updated Documentation

The documentation has been updated, and should be much easier to follow when setting up the framework.


The EPM Framework leverages the Central Management Server, PowerShell, Reporting Services 2008, and Policy-Based Management.  You will need at least one instance of SQL Server 2008 and an instance of SQL Server 2008 Reporting Services to support the framework.  I will dive deeper into installation and configuration of the framework in subsequent blogs. 

Please let me know if you are using the framework, and if you have suggestions for future enhancements.  I am going to be integrating SQL Server 2008 Policy History centralization into the framework in the next version.


Published Saturday, June 13, 2009 12:11 PM by Lara Rubbelke



jerryhung said:

I'll be the first to comment on how great EPM is and that we use it at work to evaluate SQL Server now, with our own customized Condition/Facet/Category

Keep up the great work, Lara!

June 24, 2009 3:38 PM

RowlandG said:

Just doing the test drive--this is impressive!

Thanks for another great contribution Lara! This will help many.

July 30, 2009 9:57 AM

M Mill said:

I am experiencing an error when trying to run a SQL Agent job which runs a policy category against a server group in CMS.  All of the 2005 instances fail stating:"<DMF:Exception type="string">Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy MikeM Database Backup. ---> Microsoft.SqlServer.Management.Sdk.Sfc.InvalidVersionEnumeratorException: Operation not supported on SQL Server 2005.<?char 13?> at ".  We have put CU6 on the instance that is running the PBM job but it still has issues using the category to run the check. Have not been able to find any info on tis error.  Does anybody have any idea what this might be?  Works fine on 2008 instances.

February 9, 2010 11:07 AM
New Comments to this post are disabled
Privacy Statement