Set-SqlAvailabilityGroup

This cmdlet modifies settings on an existing availability group.

Set-SqlAvailabilityGroup [-InputObject*] <AvailabilityGroup> [-AutomatedBackupPreference<AvailabilityGroupAutomatedBackupPreference>] [-FailureConditionLevel <AvailabilityGroupFailureConditionLevel>][-HealthCheckTimeout <int>] [-Script] [<CommonParameters>]

Set-SqlAvailabilityGroup [[-Path] <string>] [-AutomatedBackupPreference<AvailabilityGroupAutomatedBackupPreference>] [-FailureConditionLevel <AvailabilityGroupFailureConditionLevel>][-HealthCheckTimeout <int>] [-Script] [<CommonParameters>]

The Set-SqlAvailabilityGroup cmdlet modifies the AutomatedBackupPreference, FailureConditionLevel, and HealthCheckTimeout settings on an existing availability group. This cmdlet can be executed only at the server instance that hosts the primary replica.

Parameters
`-AutomatedBackupPreference <AvailabilityGroupAutomatedBackupPreference>` The automated backup preference for the availability group. Valid options are:Primary: Specifies that the backups always occur on the primary replica, wherever it is. This is often because the user needs features not available when backup is run on a secondary, such as differential backups.SecondaryOnly: Specifies that backups are never performed on primary replicas. If the primary replica is the only replica online, the backup will not occur.Secondary: Specifies that backups occur on secondary replicas unless the primary replica is the only replica online. Then the backup occurs on the primary replica.None: This option specifies that you do not want primary/secondary status taken into account when choosing the replica to perform backups. The BackupPriority in combination with the Online/Connected status are the only factors considered.
`-FailureConditionLevel <AvailabilityGroupFailureConditionLevel>` Determines the automatic failover behavior of the availability group. Valid options are:OnServerDown: Failover or restart when the SQL Server service stops.OnServerUnresponsive: Failover or restart when any condition of lower value is satisfied, plus when the SQL Server service is connected to the cluster and the “HealthCheckTimeout” threshold is exceeded, or when the Availability Replica currently in primary role is in a failed state. (See HealthCheckTimeout parameter.)OnCriticalServerError: Failover or restart when any condition of lower value is satisfied, plus when an internal critical Server error occurs (out of memory condition, serious write-access violation, or too much dumping).OnModerateServerError: Failover or restart when any condition of lower value is satisfied, plus when a moderate Server error occurs (persistent out of memory condition).OnAnyQualifiedFailureConditions: Failover or restart when any condition of lower value is satisfied, plus when a qualifying failure condition occurs (engine worker thread exhaustion, unsolvable deadlock detected).
`-HealthCheckTimeout <int>` The length of time in milliseconds after which an unresponsive server is declared unhealthy.
`-InputObject <AvailabilityGroup>` This value is required The AvailabilityGroup object of the availability group to be modified.
`-Path <string>` The path to the availability group. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.
`-Script [<SwitchParameter>]` Outputs a Transact-SQL script that performs the task executed by this cmdlet.
`<CommonParameters>` This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Parameters

-AutomatedBackupPreference <AvailabilityGroupAutomatedBackupPreference>

The automated backup preference for the availability group. Valid options are:Primary: Specifies that the backups always occur on the primary replica, wherever it is. This is often because the user needs features not available when backup is run on a secondary, such as differential backups.SecondaryOnly: Specifies that backups are never performed on primary replicas. If the primary replica is the only replica online, the backup will not occur.Secondary: Specifies that backups occur on secondary replicas unless the primary replica is the only replica online. Then the backup occurs on the primary replica.None: This option specifies that you do not want primary/secondary status taken into account when choosing the replica to perform backups. The BackupPriority in combination with the Online/Connected status are the only factors considered.

-FailureConditionLevel <AvailabilityGroupFailureConditionLevel>

Determines the automatic failover behavior of the availability group. Valid options are:OnServerDown: Failover or restart when the SQL Server service stops.OnServerUnresponsive: Failover or restart when any condition of lower value is satisfied, plus when the SQL Server service is connected to the cluster and the “HealthCheckTimeout” threshold is exceeded, or when the Availability Replica currently in primary role is in a failed state. (See HealthCheckTimeout parameter.)OnCriticalServerError: Failover or restart when any condition of lower value is satisfied, plus when an internal critical Server error occurs (out of memory condition, serious write-access violation, or too much dumping).OnModerateServerError: Failover or restart when any condition of lower value is satisfied, plus when a moderate Server error occurs (persistent out of memory condition).OnAnyQualifiedFailureConditions: Failover or restart when any condition of lower value is satisfied, plus when a qualifying failure condition occurs (engine worker thread exhaustion, unsolvable deadlock detected).

-HealthCheckTimeout <int>

The length of time in milliseconds after which an unresponsive server is declared unhealthy.

-InputObject <AvailabilityGroup>

This value is required

The AvailabilityGroup object of the availability group to be modified.

-Path <string>

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

-Script [<SwitchParameter>]

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

<CommonParameters>

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

Examples

This command sets the HealhCheckTimeout property on the availability group ‘MyAg’ to 120,000 milliseconds (two minutes):
```
C:PS> Set-SqlAvailabilityGroup -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAG -HealthCheckTimeout 120000
```
If the primary server is unresponsive for this length of time and automatic failover is enabled, an automatic failover will be initiated.
This command sets the AutomatedBackupPreference property on the availability group ‘MyAg’ to ‘SecondaryOnly’:
```
C:PS> Set-SqlAvailabilityGroup -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAg -AutomatedBackupPreference SecondaryOnly
```
Automated backups of databases in this availability group will never occur on the primary replica, but will be redirected to the secondary replica with the highest backup priority (see the BackupPriority property of availability replicas).
This command sets the FailureConditionLevel property on the availability group ‘MyAg’ to ‘OnServerDown’:
```
C:PS> Set-SqlAvailabilityGroup -Path SQLSERVER:SqlPrimaryServerInstanceNameAvailabilityGroupsMyAg -FailureConditionLevel OnServerDown
```
This means that when the server instance hosting the primary replica goes offline and automatic failover is enabled, an automatic failover will be initiated.

Additional Notes

Related Links