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.

This past week I was delivering some events for customers on supporting Mission Critical databases with SQL Server 2008.  During the compliance conversation I mentioned that there were a couple of new resources available related specifically to PCI compliance. 

Parente Randolph published a whitepaper on how to leverage SQL Server 2008 features to meet PCI compliance. 

Furthermore, these auditors presented a TechNet webcast on the same subject.

I would also highly recommend the SQL Server 2008 Compliance Guide which will complement the above resources with technical implementation examples.

Isn’t life grand when you have a standard approach?  That is typically my frame of mind, which is one reason why I advocate for a standard approach to designing, deploying and managing your SQL Server 2008 audits.  I have developed the attached standardized scripts which should get you started on your standardized approach.

The script SetupAudit_Shell is a single SQLCMD script that will create the Audit, the Server Audit Specification, and a Database Audit Specification in every user database (except Model and TempDB).  The script is written to send the audit data to a file destination.  You will need to alter this part of the statement if you prefer to send your audit data to the Windows Security or Application log.  The Server Audit Specification and the Database Audit Specification statements do not specify the audit action groups – you will need to supply these based on what you want to audit. 

The script SetupDataAccessAudit_Shell is a script to alter an existing Database Audit Specification with table level audit actions.  This will apply the exceptions to your standard audit when compliance goals require you audit DML activity from tables which store sensitive data.

The remaining scripts (DisableAudit_Shell, EnableAudit_Shell, RemoveAudit_Shell) are scripts to help manage the audits which are deployed with the defined standards.

The next version of the Enterprise Policy Management Framework has been published to CodePlex.  The new version includes error trapping and error reporting, and a few bug fixes.  Please be sure to download the documentation as well, which will help you understand how to use the PowerShell variables to scale the execution in your environment. 

A new whitepaper has been published outlining the intent of the Enterprise Policy Management Framework. A big thank you to Tom Davidson for all of the hard work writing the paper.

Finally, anyone who is interested in an introduction to Policy-Based Management and a demonstration of the EPM Framework in action can attend my TechNet webcast next Thursday, March 12: TechNet Webcast: Governing Your Enterprise with Policy-Based Management (Level 300).  This webcast is best suited for those of you who…:

• are currently frustrated with managing a SQL Server environment that is inconsistently deployed
• want to reduce the time you spend developing and running scripts to determine issues in business and regulatory compliance
• simply want to see a better way to manage groups of servers to better scale your time

Join in the webcast, it is sure to be fun and enlightening!  In short, you will walk away with an understanding of Policy-Based Management, how to use the SQL Server 2008 Policy-Based Management framework to support down-version (SQL Server 2000 and 2005) policy evaluation, and how to implement the EPM framework (see picture below) for an understanding of policy compliance across the enterprise. 

I have been working on some new materials for an upcoming webcast on leveraging Policy-Based Management using the Enterprise Policy Management Framework.  As I was working through some sample policies, I noticed that my SQL Server 2008 instances were not consistently configured for history retention and to log on success.  Well, I thought this was a good thing to put in a policy, as policies allow me to easily manage configuration settings for my instance.  Much to my surprise, SQL Server 2008 does not currently have a Policy Based Management Configuration facet!  I logged a suggestion to connect.microsoft.com (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=419574), and hopefully we will see a new facet in the future.  Please feel free to vote if you think this would be a helpful addition.

In the meantime, we still need to manage our PBM configuraton in our SQL Server 2008 installations.  So I developed a policy which will execute some scripts and report the state of our environments.  I cannot force configuration to my policy when conditions are defined with scripts, but at least I have a scalable solution to report on those instances which do not conform. 

The attached policy defines the PBM management settings.  I typically like to change the PBM Management properties such that the policy history is only retained for 14 days and successful evaluations are logged.  If you care to look at these setting in Management Studio, right click on Policy Management to select properties. 

• When LogOnSuccess is set to false (default) successful evaluations are not logged.  Setting LogOnSuccess to true will log all policy evaluation results.  You will want to log on success if you decide to centralize policy history and perform enterprise management reporting.  Without the log on success, you will only have failed evaluations.   This makes it difficult to determine if a policy is currently in a failed state or if it was in a failed state in the past but is currently in compliance with your specifications.
• HistoryRetentionInDays will determine how many days of policy history will be saved in the policy history tables in MSDB. 

The attached policy is not configured to run on a schedule.  You may want to change this setting prior to importing the policy to your SQL Server 2008 instances. 

It has been a long time since I have blogged, and I promise it is for a good reason.  The Enterprise Policy Management Framework has been published to CodePlex:  Enterprise Policy Management Framework - Home

I will be presenting this solution at the PASS Summit this week.  Stop by on Friday at 1 pm to learn more and see the full solution in action. 

The Enterprise Policy Management Framework is a reporting solution on the state of the enterprise against a desired state defined in a policy. Extend Policy-Based Management to all SQL Server instances in the enterprise. Centralize and report on the policy evaluation results.

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server 2008 Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define the defined intent, desired configuration, and deployment standards.

When the Enterprise Policy Management Framework (EPM) is implemented, policies will be evaluated against specified instances of SQL Server through PowerShell. This solution will require at least one instance of SQL Server 2008. The PowerShell script will run from this instance through a SQL Server Agent job or manually through the PowerShell interface. The PowerShell script will capture the policy evaluation output and insert the output to a SQL Server table. SQL Server 2008 Reporting Services reports will deliver information from the centralized table.

This solution requires the following components are configured in your environment. All SQL Server 2008 requirements listed below may be executed from and managed on the same instance:
• SQL Server 2008 instance to store policies
• SQL Server 2008 instance to act as the Central Management Server
• SQL Server 2008 instance to execute the PowerShell script
• SQL Server management database and policy history table to archive policy evaluation results
• SQL Server 2008 Reporting Services to render and deliver policy history reports

SQL Server 2008 Books Online and the Policy Based Management (PBM) blog have documented how to create a policy.  Please review these valuable resources if you are looking for an introduction to creating policies in SQL Server 2008.  Through the remainder of this series, I will be focusing on the different methods of evaluating a policy against a target (server, database, database object).  This first part will focus on policy evaluation on a single SQL Server 2008 instance.  As we journey through the series, I will deliver methods to evaluate policies across multiple servers and against down versions of SQL Server (2000 and 2005). 

As a brief introduction, SQL Server 2008 includes a new Policy Based Management (PBM) framework.  This new framework allows you to specify intent in a policy, store the policy on a server and have the server managed by that policy (note: a policy may also be stored in a file and I will touch on this later in the series).  Intent may be defined as a server configuration, database configuration, connectivity requirements, security requirements, business rules, naming conventions, and more.  A policy will identify the targets (servers, databases, database objects) that must be managed by this intent and how they are managed by the intent.  How the target is managed by the policy is dictated by the evaluation mode specified on the policy. 

I think it is very important that we set expectations:  Policy Based Management provides a level of prevention and enforcement, but the current version should be more strongly considered a framework to report and in some cases prevent and enforce compliance.  This is a tool to more easily manage deployed servers, report on the state of SQL Server, and maintain consistency and compliance to your business and regulatory requirements.

SQL Server 2008 policies can be evaluated, or invoked, in a number of ways on a single instance.  Not all evaluation modes are available to all policies.  The facet will determine the evaluation modes available for your policy.  I have pointed out the Policy Based Management blog in the past, and please spend a few minutes to review the blog on Facets.  This blog is particularly enlightening as it points directly to the relationship between each facet and the supported evaluation modes.  The team has developed a chart (hint: print this out and keep it nearby) which will be very useful when you are designing policies.

The following are available evaluation mode options when you save the policy on a SQL Server 2008 instance:

On Schedule

On Schedule will create a SQL Server agent job to execute the policy at scheduled intervals.  This option is available for all policies created for the database engine.  The results of the policy evaluation are available by viewing the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB outlined here.

Consider the following scenario:  Many organizations are challenged with maintaining a consistent deployment of SQL Server.  The process to build a new SQL Server may specify the appropriate settings, but over time these settings may be inadvertently updated.  A DBA may change an AWE setting, alter configuration settings, or alter a backup schedule - any of which can increase the time managing the server and identifying the cause of an issue.  As a result the organization either spends energy managing these configuration settings (manually reviewing periodically or building a heavily architected process to manage this process) or the enterprise has many different deployed builds in production (which leads to issues when standardizing the management of the database tier).

A policy can be deployed on these servers with an evaluation mode On Schedule.  The schedule may run once per day (for example) to determine the status of the deployed instance.  This mode will provide an ideal method for automating the evaluation of deployment standards and reporting on instances which violate these standards. 

Check on Change: Log

Check on Change: Log will write output to the event log and to MSDB when an action is executed against one of the properties that are controlled by the policy. 

For example, you may have a policy that requires xp_cmdshell is disabled.  If a DBA were to enable xp_cmdshell an event is logged indicating that the policy has been violated.  These logs may be viewed in the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB. 

Rounding out this picture, consider an alerting mechanism for violated policies that are critical.  In some cases, you may want to be immediately notified of a violation to your policy.  You may set up an alert triggered by these event logs through SQL Server or System Center which will send an email to a manager or lead administrator that an action was executed against your server that violates the policy.

Check on Change: Prevent

Check on Change: Prevent will disallow any changes that violate the policy.  When a policy is created with this option, a DDL trigger is generated on the server.  If you have multiple policies which are set to Check on Change:Prevent, they will all use the same DDL trigger. 

As an example, you may have a business rule that requires all stored procedures in a particular application database follow a consistent naming convention.  A policy is set up with this requirement and enforced on the server with the Check on Change: Prevent evaluation mode.  When a developer issues a CREATE PROCEDURE statement, the server DDL trigger is started.  SQL Server will evaluate the procedure against the conditions specified in the policy.  If the procedure violates the condition, the CREATE PROCEDURE statement is rolled back and an error is returned to the developer.  The policy will also write an event to the SQL Server event log, Windows NT Application log and the policy views in MSDB. 

On Demand

On Demand is an option where you manually run the policy.  Policies which are stored on SQL Server 2008 and evaluated On Demand are disabled and cannot be enabled.  This option is available for all policies stored on a SQL Server 2008 instance or stored as an XML file.  The results of policies executed On Demand are not written to the SQL Server Event Log, but are available in the policy views in MSDB.

Evaluating a policy On Demand on a single instance can include multiple flavors.  The most obvious is manually running the policy in SQL Server Management Studio, as in the following examples. 

• Right-click on any policy stored in SQL Server 2008 and select Evaluate to manually evaluate the policy.
• Right click the Policies folder in SQL Server Management Studio (SQL Server Instance>Management>Policy Based Management>Policies) and select Evaluate. The Evaluate Policies dialog will appear. By default, the policies stored on the SQL Server 2008 instance are available and you can check which policies you would like to evaluate. If the policies are stored as XML files, select the Browse(...) button. Navigate to the folder where the policy xml files are stored and select one or more policies (hold the shift key to select more than one policy).

In my next post in this series I will detail one of the lesser known (but very cool and useful) methods of evaluating a policy on demand.