powershellscripts.com

Tutorials  PowerShell Cmdlet Help for Invoke-PolicyEvaluation



NAME
Invoke-PolicyEvaluation

SYNOPSIS
Evaluates one or more SQL Server Policy-Based Management policies against a target set, either reporting the compli
ance level of the set, or bringing the set into compliance.

SYNTAX
Invoke-PolicyEvaluation [-Policy] [-AdHocPolicyEvaluationMode ] -TargetServer
Name [-TargetExpression ] [-OutputXml] []

Invoke-PolicyEvaluation [-Policy] [-AdHocPolicyEvaluationMode ] -TargetObject
s [-OutputXml] []


DESCRIPTION
Evaluates one or more Policy-Based Management policies against a set of SQL Server objects named the target set. Th
e policies specify the allowed values for various properties that are associated with SQL Server objects, such as e
stablishing site standards for database names or collations.
When Invoke-PolicyEvaluation runs in check mode, it reports whether the current properties of the objects in the ta
rget set comply with the rules policy definitions. The objects in the target set are not reconfigured if their prop
erties do not comply with the policies. In configure mode, Invoke-PolicyEvaluation reconfigures any objects in the
target set that do not comply with the policy definitions.


PARAMETERS
-Policy
Specifies one or more policies to be evaluated. Policies can be stored in an instance of the SQL Server Databas
e Engine, or as exported XML files. For policies that are stored in an instance of the Database Engine, use a p
ath that is based on the SQLSERVER:\SQLPolicy folder to specify the location of the polices. For policies that
are stored as XML files, use a file system path to specify the location the policies.
-Policy can take a string that specifies the names of one or more policies to evaluate. If only a file or polic
y name is specified in the string, Invoke-PolicyEvaluation uses the setting of the current path. For policies t
hat are stored in an instance of the Database Engine, use the policy name, such as "Database Status" or "SQLSER
VER:\SQLPolicy\MyComputer\DEFAULT\Policies\Database Status". For policies that are exported as XML files, use t
he name of the file, such as "Database Status.xml" or "C:\MyPolicyFolder\Database Status.xml".
-Policy can take a set of FileInfo objects, such as the piped output of a Get-ChildItem cmdlet that is run agai
nst a folder that contains exported XML policies. -Policy can also take a set of Policy objects, such as the pi
ped output of a Get-ChildItem run against a SQLSERVER:\SQLPolicy path.

Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false

-AdHocPolicyEvaluationMode
Specifies whether Invoke-PolicyEvaluation only reports on the compliance status of the target set (Check), or i
f it also reconfigures the target set to comply with the policies (Configure).

Check - Report the compliance status of the target set by using the credentials of your login account, and with
out reconfiguring any objects.
CheckSqlScriptAsProxy - Run a check report by using the ##MS_PolicyTSQLExecutionLogin## proxy account credentia
ls.
Configure - Reconfigure target set objects that do not comply with the policies, and report the resulting statu
s. Invoke-PolicyEvaluation only reconfigures properties that are settable and deterministic.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-TargetServerName
Specifies the instance of the Database Engine that contains the target set. You can specify a variable that con
tains a Microsoft.SqlServer.Management.Sfc.Sdk.SQLStoreConnection object. You can also specify a string that co
mplies with the formats that are used in the ConnectionString property of the System.Data.SqlClient.SQLConnecti
on class. These include strings such as those created by using the System.Data.SqlClient.SqlConnectionStringBui
lder class. By default, Invoke-PolicyEvaluation connects by using Windows Authentication.

Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-TargetExpression
Specifies a query that returns the list of objects that defines the target set. The queries are specified as a
string that has nodes which are separated by the '/' character. Each node is in the format ObjectType[Filter].
ObjectType is one of the objects in the SQL Server Management Objects (SMO) object model, and Filter is an expr
ession that filters for specific objects at that node. The nodes must follow the hierarchy of the SMO objects.
For example, the following query expression returns the AdventureWorks sample database:
"Server[@Name='MyComputer']/Database[@Name='AdventureWorks']"
If -TargetExpression is specified, do not specify -TargetObject.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-OutputXml
Specifies that the Invoke-PolicyEvaluation cmdlet produce its report in XML format using the Service Modeling L
anguage Interchange Format (SML-IF) schema.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-TargetObjects
Specifies a PSObject or an array of PSObjects that define the set of SQL Server objects against which the polic
y is evaluated. To connect to an instance of SQL Server Analysis Services, specify a Microsoft.AnalysisServices
.Server object for -TargetObject.
If -TargetObject is specified, do not specify -TargetExpression.

Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false


This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer and OutVariable. For more information, type,
"get-help about_commonparameters".

INPUTS



OUTPUTS



NOTES




-------------- Example 1 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server"
sl "100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy "Trustworthy Database.xml" -TargetServer "MYCOMPUTER"


Evaluate a policy on the default instance of the specified computer. Read the policy from an XML file and connect b
y using Windows Authentication.





-------------- Example 2 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server"
sl "100\Tools\Policies\DatabaseEngine\1033"
gci "Database Status.xml", "Trustworthy Database.xml" | Invoke-PolicyEvaluation -TargetServer "MYCOMPUTER"


Use Get-Childitem to read two policies from XML files in a folder, and then pipe them to Invoke-PolicyEvaluation.





-------------- Example 3 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server"
sl "100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation -Policy "Database Status.xml" -TargetServer "MYCOMPUTER" -OutputXML > C:\MyReportFolder\MyR
eport.xml


Format the output of a policy evaluation by using the Services Modeling Language Interchange Format (SML-IF) schema
, and then redirect the output to a file.





-------------- Example 4 --------------

C:\PS>sl SQLSERVER:\SQLPolicy\MYCOMPUTER\DEFAULT\Policies
gci | Where-Object { $_.PolicyCategory -eq "Microsoft Best Practices: Maintenance" } | Invoke-PolicyEvaluation -Tar
getServer "MYCOMPUTER"


Set the current path to a SQL Server policy store. Use Get-ChildItem to read all the polices, and Where-Object to f
ilter for the policies that have their PolicyCategory property set to "Microsoft Best Practices: Maintenance". Then
pipe the filtered set of policies to Invoke-PolicyEvaluation.





-------------- Example 5 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server"
sl "100\Tools\Policies\DatabaseEngine\1033"
$conn = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection("server='MYCOMPUTER';Trusted_Connectio
n=True")
Invoke-PolicyEvaluation -Policy "Database Status.xml" -TargetServer $conn


How to use an Sfc.SqlStoreConnection object to specify the target server.





-------------- Example 6 --------------

C:\PS>sl "C:\program files\Microsoft SQL Server\100
sl "tools\Policies\analysisservices\1033"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$SSASsvr = new-object Microsoft.AnalysisServices.Server
$SSASsvr.Connect("Data Source=localhost")
Invoke-PolicyEvaluation 'Surface Area Configuration for Analysis Services Features.xml' -TargetObject $SSASsvr


How to load the SQL Server Analysis Services assembly, and then create a connection to the default instance on the
local computer and run the Analysis Services surface area configuration policy.





-------------- Example 7 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server"
sl "100\Tools\Policies\DatabaseEngine\1033"
Invoke-PolicyEvaluation "Database Status.xml" -TargetServer "MYCOMPUTER" -TargetExpression "Server[@Name='MYCOMPUTE
R']/Database[@Name='AdventureWorks']"


Using a -TargetExpression query expression to specify that the Database Status policy be evaluated against the Adve
ntureWorks sample database.





-------------- Example 8 --------------

C:\PS>sl "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\ReportingServices\1033"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Dmf.Adapters")
$SSRSsvr = new-object Microsoft.SqlServer.Management.Adapters.RSContainer('MyComputer')
Invoke-PolicyEvaluation -Policy "Surface Area Configuration for Reporting Services 2008 Features.xml" -TargetObject
$SSRSsvr


How to loading the SQL Server Reporting Services assembly, and then create a connection to the default instance on
the local computer and run the Reporting Services surface area configuration policy.






RELATED LINKS
SQL Server Books Online: Using the Invoke-PolicyEvaluation cmdlet