Configuring Availability Modes
- Synchronous-commit mode involves higher transaction latency but allows manual and automatic failover.
- Asynchronous-commit mode minimises transaction latency, is suitable for geographically dispersed clusters, but only supports forced failover.
We can configure the availability mode on the Availability Group Properties page of SQL Server Management Studio, or T-SQL statement:
ALTER AVAILABILITY GROUP <group_name> MODIFY REPLICA ON '<server_name>\AlwaysOn' WITH ( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
Selecting Failover Modes
Each of these modes have the benefits but choosing what is right for us is a matter of weighing the benefit and loss.
- Automatic Failover - no data loss. Current primary and at least one secondary replica are configured with Automatic and synchronised, primary and replica are in synchronous-commit mode.
- Planned Manual Failover - no data loss. at least one of the secondary replicas is in a SYNCHRONISED state. primary and replica instances are in synchronous-commit mode.
- Forced Manual Failover - possible data loss. when no secondary replica is in the SYNCHRONISED state or when the primary replica is unavailable.
Forced Manual Failover is the only type supported if asynchronous-commit mode is used on the primary, or if the only available replica uses asynchronous-commit mode.
Manual failover can be performed using SQL Server Management studio, or T-SQL. Below is an example: on the replica instance that we will make the primary instance.
ALTER AVAILABILITY GROUP <group_name> FAILOVER;
Using PowerShell cmdlet to perform manual failover of availability group <group_mane> to the <server_name>\AlwaysOn instance it would look like this…
Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\myServer\AlwaysOn\AvailabilityGroups\myAvailabilityGroup
Mirroring endpoints must be created either before or during the availability group creation process.
Forced failover can be performed using SQL Server Management Studio, or T-SQL:
ALTER AVAILABILITY GROUP <group_name> FORCE_FAILOVER_ALLOW_DATA_LOSS;
Using PowerShell we would write:
Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\<server_name>\AlwaysOn\AvailabilityGroups\<group_name> -AllowDataLoss
Configuring Readable Secondary Replicas
To offload read-only workloads from the primary replica, readable secondary replicas can service read-only requests for database access.
ALTER AVAILABILITY GROUP Transact-SQL statement with the SECONDARY_ROLE option
An availability group replica can contain multiple databases.
There are three options when configuring a readable secondary:
- Yes (all connections are allowed to secondary databases on the secondary replica but only for read access)
- Read-intent only (Read-intent – only read-only connections are allowed to the secondary databases on the secondary replica)
- All secondary replicas can be configured to be available to read-only queries
- Failover occurs on a per-replica basis.