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:
log_send_que_size=null,log_send-size=0,suspend_reason_description=suspend_from_revalidation and suspend_reason=7
Do 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.