A distributed availability group (DAG) is a special type of availability group that spans two availability groups. This blog will clarify some issues regarding failover in a DAG.

A simple DAG ‘TIWENDAG’ is created for relevant tests that will be addressed later in this blog. The structure of ‘TIWENDAG’ is illustrated by the following diagram. ‘TIWENAG1’ is the primary AG of DAG ‘TIWENDAG’, with ‘TIWENVM1’ as the Primary Replica and ‘TIWENVM2’ as the secondary replica. ‘TIWENAG2’ is the secondary AG of the DAG, with ‘TIWENVM3’ as the primary replica and ‘TIWENVM4’ as the secondary replica.

In a DAG, both primary AG and secondary AG support automatic failover. Moreover, the availability groups in DAG are identical with regular AGs regarding the functions. The configuration of automatic failover is the same for an AG in a DAG and a regular AG. If “automatic” is specified for failover mode, the primary replica in the AG (no matter whether the AG is part of a DAG) will automatically failover to a synchronous secondary replica when the primary replica goes down.

If the primary replica on the secondary AG (also known as the “forwarder”) is lost and causes automatic failover to happen or manual failover is performed in the secondary AG in a DAG, there will be no data loss if the following conditions are met:

- The primary replica on the primary AG runs with no synchronization issue when the failover happens;
- The secondary AG on the DAG has a functioning secondary replica before the failover happens;
- The primary replica on the primary AG can communicate properly with the secondary replica on the secondary AG over their database mirroring endpoints.

The reason for this is that when the transaction logs are backed up, only the logs that have been applied to all the replicas in the DAG (including the replicas in both AGs) will be truncated. The following table provides an example of how the transaction logs will be truncated in a DAG, using the test environment described at the beginning of this post.

Assume that the hardened LSN on the primary replica of primary AG, “TIWENVM1”, is 10:15:1. The secondary replica of the primary AG, “TIWENVM2”, is synchronous, and its hardened LSN is also 10:15:1. For the secondary AG, between the primary AG and the forwarder, synchronization mode is asynchronous, and the hardened LSN of the primary replica of the secondary AG, “TIWENVM3”, is 10:12:1. The secondary replica of the secondary AG, “TIWENVM4”, is asynchronous and its hardened LSN is 10:10:1. Globally across the two AGs in the DAG, replica “TIWENVM4” has the smallest value of hardened LSN, truncation LSN on the primary replica of the primary AG won’t go beyond this smallest value. In the event that “TIWENVM3” is lost and “TIWENVM4” becomes the new forwarder, it will be able to synchronize with the primary AG.

This scenario can be easily demonstrated. The steps are as following:

1. Record the current truncation_lsn and last hardened LSN of each replica.
2. Modify the database. Here an INSERT query is executed 10 times on the “test” database.
3. Verify that the last hardened LSN of each replica grows by the same number.
4. Do a transaction logs back up on the “test” database on the primary replica on the primary AG.
5. Verify current truncation_lsn and last hardened LSN .
6. Suspend data movement on the secondary replica on the secondary AG.
7. Repeat 2-5.

The following screenshots record a complete repro of the scenario.

Step1 -

DMVs used in this step:

USE test

SELECT COUNT(*) AS '# of transaction logs' FROM fn_dblog(null, null)

SELECT database_id,
       group_id,
       replica_id,
       is_local,
       is_primary_replica,
       truncation_lsn,
       last_hardened_lsn
FROM sys.dm_hadr_database_replica_states
WHERE database_id=6 AND is_local=1

SELECT replica_id, replica_server_name
FROM sys.availability_replicas
WHERE replica_id=
      (SELECT replica_id FROM sys.dm_hadr_database_replica_states WHERE database_id=6 AND is_local=1)

SELECT COUNT(*) AS '# of entries in testt1 table' FROM testt1

Results from the four replicas :

Primary replica on primary AG:

Secondary replica on primary AG:

Primary replica on secondary AG:

Secondary replica on secondary AG:

If all the replicas are synchronized, all the replicas should have the same number of transaction logs as well as the same truncation_lsn as in the test environment shown in the screenshots above.

Step 2 -

Execute an INSERT clause 10 times.

INSERT INTO testt1 (NAME, ID) VALUES ('John Smith', 1)
GO

Step 3 -

Primary replica on primary AG:

Secondary replica on primary AG:

Primary replica on secondary AG:

Secondary replica on secondary AG:

The size of transaction logs grows due to the INSERT executions. The increase of entries in the ‘test’ database also reflects that 10 rows are inserted into the table.

Step 4 -

Perform a transaction log backup on the primary replica of the primary AG.

Step 5 -

Primary replica on primary AG:

Secondary replica on primary AG:

Primary replica on secondary AG:

Secondary replica on secondary AG:

Truncation_lsn of each replica should grow to the current lsn, and the number of transaction logs should shrink by the same size as the t-logs have been truncated.

Step 6 -

Suspend data movement of ‘test’ database on the secondary replica on the secondary AG.

Step 7 -

After repeating step 2 and 3, we can see that except for the secondary replica on the secondary AG (TIWENVM4), the ‘test’ database on all other three replicas are modified again and another 10 entries are inserted into the table. The database on secondary replica on the secondary AG is not updated because the data movement has been suspended.

Primary replica on primary AG:

Secondary replica on primary AG:

Primary replica on secondary AG:

Secondary replica on secondary AG:

The results from repeating step 4 and 5 are shown below.

Primary replica on primary AG:

Secondary replica on primary AG:

Primary replica on secondary AG:

Secondary replica on secondary AG:

Note that the newly generated transaction logs were truncated on the secondary replica on the primary AG, but the truncation_lsn of the primary replica on the primary didn’t change after 10 new rows were inserted into the database and executing transaction logs back up. As mentioned earlier, this is because the secondary replica on the secondary AG of the DAG (TIWENVM4) is not yet synchronized and thus has not successfully applied the new transaction logs generated by the INSERT queries.

The primary replica on the primary AG of a DAG only truncates logs to the least LSN among all the replicas in the DAG. This logic guarantees that there’ll be no data loss when the primary replica on the secondary AG got lost even when the secondary replica on the secondary AG has not fully synchronized with the primary replica on the secondary AG, as long as the primary replica on the primary AG is in a healthy state and can communicate with the secondary replica on the secondary AG properly.

To simulate the scenario mentioned above, we shut down the network of the primary replica on the secondary AG (TIWENVM3) so that it becomes unavailable before we resume the data movement on the secondary replica on the secondary AG (TIWENVM4).

After the data movement of ‘test’ database on TIWENVM4 is resumed, the secondary AG (TIWENAG2) is now in a resolving state because the current primary replica on TIWENAG2, TIWENVM3, fails to be connected. To fix this issue, a failover needs to be performed. Under the scenario when in the secondary AG of a DAG, the primary replica of the secondary AG is not available, the only failover option to the secondary replica of the secondary AG is FORCE_FAILOVER_ALLOW_DATA_LOSS.

ALTER AVAILABILITY GROUP TIWENAG2 FORCE_FAILOVER_ALLOW_DATA_LOSS

After performing failover with FORCE_FAILOVER_ALLOW_DATA_LOSS on the secondary replica on the secondary AG (TIWENVM4), the database is synced even though the previous primary replica on this AG is currently unavailable, as the primary replica of the primary AG keeps track of the unsynchronized part of transaction logs of the secondary replica on the secondary AG.

In this scenario, though the only failover option is FORCE_FAILOVER_ALLOW_DATA_LOSS, there’s no data loss during the failover.

One thing worth notice is that after FORCE_FAILOVER_ALLOW_DATA_LOSS and the previous primary replica (in this case, TIWENVM3) is brought back, data movement will not resume automatically and needs to be resumed manually. This behavior is by design and not limited to the distributed AG scenario but applies whenever failover with FORCE_FAILOVER_ALLOW_DATA_LOSS is performed. This is because there might be difference and even conflicts between the previous primary replica and the new primary replica, and user needs to manually choose which version to keep.

After resuming data movement on TIWENVM3, all replicas on the DAG are synchronized and the transaction logs get truncated to the same point.

Primary replica on primary AG:

Secondary replica on primary AG:

(New) Secondary replica on secondary AG:

(New) Primary replica on secondary AG:

It needs to be clarified that though under this scenario, FORCE_FAILOVER_ALLOW_DATA_LOSS in the secondary AG doesn’t cause data loss, it’s not guaranteed that there’ll be no data loss when using this option to perform failover in a distributed AG. For example, if the primary AG is lost, there might be data loss when a force failover is performed in the secondary AG. This behavior is expected, and the cases that might cause data loss are not limited to this example.