I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding. I can get the automatic seeding working but when I try to manually seed I cannot get the secondary database into the AG on the forwarder.
If I add the distributed AG to the secondary before I try to add the database to the regular AG I get the following message:
Msg 41190, Level 16, State 7, Line 22
Availability group 'MYDB' failed to process add-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.
If I try to add the DB first without joining the Distributed AG on the secondary, I get the following message because it thinks it should be a primary:
Msg 927, Level 14, State 2, Line 22
Database 'MYDB' cannot be opened. It is in the middle of a restore.
I have none of these problems with automatic seeding. Everything just magically works. All of the examples I find online use automatic seeding.
thanks in advance
Best Answer
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:
Long(er) form answer
Assuming lots of things like...
PRIMARY
and theFORWARDER
FORWARDER
AG is empty and ready to seed... you can... The following scripts are in
sqlcmd
format.Step 0. Disable log backups on
|OLD_AG|
(optional)You can ignore this step if:
Step 1. Create the DAG
CREATE
once on the current primary andALTER ... 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
...On the prospective
FORWARDER
...Step 2. Full Backups
Did you know you can append log backups to
copy_only
fulls? Neither did I until very recently! But by usingcopy_only
here you canexec as
... is not strictly necessary for this step.Step 3. Restore
Once again, executing as an appropriate service account.
Step 3(b). Did you leave the log backups on?
Nbd, but append them now ¯\_(ツ)_/¯
with norecovery
Step 4. On the Forwarder, join DBs to new AG