- Databases
- SQL Server
- Check if all AOAG Members are in Synchronous mode, and have automatic failover set
Check if all AOAG Members are in Synchronous mode, and have automatic failover set
Article Entry Date: May 27,2022 @ 06:55:43 / Last Updated On: May 27,2022 @ 07:01:45
AOAG stands for [Always On Availability Group].
The primary use of [AOAG] is for syncing multiple SQL Server instances together into a Failover safe environment. All other members are updated when the primary server is updated, so each member is ready if set as primary. Only the primary instance is used at a given time and can stay as the primary for as long as needed until it fails over to one of the other members.
The following script will check if all members have the proper settings for a safe failover when required.
In [SSMS]
Right-click on the [Listener,2433] Node.
Choose [New Query]
Copy and paste the following SQL Script into the Query window.
[Script for checking AOAG Failover settings]
Click on [Execute].
The output will be similar to this example of ours.
Name replica_server_name availability_mode_desc failover_mode
SQLGroup SQL-01\SQL19 SYNCHRONOUS_COMMIT 0
SQLGroup SQL-02\SQL19 SYNCHRONOUS_COMMIT 0
SQLGroup SQL-03\SQL19 SYNCHRONOUS_COMMIT 0
SQLGroup SQL-04\SQL19 SYNCHRONOUS_COMMIT 0