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.
Modifies configuration properties of existing availability group replicas.
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.
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>]
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
PS C:\> Get-DbaAgReplica -SqlInstance sql2016 | Out-GridView -Passthru | Set-DbaAgReplica -BackupPriority 5000
Sets the backup priority to 5000 for the selected availability groups.
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'));"
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.
The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Shows what would happen if the command were to run. No actions are actually performed.
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
Prompts you for confirmation before executing any changing operations within the command.
Alias | cf |
Required | False |
Pipeline | false |
Default Value |