Test-SqlDatabaseReplicaState

Evaluates the health of an availability database on all joined availability replicas by evaluating SQL Serverpolicy-based management (PBM) policies.
Test-SqlDatabaseReplicaState [-InputObject*] <DatabaseReplicaState[]> [-AllowUserPolicies] [-NoRefresh][-ShowPolicyDetails] [<CommonParameters>]
Test-SqlDatabaseReplicaState [[-Path] <string[]>] [-AllowUserPolicies] [-NoRefresh] [-ShowPolicyDetails][<CommonParameters>]

The Test-SqlDatabaseReplicaState cmdlet assesses the health of an availability database on all joined availability replicas by evaluating SQL Server policy based management (PBM) policies. You must have CONNECT, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions to execute this cmdlet.

Parameters
-AllowUserPolicies [<SwitchParameter>]

Runs user policies found in the AlwaysOn policy categories.

-InputObject <DatabaseReplicaState[]>

  • This value is required
  • Accepts pipeline input ByValue

A collection of objects representing availability database states. This cmdlet will compute the health of these availability databases.

-NoRefresh [<SwitchParameter>]

When this parameter is set, the cmdlet will not manually refresh the objects specified by the -Path or -InputObject parameter.

-Path <string[]>

The path to the database replica cluster state of the availability database. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-ShowPolicyDetails [<SwitchParameter>]

Shows the result of each policy evaluation performed by this cmdlet. The cmdlet outputs one object per policy evaluation, and this object has fields describing the results of evaluation (whether the policy passed or not, the policy name and category, and so forth).

<CommonParameters>

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

Inputs

Microsoft.SqlServer.Management.Smo.DatabaseReplicaState

Examples
  1. This command evaluates the health of the availability database named ‘MyDatabase’ on the availability replica ‘MyReplica’ in the availability group ‘MyAg’ and outputs a brief summary:
    C:PS> $path = "SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgDatabaseReplicaStatesMyReplica.MyDatabase"
    Test-SqlDatabaseReplicaState -Path $path
    
  2. This command evaluates the health of all availability databases in the availability group ‘MyAg’ and outputs a brief summary for each database:
    C:PS> Get-ChildItem SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgDatabaseReplicaStates | Test-SqlDatabaseReplicaState
    
  3. This command evaluates the health of all availability databases in the availability group ‘MyAg’ and outputs the evaluation results for each policy-based management (PBM) policy that was executed:
    C:PS> dir SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgDatabaseReplicaStates | Test-SqlDatabaseReplicaState -ShowPolicyDetails
    
  4. This command evaluates the health of all availability databases in the availability group ‘MyAg’:
    C:PS> dir SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgDatabaseReplicaStates | Test-SqlDatabaseReplicaState -AllowUserPolicies
    

    User-defined policies are included in this evaluation.

  5. This command shows all availability databases with a health state of “Error” in the availability group ‘MyAg’:
    C:PS> Get-ChildItem SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgDatabaseReplicaStates | Test-SqlDatabaseReplicaState | Where-Object { $_.HealthState -eq "Error" }
    
Additional Notes
 
Related Links