commands

^

Set-DbaAgReplica

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Set-DbaAgReplica on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaAgReplica.

Synopsis

Modifies configuration properties of existing availability group replicas.

Description

Modifies configuration properties of existing availability group replicas such as availability mode, failover behavior, backup priority, and read-only routing settings. This function is used for ongoing management and tuning of availability groups after initial setup, allowing you to adjust replica behavior without recreating the availability group.

Common use cases include changing synchronous replicas to asynchronous for performance, adjusting backup priorities to control where backups run, configuring automatic failover settings, and setting up read-only routing for load balancing read workloads across secondary replicas.

Syntax

Set-DbaAgReplica
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-AvailabilityGroup] <String>]
    [[-Replica] <String>]
    [[-AvailabilityMode] <String>]
    [[-FailoverMode] <String>]
    [[-BackupPriority] <Int32>]
    [[-ConnectionModeInPrimaryRole] <String>]
    [[-ConnectionModeInSecondaryRole] <String>]
    [[-SeedingMode] <String>]
    [[-SessionTimeout] <Int32>]
    [[-EndpointUrl] <String>]
    [[-ReadonlyRoutingConnectionUrl] <String>]
    [[-ReadOnlyRoutingList] <Object[]>]
    [[-InputObject] <AvailabilityReplica>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Set-DbaAgReplica -SqlInstance sql2016 -Replica sql2016 -AvailabilityGroup SharePoint -BackupPriority 5000

Sets the backup priority to 5000 for the sql2016 replica for the SharePoint availability group on sql2016

Example: 2
PS C:\> Get-DbaAgReplica -SqlInstance sql2016 | Out-GridView -Passthru | Set-DbaAgReplica -BackupPriority 5000

Sets the backup priority to 5000 for the selected availability groups.

Example: 3
PS C:\> Get-DbaAgReplica -SqlInstance sql2016 -Replica Replica1 |
>> Set-DbaAgReplica -ReadOnlyRoutingList Replica2, Replica3

Equivalent to running "ALTER AVAILABILITY GROUP... MODIFY REPLICA... (READ_ONLY_ROUTING_LIST = ('Replica2', 'Replica3'));"

Example: 4
PS C:\> Get-DbaAgReplica -SqlInstance sql2016 -Replica Replica1 |
>> Set-DbaAgReplica -ReadOnlyRoutingList @(,('Replica2','Replica3'));

Equivalent to running "ALTER AVAILABILITY GROUP... MODIFY REPLICA... (READ_ONLY_ROUTING_LIST = (('Replica2', 'Replica3')));" setting a load balanced routing list for when Replica1 is the primary
replica.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-AvailabilityGroup

Specifies the name of the availability group that contains the replica to modify.
Required when using SqlInstance parameter to identify which availability group the replica belongs to.

Alias
Required False
Pipeline false
Default Value
-Replica

Specifies the name of the availability group replica to modify. This is the server instance name that hosts the replica.
Use this when targeting a specific replica within an availability group for configuration changes.

Alias
Required False
Pipeline false
Default Value
-AvailabilityMode

Controls the data synchronization mode between primary and secondary replicas. SynchronousCommit ensures zero data loss but may impact performance, while AsynchronousCommit prioritizes performance
over guaranteed data protection.
Change this when you need to balance performance requirements against data protection needs across different replicas.

Alias
Required False
Pipeline false
Default Value
Accepted Values AsynchronousCommit,SynchronousCommit
-FailoverMode

Determines whether the replica can automatically failover when the primary becomes unavailable. Automatic failover requires SynchronousCommit availability mode and is typically used for high
availability scenarios.
Set to Manual when you want to control failover decisions or when using AsynchronousCommit replicas.

Alias
Required False
Pipeline false
Default Value
Accepted Values Automatic,Manual,External
-BackupPriority

Sets the backup priority for this replica on a scale of 0-100, where higher values indicate higher priority for backup operations.
Use this to control which replica should be preferred for automated backup jobs, with 0 excluding the replica from backup consideration entirely.

Alias
Required False
Pipeline false
Default Value 0
-ConnectionModeInPrimaryRole

Controls what types of connections are allowed when this replica is the primary. AllowAllConnections permits both read-write and read-only connections, while AllowReadWriteConnections only allows
read-write access.
Typically left as AllowAllConnections unless you need to restrict read-only workloads from connecting to the primary.

Alias
Required False
Pipeline false
Default Value
Accepted Values AllowAllConnections,AllowReadWriteConnections
-ConnectionModeInSecondaryRole

Determines connection access when this replica is secondary. Options include AllowNoConnections, AllowReadIntentConnectionsOnly (for read-only workloads), or AllowAllConnections.
Configure this to enable read-only workloads on secondary replicas for reporting or to completely block connections for backup-only replicas.

Alias
Required False
Pipeline false
Default Value
Accepted Values AllowAllConnections,AllowNoConnections,AllowReadIntentConnectionsOnly,No,Read-intent only,Yes
-SeedingMode

Controls the database initialization method for new databases added to the availability group. Automatic performs direct seeding over the network without manual backup/restore steps, while Manual
requires traditional backup and restore operations.
Choose Automatic for convenience and reduced administrative overhead, or Manual when you need control over backup/restore timing or have network bandwidth constraints.

Alias
Required False
Pipeline false
Default Value
Accepted Values Automatic,Manual
-SessionTimeout

Sets the timeout period in seconds for detecting communication failures between availability replicas. Values below 10 seconds can cause false failure detection in busy environments.
Increase this value in high-latency network environments or decrease it when you need faster failure detection, keeping the 10-second minimum recommendation in mind.

Alias
Required False
Pipeline false
Default Value 0
-EndpointUrl

Specifies the URL endpoint used for data mirroring communication between replicas, typically in the format 'TCP://servername:port'.
Update this when changing network configurations, server names, or port assignments for availability group communication.

Alias
Required False
Pipeline false
Default Value
-ReadonlyRoutingConnectionUrl

Specifies the connection string used by the availability group listener to route read-only connections to this secondary replica.
Required when setting up read-only routing to distribute read workloads across secondary replicas for load balancing.

Alias
Required False
Pipeline false
Default Value
-ReadOnlyRoutingList

Defines the ordered list of secondary replicas that should receive read-only connections when this replica is primary. Accepts arrays for load-balanced routing or simple arrays for priority-based
routing.
Use this to establish read-only routing policies that distribute read workloads across available secondary replicas.

Alias
Required False
Pipeline false
Default Value
-InputObject

Accepts availability group replica objects from Get-DbaAgReplica for pipeline operations.
Use this to modify multiple replicas or when working with replica objects retrieved from previous commands.

Alias
Required False
Pipeline true (ByValue)
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value