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.
AG.name AS [AvailabilityGroupName],
dbcs.database_name AS [DatabaseName],
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,
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
ORDER BY AG.name ASC, dbcs.database_name