February 8, 2019 at 8:11 pm
Hi,
We configured SQL 2014 Allways on availability in 3 sql 2014 servers. A-Primary(read,write),B-Secondary(Read),C-DRnode(Readable secondary we set to No.) 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.and takes very much time.'
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.
Please help.
Thanks.
February 9, 2019 at 4:32 am
Hi, this status mean: SUSPEND_FROM_REVALIDATION = Log change mismatch is detected on reconnection (see error log)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-2017
looks like replica B was set to asynchronous mode and failover was done with FORCE_FAILOVER_ALLOW_DATA_LOSS, there are some transactions on A that are missing on B.
This may also impact replication.
Check error log on A and B for AG errors and what is replication error?
February 11, 2019 at 6:21 am
In addition to the questions above, have you got trace flag 1448 enabled?
http://blogs.microsoft.co.il/yaniv_etrogi/2017/07/14/trace-flag-1448-replication-with-always-on-availability-groups/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply