Distributed Availability Group with Manual Seeding

TL;DR:

It sounds like from the current description and comments that you may have added databases to the AG on the forward before joining the DAG. Instead, join the DAG first and then add the databases in the following order:

1. Create AG1
   • Add Databases to AG1
2. Create AG2 (no databases)
3. Create the DAG
   • Join AGs 1 & 2 to the DAG from AG1
   • Join AGs 1 & 2 to the DAG from AG2
4. Add databases to AG2
5. Profit?

---

Long(er) form answer

Assuming lots of things like...

1. Listeners/Cluster/Instances are already configured
2. There is already an AG...
   • on both the PRIMARY and the FORWARDER
   • that is not the same AG
   • and neither are already a member of another DAG
   • and the FORWARDER AG is empty and ready to seed
3. You have shared storage that is accessible from both replicas

... you can... ^{The following scripts are in sqlcmd format.}

Step 0. Disable log backups on |OLD_AG| (optional)

You can ignore this step if:

1. If you don't mind a potentially "moving target" LSN at the very end of the process or
2. You can complete the entire process inside the log backup window of all databases in the AG

Step 1. Create the DAG

CREATE once on the current primary and ALTER ... JOIN on the prospective forwarder. Execute as an appropriate service account so you don't end up with parts of the architecture owned by your user account.

On the current PRIMARY...

:connect |OLD_AG|.|DOMAIN|
execute as login = 'sa'

-- double check local replica is manual seeding first
alter availability group [|OLD_AG|]
    modify replica on '|THIS_REPLICA|'
    with (seeding_mode = manual);

create availability group [|DAG_X|]
    with (distributed)
    availability group on 
        '|OLD_AG|' with ( 
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual 
        ),
        '|NEW_AG|' with ( 
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual 
        );
go

On the prospective FORWARDER...

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter availability group [|DAG_X|]
    join availability group on 
        '|OLD_AG|' with ( 
            listener_url      = 'TCP://|OLD_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual 
        ),
        '|NEW_AG|' with ( 
            listener_url      = 'TCP://|NEW_AG|.|DOMAIN|:|PORT|',
            availability_mode = synchronous_commit,
            failover_mode     = manual,
            seeding_mode      = manual 
        );
go

Step 2. Full Backups

Did you know you can append log backups to copy_only fulls? Neither did I until very recently! But by using copy_only here you can

1. protect your backup chain from any migration tomfoolery and
2. reduce the length of the restore chain at the end of the process.

exec as... is not strictly necessary for this step.

:connect |OLD_AG|.|DOMAIN|

backup database DB1 to disk = N'\\my.shared.storage\backups\DB1.bak' 
    with copy_only, compression;
backup database DB2 to disk = N'\\my.shared.storage\backups\DB2.bak' 
    with copy_only, compression;
go

Step 3. Restore

Once again, executing as an appropriate service account.

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

restore database DB1 from disk = N'\\my.shared.storage\backups\DB1.bak' 
    with norecovery;
restore database DB2 from disk = N'\\my.shared.storage\backups\DB2.bak' 
    with norecovery;
go

Step 3(b). Did you leave the log backups on?

Nbd, but append them now ¯\_(ツ)_/¯

1. Executing as an appropriate service account
2. with norecovery

Step 4. On the Forwarder, join DBs to new AG

:connect |NEW_AG|.|DOMAIN|
execute as login = 'sa'

alter database DB1 set hadr availability group = [|NEW_AG|];
alter database DB2 set hadr availability group = [|NEW_AG|];
go