Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Sometimes you may need to restart one of the servers you use Always ON.
Suppose you have 2 servers and that you have 10 availability groups on one of your servers. You need to failover your AGs to the other server before restarting.
In my article “SQL Server Availability Group Failover Process” you can find how to perform failover. But you should not perform failover without checking the synchronization status of the databases.
You can see the synchronization status of any Availability Group on SQL Server as follows.
Therefore, under normal conditions, you should look at the health status of all Availability Groups on the server as above. This process may become tedious after a while. And a script to control this will make your work a lot easier.
With the help of the following script, you can find the databases in the AGs on SQL Server and the synchronization status of these databases.
However, this script only looks at the primary and secondary availability groups on the current server. You must run this script on both the primary node and the secondary node to get a final result.
If there is no problem in both, you can perform failover.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SELECT AG.name AS [AvailabilityGroupName], dbcs.database_name AS [DatabaseName], CASE WHEN dbrs.synchronization_state =0 THEN 'Not synchronizing' WHEN dbrs.synchronization_state =1 THEN 'Synchronizing' WHEN dbrs.synchronization_state =2 THEN 'Synchronized' WHEN dbrs.synchronization_state =3 THEN 'Reverting' WHEN dbrs.synchronization_state =4 THEN 'Initializing' END AS AGState, ar.failover_mode_desc, ar.availability_mode_desc, ISNULL(dbrs.is_suspended, 0) AS [IsSuspended] FROM master.sys.availability_groups AS AG LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id where dbcs.is_database_joined=1 ORDER BY AG.name ASC, dbcs.database_name |
