Share via

Test-SqlAvailabilityReplica

Module:
SqlServer

Evaluates the health of availability replicas.

Syntax

Test-SqlAvailabilityReplica
    [-ShowPolicyDetails]
    [-AllowUserPolicies]
    [-NoRefresh]
    [[-Path] <String[]>]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]
Test-SqlAvailabilityReplica
    [-ShowPolicyDetails]
    [-AllowUserPolicies]
    [-NoRefresh]
    [-InputObject] <AvailabilityReplica[]>
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Description

The Test-SqlAvailabilityReplica cmdlet assesses the health of 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 replica

PS C:\> Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\AvailabilityReplicas\MainReplica"

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

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

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

This command evaluates the health of all availability replicas in the availability group named MainAg and outputs a brief summary for each replica.

Example 3: Evaluate the health of an availability replica for each PBM policy

PS C:\> Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\AvailabilityReplicas\MainReplica" -ShowPolicyDetails

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

Example 4: Evaluate the health of an availability replica and include userdefined policies

PS C:\> Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAg\AvailabilityReplicas\MainReplica" -AllowUserPolicies

This command evaluates the health of the availability replica named MainReplica in the MainAg availability group. User-defined policies are included in this evaluation.

Example 5: Show all availability replicas that are in an error state

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

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

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity (see references at the bottom of this page)

In common scenarios, this parameter is obtained with something like (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token (requires the Az.Accounts module)

The type of the parameter can be either string (clear text token) or SecureString (encrypted token, as returned by newer version of the Get-AzAccessToken cmdlet.)

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

-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

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

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

-InputObject

Specifies an array of availability replicas to evaluate.

Type:AvailabilityReplica[]
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 availability replicas. This parameter is optional. If not specified, 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 the result of each policy evaluation performed by this cmdlet is shown. The cmdlet outputs one object per policy evaluation. This object contains fields that describe the results of the evaluation.

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

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

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.AvailabilityReplica[]