Invoke-PolicyEvaluation

Evaluates one or more SQL Server Policy-Based Management policies against a target set, either reporting thecompliance level of the set, or bringing the set into compliance.
Invoke-PolicyEvaluation [-Policy*] <psobject> -TargetObjects* <PSObject[]> [-AdHocPolicyEvaluationMode<AdHocPolicyEvaluationMode>] [-OutputXml <Boolean>] [<CommonParameters>]
Invoke-PolicyEvaluation [-Policy*] <psobject> -TargetServerName* <psobject> [-AdHocPolicyEvaluationMode<AdHocPolicyEvaluationMode>] [-OutputXml <Boolean>] [-TargetExpression <string>] [<CommonParameters>]

Evaluates one or more Policy-Based Management policies against a set of SQL Server objects named the target set. The policies specify the allowed values for various properties that are associated with SQL Server objects, such as establishing 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 target set comply with the rules in the policy definitions. The objects in the target set are not reconfigured if their properties 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
-AdHocPolicyEvaluationMode <AdHocPolicyEvaluationMode>

Specifies whether Invoke-PolicyEvaluation only reports on the compliance status of the target set (Check), or if 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 without reconfiguring any objects.CheckSqlScriptAsProxy – Run a check report by using the ##MS_PolicyTSQLExecutionLogin## proxy account credentials.Configure – Reconfigure target set objects that do not comply with the policies, and report the resulting status. Invoke-PolicyEvaluation only reconfigures properties that are settable and deterministic.

-OutputXml <Boolean>

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

-Policy <psobject>

  • This value is required
  • Accepts pipeline input ByValue

Specifies one or more policies to be evaluated. Policies can be stored in an instance of the SQL Server Database Engine, or as exported XML files. For policies that are stored in an instance of the Database Engine, use a path 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 policy name is specified in the string, Invoke-PolicyEvaluation uses the setting of the current path. For policies that are stored in an instance of the Database Engine, use the policy name, such as “Database Status” or “SQLSERVER:SQLPolicyMyComputerDEFAULTPoliciesDatabase Status”. For policies that are exported as XML files, use the name of the file, such as “Database Status.xml” or “C:MyPolicyFolderDatabase Status.xml”.-Policy can take a set of FileInfo objects, such as the piped output of a Get-ChildItem cmdlet that is run against a folder that contains exported XML policies. -Policy can also take a set of Policy objects, such as the piped output of a Get-ChildItem run against a SQLSERVER:SQLPolicy path.

-TargetExpression <string>

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 expression 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.

-TargetObjects <PSObject[]>

  • This value is required

Specifies a PSObject or an array of PSObjects that define the set of SQL Server objects against which the policy 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.

-TargetServerName <psobject>

  • This value is required

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

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Examples
  1. Evaluate a policy on the default instance of the specified computer:
    C:PS> sl "C:Program FilesMicrosoft SQL Server"
    sl "100ToolsPoliciesDatabaseEngine1033"
    Invoke-PolicyEvaluation -Policy "Trustworthy Database.xml" -TargetServer "MYCOMPUTER"
    

    Read the policy from an XML file and connect by using Windows Authentication.

  2. Use Get-Childitem to read two policies from XML files in a folder, and then pipe them to Invoke-PolicyEvaluation:
    C:PS> sl "C:Program FilesMicrosoft SQL Server"
    sl "100ToolsPoliciesDatabaseEngine1033"
    gci "Database Status.xml", "Trustworthy Database.xml" | Invoke-PolicyEvaluation -TargetServer "MYCOMPUTER"
    
  3. 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:
    C:PS> sl "C:Program FilesMicrosoft SQL Server"
    sl "100ToolsPoliciesDatabaseEngine1033"
    Invoke-PolicyEvaluation -Policy "Database Status.xml" -TargetServer "MYCOMPUTER" -OutputXML > C:MyReportFolderMyReport.xml
    
  4. Set the current path to a SQL Server policy store:
    C:PS> sl SQLSERVER:SQLPolicyMYCOMPUTERDEFAULTPolicies
    gci | Where-Object { $_.PolicyCategory -eq "Microsoft Best Practices: Maintenance" } | Invoke-PolicyEvaluation -TargetServer "MYCOMPUTER"
    

    Use Get-ChildItem to read all the polices, and Where-Object to filter for the policies that have their PolicyCategory property set to “Microsoft Best Practices: Maintenance”. Then pipe the filtered set of policies to Invoke-PolicyEvaluation.

  5. How to use an Sfc.SqlStoreConnection object to specify the target server:
    C:PS> sl "C:Program FilesMicrosoft SQL Server"
    sl "100ToolsPoliciesDatabaseEngine1033"
    $conn = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection("server='MYCOMPUTER';Trusted_Connection=True")
    Invoke-PolicyEvaluation -Policy "Database Status.xml" -TargetServer $conn
    
  6. 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:
    C:PS> sl "C:program filesMicrosoft SQL Server100"
    sl "toolsPoliciesanalysisservices1033"
    [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
    
  7. Using a -TargetExpression query expression to specify that the Database Status policy be evaluated against the AdventureWorks2008R2 sample database:
    C:PS> sl "C:Program FilesMicrosoft SQL Server"
    sl "100ToolsPoliciesDatabaseEngine1033"
    Invoke-PolicyEvaluation "Database Status.xml" -TargetServer "MYCOMPUTER" -TargetExpression "Server[@Name='MYCOMPUTER']/Database[@Name='AdventureWorks2008R2']"
    
  8. How to load 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:
    C:PS> sl "C:Program FilesMicrosoft SQL Server100ToolsPoliciesReportingServices1033"
    [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
    
Additional Notes