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]
    [[-ClusterConnectionOption] <String>]
    [[-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.

-ClusterConnectionOption

Specifies connection options for TDS 8.0 support in SQL Server 2025 and above.
This allows the Windows Server Failover Cluster (WSFC) to connect to SQL Server instances using ODBC with TLS 1.3 encryption.
The value is a string containing semicolon-delimited key-value pairs.
Available keys:

• Encrypt: Controls connection encryption
• TrustServerCertificate: Whether to trust the server certificate
• HostNameInCertificate: Expected hostname in the certificate
• ServerCertificate: Path to server certificate
  This setting is persisted by WSFC in the registry and used continuously for cluster-to-instance communication.
  Note: PowerShell does not validate these values - invalid combinations will be rejected by SMO or the ODBC driver.
  Example: “Encrypt=Strict;TrustServerCertificate=False”
  For detailed documentation, see:
  https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

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

Outputs

Microsoft.SqlServer.Management.Smo.AvailabilityGroup

Returns one AvailabilityGroup object per availability group that was modified. The object contains the updated configuration properties that were changed by this command.

Default display properties (via Select-DefaultView):

• Name: Name of the availability group
• AvailabilityReplicas: Collection of replica instances in the AG
• AutomatedBackupPreference: Current backup preference (None, Primary, Secondary, SecondaryOnly)
• BasicAvailabilityGroup: Boolean indicating if the AG is a Basic AG (Standard Edition)
• ClusterType: Clustering technology used (Wsfc, External, None) - SQL Server 2017+
• DatabaseHealthTrigger: Boolean indicating if database health triggers failover
• DtcSupportEnabled: Boolean indicating if Distributed Transaction Coordinator support is enabled
• FailureConditionLevel: Failover sensitivity level (OnServerDown, OnServerUnresponsive, OnCriticalServerErrors, OnModerateServerErrors, OnAnyQualifiedFailureCondition)
• HealthCheckTimeout: Health check timeout in milliseconds
• IsDistributedAvailabilityGroup: Boolean indicating if this is a Distributed AG (SQL Server 2016+)

Additional properties available (from SMO AvailabilityGroup object):

• ClusterConnectionOptions: Connection options for WSFC communication (SQL Server 2025+)
• Parent: Reference to the parent SQL Server object
• Databases: Collection of databases in the AG
• ListenerIPAddresses: Collection of listener IP addresses
• AvailabilityGroupListeners: Collection of AG listeners
• CreateDate: DateTime when the AG was created
• LastModificationTime: DateTime when the AG was last modified
• Urn: The Unified Resource Name for the AG
• State: Current state of the SMO object (Existing, Creating, Pending, etc.) All properties from the base SMO AvailabilityGroup object are accessible using Select-Object * even though only default properties are displayed without that cmdlet.