Personal Notes of Edwin Eu

Availability databases in unhealthy data synchronization state

In Microsoft SQL Server on 09/05/2018 at 1:43 PM

Availability databases in unhealthy data synchronization state

The AlwaysOn user database showed not healthy data synchronization state

Database on AG group showed “Not Synchronizing”

And the warnings message “Data synchronization state of some availability database is not healthy”

 

 

 

 

 

 

 

 

In the SQL Server Management Studio for Secondary availability group replicas showed below:

 

 

 

 

 

 

 

I did some research and found MSDN article posted:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/data-synchronization-state-of-some-availability-database-is-not-healthy?view=sql-server-2017

  • The availability replica might be disconnected
  • The data movement might be suspended
  • The database might not be accessible
  • There might be a temporary delay issue due to network latency or the load on the primary or secondary replica

Solution

Execute the following  T-SQL statement on primary and all secondary replica for the databases that showed “Not Synchronizing” status.

ALTER DATABASE  [DB_Test] SET HADR RESUME

 

How to fix the suspended data movement in MS SQL Server 2016 AlwaysOn Availability Group (AG)

In Microsoft SQL Server on 08/15/2018 at 7:17 AM

How To fix the suspended data movement in MS SQL Server 2016 AlwaysOn Availability Group (AG)

  • Right click the user database
  • Select Resume Data Movement

How to simulate a suspended data movement in SQL Server 2016 AlwaysOn Availability Group (AG)

In Microsoft SQL Server on 08/15/2018 at 7:16 AM

How to simulate a suspended data movement in SQL Server 2016 AlwaysOn Availability Group (AG)

Select one of the user database.

  • First right clicked the user database –> under Available Database à Select Suspend Data Movement
  • Re-launch the Dashboard
  • The Dashboard should show a critical state and several warnings

 

 

 

 

 

 

Click on “Critical and Warning” hyperlink to drill down and see the detail