Share via

Test-SqlDatabaseReplicaState

  • Reference
Module:
SQLServer

Evaluates the health of an availability database.

Syntax

Test-SqlDatabaseReplicaState
    [-ShowPolicyDetails]
    [-AllowUserPolicies]
    [-NoRefresh]
    [[-Path] <String[]>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]
Test-SqlDatabaseReplicaState
    [-ShowPolicyDetails]
    [-AllowUserPolicies]
    [-NoRefresh]
    [-InputObject] <DatabaseReplicaState[]>
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Description

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.

Module requirements: version 21+ on PowerShell 5.1; version 22+ on PowerShell 7.x.

Examples

Example 1: Evaluate the health of an availability database

PS C:\> $Path = "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\DatabaseReplicaStates\MainReplica.MainDatabase"
PS C:\> Test-SqlDatabaseReplicaState -Path $Path

This command evaluates the health of the availability database named MainDatabase on the availability replica MainReplica in the availability group MainAg and outputs a brief summary.

Example 2: Evaluate the health of all availability databases in an availability group

PS C:\> Get-ChildItem "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\DatabaseReplicaStates" | Test-SqlDatabaseReplicaState

This command evaluates the health of all availability databases in the MainAg availability group and outputs a brief summary for each database.

Example 3: Evaluate the health of all availability databases in an availability group showing PBM evaluation results

PS C:\> Get-ChildItem "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\DatabaseReplicaStates" | Test-SqlDatabaseReplicaState -ShowPolicyDetails

This command evaluates the health of all availability databases in the MainAg availability group and outputs the evaluation results for each PBM policy that was executed.

Example 4: Evaluate the health of all availability databases in an availability group and include userdefined policies

PS C:\> Get-ChildItem "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\DatabaseReplicaStates" | Test-SqlDatabaseReplicaState -AllowUserPolicies

This command evaluates the health of all availability databases in the MainAg availability group. User-defined policies are included in this evaluation.

Example 5: Show all availability databases in an error health state

PS C:\> Get-ChildItem "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\DatabaseReplicaStates" | Test-SqlDatabaseReplicaState | Where-Object { $_.HealthState -eq "Error" }

This command shows all availability databases with a health state of Error in the MainAg availability group.

Parameters

-AllowUserPolicies

Indicates that this cmdlet runs user policies found in the Always On policy categories.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies an array of availability database state objects. This cmdlet computes the health of these availability databases.

Type:DatabaseReplicaState[]
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-NoRefresh

Indicates that this cmdlet will not manually refresh the objects specified by the Path or InputObject parameters.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Path

Specifies the path to one or more database replica cluster states of the availability database. This is an optional parameter. If not specified, the value of the current working location is used.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ShowPolicyDetails

Indicates that this cmdlet shows the result of each policy evaluation performed. The cmdlet outputs one object per policy evaluation and the results of evaluation are available in the fields of the object.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:SwitchParameter
Aliases:wi
Position:Named
Default value:False
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.DatabaseReplicaState[]