Set-SqlAvailabilityReplica

Set-SqlAvailabilityReplica
    [[-Path] <String>]
    [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
    [-FailoverMode <AvailabilityReplicaFailoverMode>]
    [-EndpointUrl <String>]
    [-SessionTimeout <Int32>]
    [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
    [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
    [-BackupPriority <Int32>]
    [-ReadOnlyRoutingList <String[]>]
    [-ReadonlyRoutingConnectionUrl <String>]
    [-Script]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Set-SqlAvailabilityReplica
    [-InputObject] <AvailabilityReplica>
    [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
    [-FailoverMode <AvailabilityReplicaFailoverMode>]
    [-EndpointUrl <String>]
    [-SessionTimeout <Int32>]
    [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
    [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
    [-BackupPriority <Int32>]
    [-ReadOnlyRoutingList <String[]>]
    [-ReadonlyRoutingConnectionUrl <String>]
    [-Script]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

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.

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.

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.

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.

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

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

The second command gets the replica for the primary server, and then stores it in the $PrimaryReplica variable.

The third command gets the replica for the secondary server, and then stores it in the $SecondaryReplica variable.

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.

The fifth command assigns a read-only routing URL to the secondary replica.

The sixth command sets the read-only routing list on the primary replica. Connections that have with the ReadOnly property connection string are redirected to the secondary replica. If the secondary replica is not readable, the connection is directed back to the primary replica.

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.

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.