Automating Policies Management, Part 2

A few practical examples on setting up policies. This time, we PowerShell it.

• By Joshua Jones
• 11/09/2009

Last time, we talked about using some simple cmdlets in SQLPS to explore policy objects in a SQL Server. Now, let's look at a couple of practical examples using policies from the PowerShell environment.

First off, you're very likely going to want to be able to evaluate policies against a server. Conveniently, there's a cmdlet for this: Invoke-PolicyEvaluation. This cmdlet takes a policy and evaluates a specified target server, and outputs the result. Here's the syntax for this cmdlet:

Invoke-PolicyEvaluation [-Policy] <PSObject> [-AdHocPolicyEvaluationMode <AdHocPolicyEvaluationMode>] -TargetServerName <PSObject> [-TargetExpression <String>] [-OutputXml] [<CommonParameters>]

Invoke-PolicyEvaluation [-Policy] <PSObject> [-AdHocPolicyEvaluationMode <AdHocPolicyEvaluationMode> ] -TargetObjects <PSObject[]> [-OutputXml] [ <CommonParameters>]

You can see here that it takes a policy (either explicitly or from a file), the evaluation mode (audit or enforce), a target server, an optional expression for the policy to use (more on this later), and a location to output the results. Finally, it'll take one of the following two parameters:

-TargetExpression: Used to specify a query to determine what objects to evaluate the policy against. It uses a node structure, with the / as a separator. For example, to specify the AdventureWorks database, you would use the following:

Server[@Name='MyComputer']/Database[@Name='AdventureWorks']

-TargetObjects: This specifies an array of objects to evaluate the policy against. For example, if evaluating against a SQL Server Analysis Services Server, you would specify the Microsoft.AnalysisServices.Server object.

These two parameters are mutually exclusive. There's also support for the common parameters that all cmdlets accept, i.e. Verbose, Debug, etc. If you need to see these parameters at any time, remember you can always get the detailed list by issuing get-help Invoke-PolicyEvaluation –detailed.

Using Invoke-PolicyEvaluation is fairly simple; if you have your policies (in XML format, which you can just right-click and export from SSMS) in a folder, you can simply change your working location to that folder and execute the policy:

#use sl (set-location cmdlet) and go to the default policy location
sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy "Some Policy.xml" -TargetServer "MyServer"

The results are then output to the console. If you want to run multiple policies, you can literally pass them in as a comma separated list (i.e. –Policy "Some Policy.xml", "Some Other Policy.xml").

However, once you've started using policies, you'll have more than one or two to evaluate. So, you could pass a list of policies into Invoke-PolicyEvaluation using the Get-ChildItem command and a pipe (from within a directory containing XML formatted policy files):

Get-ChildItem | Invoke-PolicyEvaluation -TargetServer "MyServer"

Using Get-ChildItem cycles the list of policies in the directory, and then hits the server specified. When you combine this with the out-file cmdlet, you can pass in a list of policies, evaluate them against a server, and then output the results for perusal later.

Finally, let's talk automation: As of SQL Server 2008, the SQL Server Agent supports the PowerShell job step type. Using this, you could actually build a job on each server (or one central server) to run your policy evaluations and output the results to a central repository. Fig. 1 shows how the job step would look like.

Figure 1. Building a job to run policy evaluations, with results going to a central repository. (Click image to view larger version.)

This evaluates all of the policies in the default directory (obviously, most of us will use a custom list) against my server, and then outputs the results. Fig. 2 shows a sample of the results I got on my machine.

Figure 2. Results of the job I built in Fig. 1. (Click image to view larger version.)

The true power here is that you could evaluate policies against SQL 2000 and SQL 2005 servers (where applicable). You could centralize policy management for all servers in a single location, and start reporting on the results regularly. You could even import the results into SQL Server, and use a Reporting Services report! There's a lot of power here, so explore and see what you can come up with. Have fun!