Distributed availability groups was introduced in SQL Server 2016.
“DAG” is not the official abbreviation for distributed availability group, because the abbreviation is already used for the Exchange Database Availability Group feature. This Exchange feature has no relation to SQL Server availability groups or distributed availability groups.
The DAG came to allow us to spans two separates AG (configured on two different Windows Failover Clustering). Also, the Availability group that participates at DAG do not need to be hosted at the same location, they can be a physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability-group deployment. As long as two availability groups can communicate, you can configure a distributed availability group with them.
Opposite of traditional AG that has resources configured at Windows Server Failover Clustering (WSFC), the DAG do not store resources at WSFC, all information about DAG it is stored at SQL Server
We can use the DAG for this three main usage scenarios
- Disaster recovery and easier multi-site configurations
- Migration to new hardware or configurations, which might include using new hardware or changing the underlying operating systems
- Increasing the number of readable replicas beyond eight in a single availability group by spanning multiple availability groups
How it works?
DAG will provide to the application the possibility to connect to a read-only replica with different listener.
As mentioned before, each server will have their own WSFC (also can be at different domains)
As we can see at above image, we can create a DAG between 2 AG (or more), the the AG will consider the listener as nodes, so it is not possible to create DAG between the server name, we must use the virtual name (as CAP on FCI)
In this scenario, the Global Primary will send the logs to Primary node at second AG (called as forwarder) and this forwarder will send the logs to
You can configure the data movement in distributed availability groups as synchronous or asynchronous. However, data movement is slightly different within distributed availability groups compared to a traditional availability group. Although each availability group has a primary replica, there is only one copy of the databases participating in a distributed availability group that can accept inserts, updates, and deletions. As shown in the above image, AG1 is the primary availability group. Its primary replica sends transactions to both the secondary replicas of AG1 and the primary replica of AG2. The primary replica of AG2 is also known as a forwarder. A forwarder is a primary replica in a secondary availability group in a distributed availability group. The forwarder receives transactions from the primary replica in the primary availability group and forwards them to the secondary replicas in its own availability group.
It is important to know that DAG will keep just one replica writable, the remains replica it is Read-only!
We also we can create DAG from DAG, this off course will create some complexity for DBA’s administrations.
- We can only create DAG between AG and should use the listener as Nodes.
- Distributed availability groups can not be configured with Standard edition or mix of Standard and Enterprise edition.
To create a DAG you can follow this example:
OBS: Note that the code are using automatic seeding
At secondary AG, now let's join it to DAG
- Only manual failover is supported for a distributed availability group. In a disaster recovery situation where you are switching data centers, you should not configure automatic failover (with rare exceptions).
- You need monitor network latency at a different layer for the data transport. The difference is that each WSFC cluster maintains its own availability.
- We recommend asynchronous data movement, because this approach would be for disaster-recovery purposes.
- If you configure synchronous data movement between the primary replica and at least one secondary replica of the second availability group, and you configure synchronous movement on the distributed availability group, a distributed availability group will wait until all synchronous copies acknowledge that they have the data.
We can use the Dashboard at SSMS to monitor DAG as we do for traditional AG, and also we can query the DMV's
Fonte: Marcelo Fernandes