SQL Server Technical Article

Writers: Tom Davidson, Lara Rubbelke, Dmitri Tchikatilov

Contributor: Sanjay Mishra

Technical Reviewers: Lindsey Allen, Hongfei Guo, Prem Mehra, Joseph Sack, Jimmy May, Glenn Berry (SQL Server MVP), Michael Thomassy

Published: February 2009

Applies to: SQL Server 2008

Summary: The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including down-level instances of SQL Server such as SQL Server 2000 and SQL Server 2005.

>Introduction

The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including down-level instances of SQL Server such as SQL Server 2000 and SQL Server 2005. In addition, the EPM Framework can be used to:

• Automate the evaluation of policies against a defined set of SQL Server instances, including SQL Server 2000 and SQL Server 2005.
• Centralize the policy evaluation history to a single source for enterprise policy reporting.
• Define best practices for implementing policy evaluation in extremely large enterprise environments.

This document provides best practice guidance on the use of the EPM Framework as a means of collecting and reporting on policy compliance across an entire SQL Server enterprise.

Policy-Based Management (PBM), a feature introduced in SQL Server 2008, significantly changes the way administrators manage the SQL Server data platform. In previous versions of SQL Server, DBAs spent a large amount of time reacting to issues caused by configuration changes or deployments that did not comply with best practice standards or regulatory requirements. To address this issue, some enterprise environments used custom code or scripts to validate compliance with these standards and requirements. The problem with this approach is that these custom consistency scripts are difficult and expensive to develop and maintain. Using PBM in SQL Server 2008, DBAs declare the desired state of the SQL Server environment and then manually or automatically check and/or enforce compliance of the system with that desired state. The desired state and the rules by which that desired state is enforced are known collectively as a policy. For an introduction to the SQL Server 2008 Policy-Based Management feature, see Administering Servers Using Policy-Based Management in SQL Server 2008 Books Online.

The Enterprise Policy Management Framework automates and extends the SQL Server 2008 Policy-Based Management feature to down-level instances of SQL Server. Because down-level versions such as SQL Server 2000 and SQL Server 2005 do not have the built-in SQL Server 2008 policy engine, policies must be evaluated periodically or on demand using Windows PowerShell command-line scripts. These PowerShell scripts evaluate each instance of SQL Server 2000 or SQL Server 2005 against policies that are defined and stored on an instance of SQL Server 2008. The results of these evaluations are stored in a management data warehouse that resides on a SQL Server 2008 instance that is defined as a Central Management Server.

The EPM Framework can be downloaded at http://www.codeplex.com/EPMFramework.

Benefits of the Enterprise Policy Management Framework

The EPM Framework can be used to automate policy evaluation across the enterprise, centralize collection of policy evaluations and provide reports across the enterprise containing graphical representations of how an IT department is implementing and meeting (or not meeting) company goals. The drill-down capabilities of reporting services provide details of problem areas. It provides concrete answers to such important questions as:

• “Have all the backups completed?”
• “Are we in compliance with security mandates?”
• “Are we in compliance with performance best practices?”

The Enterprise Policy Management Framework is a solution for governing the business and regulatory compliance of your SQL Server environment. By comparing SQL Server deployments to the desired states known as policies, EPM provides the basis for new strategic management initiatives that focus manageability efforts, drive costs lower, limit exposure, and improve productivity.

The EPM Framework extends the Policy-Based Management feature of SQL Server 2008 to all identified SQL Server instances. The EPM Framework, the built-in policy engine, predefined policies, and the ease with which new servers and user-defined policies can be added to the existing policy umbrella all combine to provide a rich and nimble environment, replacing the need for high-maintenance custom scripting or mundane, error-prone manual checks. By collecting historical policy evaluation, EPM can be used to measure the quality of IT support over time insofar as adherence to policy objectives is concerned.

Today there are generally accepted standard industry metrics for IT service levels, such as:

• Availability or up-time (for example, 99.99%).
• Query performance (for example, 95% of all orders must complete in less than 5 seconds).
• Report performance (for example, reports must be completed in less than 5 minutes).
• Timely backups and batch processing, performed at times of off-peak load (for example, backups and batch processing must be completed by 6:00 A.M).

Enterprise Policy Management Framework compliance metrics could easily be added to the above list of accepted industry metrics. Organizations can identify a compliance goal and use the EPM Framework to quantify progress toward this organizational goal. When properly implemented, these goals can be defined at various levels, or categories, rolling up to the overall key performance indicator (KPI).

A sample report from the EPM Framework is shown in Figure 1 entitled “SQL Server Compliance Report”. The SQL Server Compliance report contains four perspectives of policy evaluation including Instance Compliance, Failed Policy Count by Policy Category, Failed Policy % By Month, and Last Execution Status.

Figure 1: SQL Server Compliance Report

Instance Compliance (upper left) shows policy compliance overall by instance. In this report, policy checks were performed on three instances overall. Two instances failed policy checks and one instance passed. Last Execution Status (lower left) provides drill-down details. Of the 18 failed policies, 13 were for the WIN2008\DEMO1 instance while 5 failures were reported for the WIN2008\SQL2000 instance.

Failed Policy Counts (upper right) breaks down evaluation by Policy Category. The 18 failed policies are further broken down into policy categories “2008 compliance”, “Microsoft Best Practices: Maintenance”, “Microsoft Best Practices: Security”, and “Microsoft Best Practices: Performance”. Failed Policy % By Month (lower right) shows compliance measured over time. Policy checking is shown in the blue bar graph. The blue line shows failed policy checks. The trend shows that over time more policies are being checked, with fewer failures.

The SQL Server Compliance report can be used to effectively and proactively focus managing efforts to drive costs lower, limit exposure, and improve productivity. Moreover, IT productivity goals can be set in terms of compliance. Let’s assume we have an IT policy compliance target of 80 percent. Trending over time clearly shows improvement in compliance. The last execution status showed a compliance rate exceeding the target at 82%. Next year’s compliance target would be set higher, say 90 percent.

Out of the box, SQL Server 2008 provides many predefined policies. In addition, users can design their own policies. Table 1 shows examples of policies for each policy area.

Table 1: Sample Policies for Enterprise Policy Management Framework

Ensuring compliance with best practices can help IT departments proactively avoid known problems before they occur.

>Components of the EPM Framework

The EPM Framework solution, which extends Policy-Based Management to the enterprise, requires the following components to be configured in your environment. All SQL Server 2008 requirements listed below can 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

When the Enterprise Policy Management (EPM) Framework is implemented, policies are evaluated against specified instances of SQL Server through PowerShell. This solution requires at least one instance of SQL Server 2008. The PowerShell script runs from this instance through a SQL Server Agent job or manually through the PowerShell interface. The PowerShell script captures the policy evaluation output, and then it inserts the output into a SQL Server table. SQL Server 2008 Reporting Services reports deliver information from the centralized table.

The Central Management Server (CMS) plays two very important roles in the EPM Framework. First, the CMS stores all of the logical server groups that are necessary in the PowerShell evaluation to determine which servers are evaluated. Second, the CMS acts as the centralized enterprise policy store. In SQL Server 2008, you can administer multiple servers by designating a Central Management Server and creating server groups. An instance of SQL Server that is designated as a Central Management Server maintains server groups that maintain the connection information for one or more instances of SQL Server. Transact-SQL statements and Policy-Based Management policies can be manually executed against server groups.

Invoke-PolicyEvaluation is the PowerShell cmdlet that runs serially. For better performance, multiple PowerShells can be run in parallel to expedite policy evaluation. To simplify the management of policy evaluation, multiple policies can be organized into policy categories. PerformanceBestPracticePolicies and SecurityPolicies are examples of policy categories. Servers should be organized into server groups such as ProductionServerGroup or SalesServerGroup. Using policy categories and server groups, Invoke-PolicyEvaluation performs the evaluation of a category of policies against a group of servers.

The EPM Framework requires a Policy Data Warehouse to store all policy history results. The EPM Framework enables an administrator to define the Policy Data Warehouse on any instance of SQL Server, although it may be worthwhile to place this database on the Central Management Server. All policy evaluation results are collected and stored in the Policy Data Warehouse for analysis and reporting.

The EPM Framework includes tools to centralize all historical policy evaluation results, making it a single reporting source for enterprise policy evaluation history and enterprise compliance. The PowerShell evaluation used for down-version evaluation naturally centralizes the results to the designated Policy Data Warehouse. SQL Server 2008 evaluation results are automatically stored in the local SQL Server 2008 msdb database. The EPM Framework includes a PowerShell script that is to be scheduled on each SQL Server 2008 instance. This script exports SQL Server 2008 policy history data for consumption to the Policy Data Warehouse.

As previously noted, PBM enables the DBA to declare the desired state of the SQL Server environment and then check the system for compliance with that state. The Invoke-PolicyEvaluation PowerShell cmdlet reports whether a target set of SQL Server objects complies with the conditions specified in one or more Policy-Based Management policies. Invoke-PolicyEvaluation evaluates one or more policies against a set of SQL Server objects called the target set. The set of target objects comes from a target server. Each policy defines conditions, which are the allowed states for the target objects.

SQL Server Reporting Services reports analyze the historical data with trends of policy compliance over time. KPIs show progress toward the policy targets of the enterprise.

To summarize, the Enterprise Policy Management Framework extends the reach of Policy-Based Management, providing a new perspective on IT quality that complements the established industry metrics of service levels and performance.

Advantages of SQL Server 2008 Policy Evaluation

There are a number of important distinctions regarding policy evaluation on SQL Server 2008, including on-change evaluation, event log alerting, advanced functionality and integration of SQL Server Management Studio. The on-change evaluation mode of SQL Server 2008 provides real-time reporting and enforcement (for example, 24x7). Advanced functionality and integration with SQL Server Management Studio includes policy dependencies, health states, subscriptions, history, and metrics such as KPIs.

For down-level versions of SQL Server 2000 and SQL Server 2005, an instance of SQL Server 2008 is used to execute the PowerShell scripts that evaluate each instance of SQL Server 2000 and SQL Server 2005 against the appropriate policies. The results of these evaluations are stored in a management data warehouse, which resides on the Central Management Server.

Advantages of Upgrading to SQL Server 2008 for Policy Evaluation

SQL Server 2008 can perform policy evaluation of down-level versions such as SQL Server 2000 and SQL Server 2005, but there is an important limitation. Because down-level versions do not include the SQL Server 2008 policy engine, policies must be evaluated periodically or on-demand using PowerShell scripts from SQL Server 2008. The limitation is that continual on-change enforcement or reporting is NOT available on down-level versions such as SQL Server 2000 and SQL Server 2005. The policy compliance of SQL Server 2000 and SQL Server 2005 are accurate at the time of evaluation ONLY.

Because continual or on-change evaluation is ONLY available if the target server is SQL Server 2008, this down-level solution is not designed for systems that have strong regulatory compliance requirements (requiring continual —that is, 24x7—compliance). The EPM Framework can, however, add some much-needed reporting of existing state as enterprise environments plan toward their SQL Server 2008 upgrades.

Organizations whose environments are subject to strong regulatory compliance requirements will want to upgrade to SQL Server 2008 as soon as possible to take advantage of SQL Server 2008 on-change policy enforcement modes. Lastly, some policies, such as strong passwords, do not apply to some earlier versions, such as SQL Server 2000.

Recommendations

The Enterprise Policy Management Framework collects policy evaluation history, and it provides analysis and reporting capabilities for the enterprise. Policy compliance effectively addresses one of the main issues facing DBAs today: the ad hoc problems caused by configuration changes or deployments that do not comply with the best practice standards.

A typical SQL Server environment will have multiple versions of SQL Server. A single SQL Server 2008 server will act as the CMS for enterprise policy management. This CMS server will store all of the server groups and the policies which are necessary in the enterprise. Using the CMS, policies are pushed out to the SQL Server 2008 servers. Only SQL Server 2008 instances allow you to leverage the full benefits of PBM, including on-change evaluation modes.

Down-level SQL Server policy evaluation requires PowerShell on-demand job execution. A PowerShell script is executed on a scheduled basis from the CMS. These PowerShell scripts evaluate each instance of SQL Server 2000 and SQL Server 2005 against the appropriate policies. The results of these evaluations are stored in a management data warehouse that resides on the CMS.

The following are best practice recommendations with EPM:

• For performance, use parallel policy evaluation. Policy evaluation using Invoke-PolicyEvaluation runs serially. For this reason, multiple PowerShell executions of Invoke-PolicyEvaluation should be used to expedite the process.
• Use policy categories. To simplify the management of policy evaluation, multiple policies should be organized into policy categories. PerformanceBestPracticePolicies or SecurityPolicies are examples of policy categories.
• Leverage Best Practice Policies. Many of these predefined policies can be used to avoid problems before they occur.
• Use server groups. To simplify the management of servers to be evaluated, multiple servers should be organized into server groups such as ProductionServerGroup or SalesServerGroup. In this manner, categories of policies can run against a group of servers.
• Migrate down-level SQL Server 2000 and SQL Server 2005 instances to SQL Server 2008 as soon as practical. Only SQL Server 2008 targets can take advantage of 24x7 on-change evaluation modes which are critical for high regulatory requirements.

>Conclusion

Policy-Based Management in SQL Server 2008 significantly improves the way administrators manage the data platform. The ease with which SQL Server 2008 enables effective policy management provides auditors and management more control over the environment. Ensuring compliance with best practices can help IT departments proactively manage the data platform, avoiding known problems before they occur.

The EPM Framework extends Policy-Based Management to the enterprise, providing an effective way to measure IT quality, providing direction for new strategic management initiatives to focus manageability efforts, driving costs lower, limiting exposure, and improving productivity.

For more information:

http://sqlcat.com/Default.aspx: SQL Server Customer Advisory Team (SQLCAT) site

http://msdn.microsoft.com/en-us/library/dd334464.aspx: SQLCAT White Papers in MSDN Library

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx: SQL Server Best Practices on the TechCenter

http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

http://www.microsoft.com/sqlserver/: SQL Server Web site

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

• Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
• Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.

Appendix: Additional Resources

Find additional resources below:

The Enterprise Policy Management Framework download: http://www.codeplex.com/EPMFramework

The SQL Server Manageability Team has started a new blog related to Policy-Based Management. This site will provide some great learning content for the terminology and development of policies: http://blogs.msdn.com/sqlpbm/ 

Windows PowerShell blog: http://blogs.msdn.com/powershell/

Lara Rubbelke’s blog: http://sqlblog.com/blogs/lara_rubbelke/default.aspx 

Dan Jones’ blog: http://blogs.msdn.com/dtjones/default.aspx 

Buck Woody’s blog: http://blogs.msdn.com/buckwoody/default.aspx