Set-SqlAvailabilityReplica

Module:: SqlServer Module

Sets the settings on an availability replica.

Syntax

ByPath (Default)

Set-SqlAvailabilityReplica
    [[-Path] <String>]
    [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
    [-FailoverMode <AvailabilityReplicaFailoverMode>]
    [-EndpointUrl <String>]
    [-SessionTimeout <Int32>]
    [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
    [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
    [-SeedingMode <AvailabilityReplicaSeedingMode>]
    [-BackupPriority <Int32>]
    [-ReadOnlyRoutingList <String[]>]
    [-ReadonlyRoutingConnectionUrl <String>]
    [-LoadBalancedReadOnlyRoutingList <String[][]>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

ByObject

Set-SqlAvailabilityReplica
    [-InputObject] <AvailabilityReplica>
    [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
    [-FailoverMode <AvailabilityReplicaFailoverMode>]
    [-EndpointUrl <String>]
    [-SessionTimeout <Int32>]
    [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
    [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
    [-SeedingMode <AvailabilityReplicaSeedingMode>]
    [-BackupPriority <Int32>]
    [-ReadOnlyRoutingList <String[]>]
    [-ReadonlyRoutingConnectionUrl <String>]
    [-LoadBalancedReadOnlyRoutingList <String[][]>]
    [-Script]
    [-AccessToken <PSObject>]
    [-TrustServerCertificate]
    [-HostNameInCertificate <String>]
    [-Encrypt <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Description

The Set-SqlAvailabilityReplica cmdlet sets or modifies a variety of properties for an availability replica. Run this cmdlet on the server instance that hosts the primary replica.

Examples

Example 1: Modify a replica availability mode and automatic failover

PS C:\> Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -FailoverMode Automatic -Path "SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MainAG\AvailabilityReplicas\Replica02"

This command modifies the replica named Replica02 in the availability group named MainAG to use synchronous-commit availability mode and to support automatic failover.

Example 2: Modify a replica to support forced manual failover

PS C:\> Set-SqlAvailabilityReplica -AvailabilityMode AsynchronousCommit -FailoverMode Manual -Path "SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MainAG\AvailabilityReplicas\Replica02"

This command modifies the replica named Replica02 in the availability group named MainAG to use asynchronous-commit availability mode and to support only forced manual failover, which could incur data loss.

Example 3: Allow all connections in the secondary role

PS C:\> Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole AllowAllConnections -Path "SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MainAG\AvailabilityReplicas\Replica02"

This command modifies the replica Replica02 in the availability group MainAG to allow all connections in the secondary role. This lets you offload read-only data processing workloads to secondary replicas.

Example 4: Configure a primary replica and secondary replica for readonly routing

PS C:\> Set-Location "SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MainAG"
PS C:\> $PrimaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
PS C:\> $SecondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"
PS C:\> Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:5022" -InputObject $PrimaryReplica
PS C:\> Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:5022" -InputObject $SecondaryReplica
PS C:\> Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $PrimaryReplica

The first command changes location to a location in the SQLSERVER: provider.

The fourth command assigns a read-only routing URL to the primary replica. Then it sets the read-only routing list on the primary replica.

Example 5: Modify backup priority

PS C:\> Set-SqlAvailabilityReplica -BackupPriority 60 -Path "SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MainAG\AvailabilityReplicas\Replica02"

This command sets the backup priority of the availability replica Replica02 to 60. This priority is used by the server instance that hosts the primary replica to decide which replica should service an automated backup request on a database in the availability group. The replica that has the highest priority is chosen.

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.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Parameter properties

Type:	PSObject
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-AvailabilityMode

Specifies the replica availability mode.

You can specify a value of $Null.

Parameter properties

Type:	AvailabilityReplicaAvailabilityMode
Default value:	None
Accepted values:	AsynchronousCommit, SynchronousCommit, Unknown, ConfigurationOnly
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-BackupPriority

Specifies the desired priority of the replicas in performing backups. The acceptable values for this parameter are integers from 0 through 100. Of the set of replicas which are online and available, the replica that has the highest priority performs the backup.

A value of zero (0) indicates that the replica is not a candidate.

Parameter properties

Type:	Int32
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-Confirm

Prompts you for confirmation before running the cmdlet.

Parameter properties

Type:	SwitchParameter
Default value:	False
Supports wildcards:	False
DontShow:	False
Aliases:	cf

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-ConnectionModeInPrimaryRole

Specifies how the availability replica handles connections when in the primary role. The acceptable values for this parameter are:

AllowReadWriteConnections. Allows read/write connections.
AllowAllConnections. Allows all connections.

Parameter properties

Type:	AvailabilityReplicaConnectionModeInPrimaryRole
Default value:	None
Accepted values:	AllowAllConnections, AllowReadWriteConnections, Unknown
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-ConnectionModeInSecondaryRole

Specifies how the availability replica handles connections when in the secondary role. The acceptable values for this parameter are:

AllowNoConnections. Disallows connections.
AllowReadIntentConnectionsOnly. Allows only read-intent connections.
AllowAllConnections. Allows all connections.

Parameter properties

Type:	AvailabilityReplicaConnectionModeInSecondaryRole
Default value:	None
Accepted values:	AllowNoConnections, AllowReadIntentConnectionsOnly, AllowAllConnections, Unknown
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	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.

Parameter properties

Type:	String
Default value:	None
Accepted values:	Mandatory, Optional, Strict
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-EndpointUrl

Specifies the URL of the database mirroring endpoint. This URL is a TCP address in the following form: TCP://system-address:port

Parameter properties

Type:	String
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-FailoverMode

Specifies the failover mode.

You can specify a value of $Null.

Parameter properties

Type:	AvailabilityReplicaFailoverMode
Default value:	None
Accepted values:	Automatic, Manual, External, Unknown
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	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.

Parameter properties

Type:	String
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	False
Value from remaining arguments:	False

-InputObject

Specifies the availability group, as an AvailabilityGroup object, to which the replica belongs.

Parameter properties

Type:	AvailabilityReplica
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

ByObject

Position:	1
Mandatory:	True
Value from pipeline:	True
Value from pipeline by property name:	False
Value from remaining arguments:	False

-LoadBalancedReadOnlyRoutingList

Specifies the load-balanced read-only routing list.

The routing list is a list of load-balanced sets, which in turn are lists of replicas.

For example, passing a value like

@('Server1','Server2'),@('Server3'),@('Server4')

means what we are passing 3 load-balanced sets: 1 with 2 replicas (Server1 and Server2) and 2 with just one (Server3 and Server4, respectively).

At runtime, SQL Server will look sequentially at all the load-balanced sets until finds one such that at least on replica in it is available and use it for load-balancing.

So, in the example above, if both Server1 and Server2 are not available, but Server3 is, SQL Server will pick Server3.

This cmdlet only sets the read-only routing list and does not check on the availablility of the specified replicas.