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

Evaluating Policies On Demand Through PowerShell

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 ( and 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.

Published Thursday, June 19, 2008 12:22 AM by Lara Rubbelke



Jean-Pierre Riehl said:

The job created by the system when we set a policy as "On Schedule" use Powershell command (invoke-policyevaluation). When TargetServerName is local server, resultats can be found in history. When TargetServerName is changed to another server, no results or history are logged....

How can I consolidate results (without EPBM Fx) ?

May 5, 2011 7:34 AM

John Wood said:

Absolutely great article. When I first learned of PBM, I was blown away by the potential but I still have a lot of 2005 servers. Now I can use PBM on all my servers!

July 19, 2012 3:31 PM

Lara Rubbelke said:

Great John! Be sure to check out the EPM Framework on CodePlex. This is a policy evaluation tool for all servers in your ecosystem.  

July 19, 2012 4:08 PM

Marcos Freccia said:

is it possible extract any valuable information from the xml generated in the Invoke-PolicyEvaluation without using EPM?

June 17, 2013 10:11 PM

Lara Rubbelke said:

Marcos - Absolutely.  The EPM Framework is simply a collection of PowerShell scripts and SQL Server objects that transform the raw data and deliver visualizations.  You can use any piece of the framework individually - and even just pull the logic out of the stored procedures to create your own process.  

June 19, 2013 8:42 AM
New Comments to this post are disabled
Privacy Statement