Resuming Data Movement in Always On

Keeping an Always On Availability Group healthy is crucial, and seeing a non-synchronizing database in an Always On High Availability Group can give you a sinking feeling (pardon the pun). Disregarding the reason for the syncing issue, there are a few ways to resume syncing and get your setup back in the green.

Let’s look at resuming using the SSMS GUI and running a SQL statement.

Resume Using the SSMS GUI

The point-and-click method using the SSMS GUI isn’t too tedious. From Object Explorer, expand Always On High Availability > Availability Groups > [your Availability Group name]. It’s worth noting that from here you can right-click and select Show Dashboard, which will give you a view to see the sync status of your databases. Once you know the database you want to resume, expand Availability Databases. Find the database that needs to be resumed, right-click it, and select Resume Data Movement.

Resume Using a SQL Statement

If you only have one database to worry about, resuming by clicking through in SSMS will get you up and running quickly. But what if you have a larger list of databases and want to narrow down what’s synchronized and what’s not synchronized? How can we run a query to see what databases are synchronizing and what databases we need to resume?

We can check the sys.dm_hadr_database_replica_states table, specifically the synchronization_state_desc column. Possible values for this field include NOT SYNCHRONIZING, SYNCHRONIZING, SYNCHRONIZED, REVERTING, and INITIALIZING. This table doesn’t include the database name but does include the database_id, which you will want to join up with sys.databases to pull in the database name.

Once you have your list of databases to resume, you can run the following statement for each:

ALTER DATABASE MyDatabase SET HADR RESUME;

You’ll then want to go back and confirm that all databases are synchronized.

Synchronicity

Whether you use the SSMS GUI or running SQL statements, these methods will help you get your databases back on track. Remember to keep tabs on the synchronization status or have alerting in place to ensure everything continues running smoothly.

Thanks for reading!