Automating SQL Server 2005/2000 Policy Evaluation

Lara Rubbelke — Sat, 13 Jun 2009 16:11:00 GMT

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.

Lara Rubbelke : SQL Server 2005, Policy Based Management

Automating SQL Server 2005/2000 Policy Evaluation