Database Administration

Distributed Availability Group Direct Seeding FAILED, failure_state SQL Error, failure_state 2


We just started setting up Distributed Availability groups to replicate our production databases into a new reporting cluster. The first availability group that we setup for replication worked great without any issues, however when we then moved on to the second availability group with much larger databases (over 3TB total) it took much longer and two of the 5 databases failed. We setup the distributed availability group to use direct seeding and when querying the sys.dm_hadr_automatic_seeding table it indicates the current_state as FAILED, with failure_state 2 (SQL Error) or 21 (Seeding Check Message Timeout):

dm_hadr_automatic_seeding

What can we do to troubleshoot this issue?




Solution

The AlwaysOn Professional blog has some general troubleshooting steps for direct seeding and also includes some details about trace flag 9567 to enable compression during seeding, but I didn't find any details about the SQL Error or Seeding Timeout.

We previously have had issues with large databases causing problems in availability groups, but this usually is resolved by applying the latest transaction logs from the primary against the replica.

In this case the databases were listed on the secondary availability group as recovering, so I tried applying the latest transaction log backups from the primary and then joining the database to the secondary availability group:

--Restore transaction logs from primary and stay in recovery mode. Multiple backup files may need to be restored from oldest to newest.
RESTORE LOG stackoverflow from disk = '\\Backups\SQL\_Trans\StackOverflow_AG\StackOverflow\StackOverflow_LOG_20170810_175400.trn' WITH NORECOVERY;
ALTER DATABASE stackoverflow SET HADR AVAILABILITY GROUP = [StackOverflow_RAG];
ALTER DATABASE stackoverflow SET HADR RESUME;

This worked for both of the failed databases and fixed the replication issues. Our reporting cluster now has all databases kept in sync from the primary availability group:

enter image description here





Comments (1)

  • +0 – I see you are using opserver in your screenshot. I am struggling to get it working from ground up. Do you mind writing a blog post on how external people can get this up and running + do you store historical data so that you can go back and see what happened last week ? Appreciate it and it will be huge help if you can blog a series :-) — Jun 28, 2018 at 19:57