Set-DbaAvailabilityGroup

Synopsis

Modifies availability group configuration settings including DTC support, backup preferences, and failover conditions

Description

Modifies configuration properties of existing availability groups without requiring you to script out and recreate the entire AG setup. Commonly used to enable DTC support for distributed transactions, adjust automated backup preferences across replicas, configure failure condition levels for automatic failover, and set health check timeouts for monitoring. This saves time compared to using SQL Server Management Studio or T-SQL ALTER AVAILABILITY GROUP statements for routine configuration changes.

Syntax

Set-DbaAvailabilityGroup
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-AvailabilityGroup] <String[]>]
    [-AllAvailabilityGroups]
    [-DtcSupportEnabled]
    [[-ClusterType] <String>]
    [[-AutomatedBackupPreference] <String>]
    [[-FailureConditionLevel] <String>]
    [[-HealthCheckTimeout] <Int32>]
    [-BasicAvailabilityGroup]
    [-DatabaseHealthTrigger]
    [-IsDistributedAvailabilityGroup]
    [[-InputObject] <AvailabilityGroup[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2016 | Set-DbaAvailabilityGroup -DtcSupportEnabled

Enables DTC for all availability groups on sql2016

Example: 2

PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 | Set-DbaAvailabilityGroup -DtcSupportEnabled:$false

Disables DTC support for the availability group AG1

Example: 3

PS C:\> Set-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 -DtcSupportEnabled:$false

Disables DTC support for the availability group AG1

Optional Parameters

-SqlInstance

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

-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.

-AvailabilityGroup

Specifies the name(s) of specific availability groups to modify. Accepts multiple AG names as an array.
Use this to target individual AGs instead of modifying all AGs on the instance.

-AllAvailabilityGroups

Modifies configuration settings for every availability group on the target SQL Server instance.
Use this switch when you need to apply the same configuration changes across all AGs simultaneously.

-DtcSupportEnabled

Enables or disables Distributed Transaction Coordinator (DTC) support for the availability group.
Required when applications use distributed transactions across multiple databases in the AG. Set to $false to disable DTC support.

-ClusterType

Specifies the clustering technology used by the availability group. Only supported in SQL Server 2017 and above.
Use ‘Wsfc’ for Windows Server Failover Clustering, ‘External’ for third-party cluster managers like Pacemaker on Linux, or ‘None’ for read-scale AGs without automatic failover.

-AutomatedBackupPreference

Controls which replica should be preferred for automated backup operations within the availability group.
Use ‘Secondary’ to offload backups from the primary, ‘SecondaryOnly’ to prevent backups on primary, ‘Primary’ to always backup on primary, or ‘None’ to disable preference-based backup routing.

-FailureConditionLevel

Sets the sensitivity level for automatic failover conditions in the availability group.
Use ‘OnServerDown’ for basic failover, ‘OnServerUnresponsive’ for SQL Service issues, ‘OnCriticalServerErrors’ for critical SQL errors, ‘OnModerateServerErrors’ for moderate SQL errors, or
‘OnAnyQualifiedFailureCondition’ for maximum sensitivity.

-HealthCheckTimeout

Sets the timeout in milliseconds for health check responses from sp_server_diagnostics before marking the AG as unresponsive.
Increase this value for busy systems or slow storage to reduce false failovers. Decrease for faster failover detection in stable environments.
Default is 30000 (30 seconds). Changes take effect immediately without restart.

-BasicAvailabilityGroup

Configures the availability group as a Basic AG with limited functionality for Standard Edition licensing.
Basic AGs support only one database, two replicas, and no read-access to secondary replicas. Used when full AG features aren’t needed or licensed.

-DatabaseHealthTrigger

Enables database-level health monitoring that can trigger automatic failovers based on individual database health status.
When enabled, databases that become offline or experience critical errors can initiate AG failover. Useful for comprehensive monitoring beyond SQL Server instance health.

-IsDistributedAvailabilityGroup

Configures the availability group as a Distributed AG that spans multiple WSFC clusters or standalone instances.
Used for disaster recovery scenarios across geographic locations or different domains. Requires SQL Server 2016 or later.

-InputObject

Accepts availability group objects from Get-DbaAvailabilityGroup for pipeline operations.
Use this to pipe specific AG objects directly to the function instead of specifying SqlInstance and AG names separately.

-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.

-WhatIf

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

-Confirm

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