AG Secondary not in sync

SQL Server Administration
1

Hi,

We configured Allways on availability in 3 sql 2014 servers. A-Primary(read,write),B-Secondary(Read),C-DRnode(Readable secondary we set to No.) we set Properties- Manual failover for all.

And as per application we configured Transactional replication for db-XYZ from A to B .

Midnight our primary server A completely went down for 4 hours. Now the server is up. But we found that The Data is not sync and replication also failed. Ofcourse now server B is Primary. And the secondary is server A(db is not in sync). The DRnode db is also in Sync state.No complaints with DRnode and Primary server. (In AG properties - Now Primary is B the availability mode is Synchronous and the Secondary which is B server is Asynchronous and DRNode is Asynchronous).

When i checked the replication the error msg is db is not accessible for queries. and In ALLWayson Dashboard with warning beow:

"At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state."

except only one db-XYZ all other dbs are in sync. this db size is around 1 TB. Which the same db is in tran replication too.

How to bring back this db-XYZ to normal sync. and replication should work normally?

Please guide me. this is very critical production issue and need to bring to sync.

I tried " ALTER DATABASE [XYZ] SET HADR RESUME" -No result.

Thought of removing XYZ db from AG and add freshly but my Sr DBA tells not a feasible option.

I‌ tried "select * from sys.dm_hadr_database_replica_states" below are results:
l‌og_send_que_size=null,log_send-size=0,suspend_reason_description=suspend_from_revalidation and suspend_reason=7
D‌o i need to check anything?‌

We just started taking FULL Backup of the XYZ. It willl take 3 hrs to finish. By this time i need to get some solution to resolve this issue.

Please help.

Thanks.

2

  1. You said all where in manual. And you also set when Server A went down, Server B became the primary. That doesn't make sense.

  2. You say that Server B is the primary and Server C (the DR node are in sync). If those have a fast network between them, I would set those two to synchronous and automatic fail over. Then you can reset all the databases on Server A.

  3. When you configured replication, did you configure it for Availability Groups: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017. If not, you will need to remove it and reconfigure it when the AG is in good shape.

3

Thanks for your reply..