6

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

  • Just trying to understand - is the AG group already there with dbs already part of it and you want to add a new db with manual seeding ? – Kin Jun 26 '18 at 17:57
  • @Kin it's all from scratch. New AG's and new distributed AG's. – Alf47 Jun 26 '18 at 19:41
3
+100

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
  • I'm fairly certain the condition the system was in when I received the first error posted above was when the DAG existed on both the primary and secondary and the local AG on the secondary was empty, but I'm going to go through the entire process again using your suggestions and let you know before I mark this as accepted. The one difference i can see is that you have the AGs set with SYNCHRONOUS commit and I was using ASYNC. – Alf47 Jun 27 '18 at 13:05
  • These scripts are copy-pasta from some run book templates I made for a recent migration. Sync was for a forward-only failover but I’d be surprised if that was the factor that made the difference. – Peter Vandivier Jun 27 '18 at 13:12
  • It depends (but probably yes- for me it was unreadable for a time). Is your forwarder a higher version than the current primary? To use a DAG to fail forward from 2016 to 2017 (for example) the forwarder will not be readable until failover (at which point the old primary (2016) will no longer be readable (and fail back is not possible)). – Peter Vandivier Jun 27 '18 at 17:16
  • There was one issue that occurred that I have not been able to explain. When I restored the database to the secondary AG, I was able to add the database to the AG but it stayed in restoring state. I noticed the log file was complaining saying it was restoring and not allowing recovery to be run. After another 2 attempts of altering the second AG to add the DB by restoring more logs and running the alter command, it finally worked and I saw a message in the log saying parallel redo is started. I don't know if this was a timing issue or if some other problem is happening. – Alf47 Jun 27 '18 at 17:18
  • No they're the same version on the same patch level – Alf47 Jun 27 '18 at 17:19
0

From Distributed availability groups

Distributed availability groups were designed with automatic seeding to be the main method used to initialize the primary replica on the second availability group.

A full database restore on the primary replica of the second availability group is possible if you do the following:

  • Restore the database backup WITH NORECOVERY.
  • If necessary, restore the proper transaction log backups WITH NORECOVERY.
  • Create the second availability group without specifying a database name and with SEEDING_MODE set to AUTOMATIC.
  • Create the distributed availability group by using automatic seeding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.