Managing policies with PowerShell

Whilst Powershell is undoubtedly an incredibly powerful tool, in SQL Server 2008, Microsoft did not seem to get a chance to finish off the help files, and although it is included to allow you to automate the management of policies, there seems to be very little written on the web about how to do this, so let me try and unravel a few mysteries for you…

Firstly, lets assume that we have three policies stored in the file system. They are in a folder named c:\policies and are called MyPolicy1.xml, MyPolicy2.xml and MyPolicy3.xml.

Now assume that you want to evaluate both of these policies against your sever, but you also want to enforce the policies at the same time. From within PowerShell, the first thing that we need to do is navigate to our folder in the file system…

sl "c:\policies\"

Now we are in the correct location, we need to evaluate them, but to enforce the policies, we will also set an ad-hoc evaluation mode…

 

invoke-policyevaluation -policy "name of pol.xml", "name of pol2.xml" -adhocpolicyevaluationmode "configure"

That is useful, but more than likely, or policies will not be stored in the file system, they will be stored in our SQL Server instance, and if you have many policy objects, you may also be taking advantage of policy categories. So lets navigate back to our Policy Store in SQL…

 

sl sqlserver:\sqlpolicy\ny-srv-01\default\policies

Now for the slightly more complex statement. Here, we are going to use | as a for loop, to loop through the policies stored in a category called MyCategory1. We are also going to use > to send the results out to a xml file, so that we have an audit, rather than having to view the results manually…

 

get-childitem | where-object {$_.policycategory -eq "new2" } | invoke-policyevaluation -targetserver "ny-srv-01" -outputxml > "c:\policies\policyeval.xml“

This is a simple, but classic example of combining PowerShell with SMO & Policies. If you want you can go much further than this, and on CodePlex, you will find a community project called the EPM Framework, which uses PowerShell in conjunction with Reporting Services to produce a fantastic compliance tool. http://epmframework.codeplex.com/

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom