Search results matching tags 'Maintenance' and 'PowerShell'

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.

Enterprise Policy Management: Updates, New Whitepaper, and Upcoming Webcast

Lara Rubbelke — Wed, 04 Mar 2009 15:21:00 GMT

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.

Evaluating Policies On Demand Through PowerShell

Lara Rubbelke — Thu, 19 Jun 2008 04:22:00 GMT

In my previous blog I reviewed the different policy evaluation modes available in SQL Server 2008. In this next installment I will detail how to initiate a policy on demand using PowerShell.

PowerShell integration with SQL Server 2008 is getting more press lately. In past blogs I noted a few resources to help learn PowerShell, and since that time additional resources (http://blogs.technet.com/industry_insiders/pages/powershell-in-sql-server-2008.aspx and http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx to call out a few) continue to surface. This blog series is focused on Policy Evaluation, so I will be specifically looking at the new PowerShell cmdlet available in SQL Server 2008 to support an on demand evaluation of policies.

The cmdlet Invoke-PolicyEvaluation will allow you to specify a policy (or set of policies) to be evaluated against a target server. The general syntax is as follows:

Invoke-PolicyEvaluation [-Policy [(<string[]> | <Policy> | <FileInfo>)]] -TargetServerName <(string | ISfcConnection)> [-TargetExpression <string>] [-OutputXml] [-AdHocPolicyExecutionMode <AdHocPolicyExecutionMode>]

To open the SQL Server PowerShell provider through Management Studio, right-click on the Server and select Start PowerShell.

Note: You can also add the SQL Server PowerShell provider into the main PowerShell. Open PowerShell and run the following commands to load the SQL Server snap-ins. These are documented incorrectly in SQL Server 2008 RC0 BOL.

add-pssnapin SqlServerCmdletSnapin100

add-pssnapin SqlServerProviderSnapin100

Once you are in PowerShell, we can execute our commands to evaluate a policy. I encourage you to issue the following command, which will return a very detailed report on the cmdlet:

Get-Help Invoke-PolicyEvaluation -Detailed

A very simple example will assume that you have the SQL Server 2008 sample policies installed in the default location. On my laptop, they are located at "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033".

#Set the working location to the file with our sample policies

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml" -TargetServer "MyServerName"

Executing these commands will return the output on the console with the results of the policy evaluation. Note that these results are NOT stored in the MSDB database (as other evaluation modes will do).

If you would like to evaluate multiple policies, they are passed as a comma separated list as follows:

#Set the working location to the file with our sample policies

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml", "Database Auto Shrink" -TargetServer "MyServerName"

The Invoke-PolicyEvaluation cmdlet can accept inputs from a pipe. The following example will loop through and evaluate each policy stored in the file against the server.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

gci | Invoke-PolicyEvaluation -TargetServer "MyServerName"

By default, the -AdHocPolicyExecutionMode parameter is "Check", which will simply evaluate the policy and report back through the console or through an XML output file. The "Configure" option will reconfigure properties that failed compliance so they comply with the policy, as long as they are deterministic and settable.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml", "Database Auto Shrink" -TargetServer "MyServerName" -AdHocPolicyExecutionMode "Configure"

The final parameter I want to point out is the -OutputXml, which will produce an XML report. As mentioned above, the policy evaluation results are not stored in the MSDB database. Using this parameter, you can produce an output file which will be very useful when you want to review the information at a later time.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml" -TargetServer "MyServerName" -OutputXml >"Report.xml"

So, yes you can evaluate a policy on demand through PowerShell. You can create a SQL Server Agent Job with a step that executes your PowerShell script (new in 2008).

I can hear what many of you are thinking - "<insert your name here>, why would I need to use this feature if I can simply create a policy on SQL Server 2008 and let the policy manage the server and have the output stored in MSDB?" In my opinion, the policy should be on the server when your server is SQL Server 2008. The server should be managed by the policy whenever possible. However... What if you want to evaluate a policy against a SQL Server 2000 or 2005 instance? Aha! Now you have a real use case for invoking policy evaluation through PowerShell.

This is just a brief overview of some of the ways to use this cmdlet, but as with all PowerShell and SQL implementations there are multiple ways to put these into action. In the next parts of the series, I will explore central management servers, data collectors, and examples on how to extend this cmdlet for an enterprise solution.