Running Against SQL Server 2005 and SQL Server 2000

  • Article

@ TechEd SQL Server MVP Peter DeBetta and I presented a session where we created a PowerShell script for running a group of policies against a group of servers. The script was then automated using the PowerShell subsystem in SQL Server Agent. The result of each policy evaluation was loaded into a table for reporting purposes.

Because PBM is built on top of SQL Server Management Objects (SMO) and SMO supports SQL Server 2000, SQL Server 2005, and SQL Server 2008, PBM will work against all of these versions.

The script reads in a text file containing a list of the target servers (the servers each policy will be evaluated against). It connects to a primary server to grab the policies and then evaluates each policy in a particular category against each server.

The script is intended to be a proof of concept and is not ready for a production environment. Some alterations that you would want to make include adding error handling and reading the servers from a Central Manageability Server or at the least a table in the primary server rather than from a file.

Here's the script for creating the database and table to store the results of the policy evaluation:

CREATE DATABASE [PolicyResults]
GO

USE [PolicyResults]
GO

CREATE TABLE [dbo].[PolicyHistory](
[EvalServer] [nvarchar](50) NULL,
[EvalDateTime] [datetime] NULL,
[EvalPolicy] [nvarchar](max) NULL,
[EvalResults] [xml] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PolicyHistory] ADD CONSTRAINT [DF_PolicyHistory_EvalDateTime] DEFAULT (getdate()) FOR [EvalDateTime]
GO

You might want to add a column to store the policy result or you can use an XQuery to grab it out of the EvalResults column. I'll show you the query below.

Here's the PowerShell script. I cleaned up the version of the script that was presented - reorganizing it a little to make it easier to follow what's going on.

#Evaluate Policies in a Particular Category against a Server list
#Uses the Invoke-PolicyEvaluation & Invoke-SqlCmd Cmdlets

function InsertPolicyHistory($ServerVariable, $DBVariable, $EvalServer, $EvalPolicy, $EvalResults)
{
#Escape single quotes so we can insert
$EvalResultsEscaped = $EvalResults -replace "'", "''"
$EvalPolicyEscaped = $EvalPolicy -replace "'", "''"

    #Setup the insert statement
$QueryText = "INSERT INTO PolicyHistory (EvalServer, EvalPolicy, EvalResults) VALUES(N'$EvalServer', '$EvalPolicyEscaped', N'$EvalResultsEscaped')"

    #Run the insert statement using the Invoke-SqlCmd Cmdlet
Invoke-Sqlcmd -ServerInstance $ServerVariable -Database $DBVariable -Query $QueryText
}

#CONSTANTS
#Declare the Server\Instance & database to post the policy results
$HistoryServer = "myServer\myInstance"
$HistoryDatabase = "PolicyResults"
#Declare the server\instance for the Policy store
$PolicySourceServer = $HistoryServer
#Setup the file containing the list of servers
$ServersFile = "c:\Servers.txt"
#Setup the location to dump the policy evaluation result output
$PolicyDir = "c:\PolicyEvaluation\"
#Setup the policy filter - only policies in this category will be processed
$PolicyCategoryFilter = "Custom Automation"

#Setup a connection to the policy store
$Conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$PolicySourceServer;Trusted_Connection=true");
$PolStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn);

#Read the servers file into a variable
$Servers = Get-Content $ServersFile

#Clear out the directory where the evaluation results will go
del c:\PolicyEvaluation\*

foreach ($TargetServer in $Servers)
{
foreach ($Policy in $PolStore.Policies)
{
if ($Policy.PolicyCategory -eq $PolicyCategoryFilter)
{
#Clean-up any invalid file system characters
$PolicyNameFriendly = (Encode-SqlName $Policy.Name)
$TargetServerFriendly = (Encode-SqlName $TargetServer)

            #Setup the output file as Server_Policy.xml
$OutputFile = $PolicyDir + ("{0}_{1}.xml" -f $TargetServerFriendly, $PolicyNameFriendly);

            #Evaluate the policy
Invoke-PolicyEvaluation -Policy $policy -TargetServerName $TargetServer -OutputXML > $OutputFile;

            #Read in the policy evaluation results to load it into the result table
$PolicyResult = Get-Content $OutputFile;
#Insert the results to our result table
InsertPolicyHistory $HistoryServer $HistoryDatabase $TargetServer $Policy.Name $PolicyResult;
}
}
}
#Clean-up the evaluation result files
del c:\PolicyEvaluation\*

After you run the script you can use SQLCMD or SSMS to query the result table. The query below will return all rows where the policy was violated. To get back all of the rows where the policy passed change "false" to "true".

USE [PolicyResults]
GO
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/DMF/2007/08' AS Pol)
SELECT *
FROM dbo.policyhistory
WHERE EvaluationResults.exist('//Pol:EvalDetail/Pol:Result/text()[. = "false"]') = 1

In closing, if you automate the script using SQL Server Agent it will run under the context of the SQL Server Agent job. This can either be the Agent service account or you can use a proxy account. Which ever path you choose you'll need to make sure the account has access to the servers.txt file, the location where the results will be written to, the server containing the policies, the server where you want to write the results and each of the servers listed in servers.txt.

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.