SQL Server Availability Group Databases Synchronization Status

 

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *