Set-SqlAvailabilityReplica

This cmdlet modifies the settings on an existing availability replica.
Set-SqlAvailabilityReplica [[-Path] <string>] [-AvailabilityMode <AvailabilityReplicaAvailabilityMode>][-BackupPriority <int>] [-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>][-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>] [-EndpointUrl <string>][-FailoverMode <AvailabilityReplicaFailoverMode>] [-ReadonlyRoutingConnectionUrl <string>] [-ReadonlyRoutingList<string[]>] [-Script] [-SessionTimeout <int>] [-Confirm] [-WhatIf] [<CommonParameters>]
Set-SqlAvailabilityReplica [-InputObject*] <AvailabilityReplica> [-AvailabilityMode<AvailabilityReplicaAvailabilityMode>] [-BackupPriority <int>] [-ConnectionModeInPrimaryRole<AvailabilityReplicaConnectionModeInPrimaryRole>] [-ConnectionModeInSecondaryRole<AvailabilityReplicaConnectionModeInSecondaryRole>] [-EndpointUrl <string>] [-FailoverMode<AvailabilityReplicaFailoverMode>] [-ReadonlyRoutingConnectionUrl <string>] [-ReadonlyRoutingList <string[]>][-Script] [-SessionTimeout <int>] [-Confirm] [-WhatIf] [<CommonParameters>]

The Set-SqlAvailabilityReplica cmdlet sets or modifies a variety of properties for an availability replica. This cmdlet can be executed only at the server instance that hosts the primary replica.

Parameters
-AvailabilityMode <AvailabilityReplicaAvailabilityMode>

The availability mode of the replica. Options are: SynchronousCommit or AsynchronousCommit.

-BackupPriority <int>

This option is an integer in the range of 0..100, and represents the desired priority of the replicas in performing backups. Of the set of replicas which are online and available, the replica with the highest priority will be chosen to perform the backup.

-ConnectionModeInPrimaryRole <AvailabilityReplicaConnectionModeInPrimaryRole>

How the availability replica should handle connections when in the primary role. Valid settings are:AllowReadWriteConnections: Allow read/write connectionsAllowAllConnections: Allow all connections

-ConnectionModeInSecondaryRole <AvailabilityReplicaConnectionModeInSecondaryRole>

How the availability replica should handle connections when in the secondary role. Valid settings are:AllowNoConnections: Disallows connectionsAllowReadIntentConnectionsOnly: Allows only read-intent connectionsAllowAllConnections: Allows all connections

-EndpointUrl <string>

The URL of the endpoint in the format of TCP://System-address:port

-FailoverMode <AvailabilityReplicaFailoverMode>

The failover mode of the replica. Options are: Manual or Automatic.

-InputObject <AvailabilityReplica>

  • This value is required
  • Accepts pipeline input ByValue

The AvailabilityReplica object of the replica to be modified.

-Path <string>

The path to the availability replica. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-ReadonlyRoutingConnectionUrl <string>

The connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections. For example: TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024

-ReadonlyRoutingList <string[]>

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 is applicable when the availability replica is the current primary of the availability group.

-Script [<SwitchParameter>]

Outputs a Transact-SQL script that performs the task executed by this cmdlet.

-SessionTimeout <int>

The number of seconds to wait for a response between the primary replica and this replica before considering the connection failed.

-Confirm [<SwitchParameter>]

Prompts you for confirmation before executing the command.

-WhatIf [<SwitchParameter>]

Describes what would happen if you executed the command without actually executing the command.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

Microsoft.SqlServer.Management.Smo.AvailabilityReplica

The Availability Replica that is to be modified.

Examples
  1. This command modifies the replica ‘MyReplica’ in the availability group ‘MyAg’ to use synchronous-commit availability mode and to support automatic failover:
    C:PS> Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAgAvailabilityReplicasMyReplica
    
  2. This command modifies the replica ‘MyReplica’ in the availability group ‘MyAg’ to use asynchronous-commit availability mode and to support only forced manual failover (with possible data loss):
    C:PS> Set-SqlAvailabilityReplica -AvailabilityMode "AsynchronousCommit" -FailoverMode "Manual" -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAgAvailabilityReplicasMyReplica
    
  3. This command modifies the replica ‘MyReplica’ in the availability group ‘MyAg’ to allow all connections in the secondary role:
    C:PS> Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAgAvailabilityReplicasMyReplica
    

    This is useful if you want to offload read-only data processing workloads to secondary replicas.

  4. This example configures the primary replica and one secondary replica in an availability group for read-only routing:
    C:PS> Set-Location SQLSERVER:SQLPrimaryServerdefaultAvailabilityGroupsMyAg
    $primaryReplica = Get-Item "AvailabilityReplicasPrimaryServer"
    $secondaryReplica = Get-Item "AvailabilityReplicasSecondaryServer"
    
       Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:5022" -InputObject 
       $primaryReplica
       Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:5022" -InputObject 
       $secondaryReplica
       Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

    If the ConnectionModeInSecondaryRole option is set to AllowAllConnections or AllowReadIntentConnectionsOnly, read-only routing allows the secondary replica to receive and accept read requests when the client connects to the availability group through a virtual network name. First, the example assigns a read-only routing URL to each replica. Then it sets the read-only routing list on the primary replica. Connections with the “ReadOnly” property set in the connection string will be redirected to the secondary replica. If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

  5. This command sets the backup priority of the availability replica ‘MyReplica’ to 60:
    C:PS> Set-SqlAvailabilityReplica -BackupPriority 60 -Path SQLSERVER:SqlComputerInstanceAvailabilityGroupsMyAgAvailabilityReplicasMyReplica
    

    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 with highest priority is chosen). This priority can be any number between 0 and 100, inclusive. A priority of 0 indicates that the replica should not be considered as a candidate for servicing backup requests.

Additional Notes
 
Related Links