Set-SqlAvailabilityReplica

Sets the settings on an availability replica.

Syntax

Set-SqlAvailabilityReplica
   [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
   [-FailoverMode <AvailabilityReplicaFailoverMode>]
   [-EndpointUrl <String>]
   [-SessionTimeout <Int32>]
   [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
   [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
   [-SeedingMode <AvailabilityReplicaSeedingMode>]
   [-BackupPriority <Int32>]
   [-ReadOnlyRoutingList <String[]>]
   [-ReadonlyRoutingConnectionUrl <String>]
   [-LoadBalancedReadOnlyRoutingList <String[][]>]
   [[-Path] <String>]
   [-Script]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Set-SqlAvailabilityReplica
   [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>]
   [-FailoverMode <AvailabilityReplicaFailoverMode>]
   [-EndpointUrl <String>]
   [-SessionTimeout <Int32>]
   [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>]
   [-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>]
   [-SeedingMode <AvailabilityReplicaSeedingMode>]
   [-BackupPriority <Int32>]
   [-ReadOnlyRoutingList <String[]>]
   [-ReadonlyRoutingConnectionUrl <String>]
   [-LoadBalancedReadOnlyRoutingList <String[][]>]
   [-InputObject] <AvailabilityReplica>
   [-Script]
   [-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

-AvailabilityMode

Specifies the replica availability mode.

You can specify a value of $Null.

Type:AvailabilityReplicaAvailabilityMode
Accepted values:AsynchronousCommit, SynchronousCommit, Unknown, ConfigurationOnly
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters: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.

Type:Int32
Position:Named
Default value:None
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
Accept pipeline input:False
Accept wildcard characters: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.
Type:AvailabilityReplicaConnectionModeInPrimaryRole
Accepted values:AllowAllConnections, AllowReadWriteConnections, Unknown
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters: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.
Type:AvailabilityReplicaConnectionModeInSecondaryRole
Accepted values:AllowNoConnections, AllowReadIntentConnectionsOnly, AllowAllConnections, Unknown
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-EndpointUrl

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

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

Specifies the failover mode.

You can specify a value of $Null.

Type:AvailabilityReplicaFailoverMode
Accepted values:Automatic, Manual, External, Unknown
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-InputObject

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

Type:AvailabilityReplica
Position:1
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters: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.

Type:String[][]
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Path

Specifies the path of the availability group to which the replica belongs. If you do not specify this parameter, this cmdlet uses current working location.

Type:String
Position:1
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ReadOnlyRoutingList

Specifies an ordered list of replica server names that represent the probe sequence for connection director to use when redirecting read-only connections through this availability replica. This parameter applies if the availability replica is the current primary replica of the availability group.

Type:String[]
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ReadonlyRoutingConnectionUrl

Specifies the fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections, as in the following example: TCP://DBSERVER8.manufacturing.Contoso.com:7024

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

Indicates that this cmdlet returns a Transact-SQL script that performs the task that this cmdlet performs.

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

Specifies how the secondary replica will be initially seeded.

Allowed values:

  • Automatic. Enables direct seeding. This method will seed the secondary replica over the network. This method does not require you to backup and restore a copy of the primary database on the replica.
  • Manual. Specifies manual seeding. This method requires you to create a backup of the database on the primary replica and manually restore that backup on the secondary replica.
Type:AvailabilityReplicaSeedingMode
Accepted values:Automatic, Manual
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-SessionTimeout

Specifies the amount of time, in seconds, to wait for a response between the primary replica and this replica before the connection fails.

Type:Int32
Position:Named
Default value:None
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
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.AvailabilityReplica