Distributed Availability Group with Manual Seeding

Question

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

Answer 1

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

Answer 2

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.

Answer 3

Scenario: Manually restore backups and add a database to both AGs and DistAG

Notes: DistAGs are designed to use Automatic seeding; however, I've had timeout issues using the feature. Therefore, restore manually and join to AGs.

Prerequisites

    1. Download backups to a folder on one of the replicas
    2. Set the folder as a share
            \\ShareMachine\ShareDirectory
    3. Disable tlog backups on the Global Primary
    4. Restore the database to all replicas across the DistAG and leave in restoring mode

To use GUI

    1.  Add database to local GlobalPrimary.
    
        Global Primary
        
            Bring the database on the GlobalPrimary online. 

                USE master
                RESTORE DATABASE DBName WITH RECOVERY
                GO

            Join Database to local AG.
        
                Object Explorer > Always On High Availability > Availability Databases > Add Database > Select Database > Authenticate > Join Only > Validate > Finish

    2.  Add database to forwarder AG.

        Forwarder Primary

            Add Database to the Dist AG 

                USE master 
                ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = DistAGName

        Forwarder Secondary

            Start syncing on the forwarder secondary. 
            Add database to the forwarder AG.

                USE master 
                ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = ForwarderAGName;

    3.  Enable tlog backups on the Global Primary


-- To join manually
    -- Run in SQLCMD Mode within SSMS

    -- 1.   Add Database to local AG

        -- GlobalPrimary
        
            -- Bring the database on the GlobalPrimary online.  

                :Connect GlobalPrimary_Machine\Instance -- GlobalPrimary
                USE master
                RESTORE DATABASE DBName WITH RECOVERY
                GO

            -- Add database to AG
                
                :Connect GlobalPrimary_Machine\Instance -- GlobalPrimary 

                USE [master]

                GO

                ALTER AVAILABILITY GROUP [GlobalPrimaryAGName]
                MODIFY REPLICA ON N'GlobalPrimarySecondary_Machine\Instance' WITH (SEEDING_MODE = MANUAL)

                GO

                USE [master]

                GO

                ALTER AVAILABILITY GROUP [GlobalPrimaryAGName]
                ADD DATABASE [DBName];

                GO

                :Connect GlobalPrimarySecondary_Machine\Instance -- GlobalPrimary Secondary


                -- Wait for the replica to start communicating
                BEGIN TRY
                DECLARE @conn BIT
                DECLARE @count INT
                DECLARE @replica_id UNIQUEIDENTIFIER 
                DECLARE @group_id UNIQUEIDENTIFIER
                SET @conn = 0
                SET @count = 30 -- wait for 5 minutes 

                IF (SERVERPROPERTY('IsHadrEnabled') = 1)
                    AND (ISNULL((SELECT member_state FROM master.sys.dm_hadr_cluster_members WHERE UPPER(member_name COLLATE Latin1_General_CI_AS) = UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
                    AND (ISNULL((SELECT state FROM master.sys.database_mirroring_endpoints), 1) = 0)
                BEGIN
                    SELECT @group_id = ags.group_id FROM master.sys.availability_groups AS ags WHERE name = N'GlobalPrimaryAGName'
                    SELECT @replica_id = replicas.replica_id FROM master.sys.availability_replicas AS replicas WHERE UPPER(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = UPPER(@@SERVERNAME COLLATE Latin1_General_CI_AS) AND group_id = @group_id
                    WHILE @conn <> 1 AND @count > 0
                    BEGIN
                        SET @conn = ISNULL((SELECT connected_state FROM master.sys.dm_hadr_availability_replica_states AS states WHERE states.replica_id = @replica_id), 1)
                        IF @conn = 1
                        BEGIN
                            -- exit loop when the replica is connected, or if the query cannot find the replica status
                            BREAK
                        END
                        WAITFOR DELAY '00:00:10'
                        SET @count = @count - 1
                    END
                END
                END TRY
                BEGIN CATCH
                    -- If the wait loop fails, do not stop the execution of the alter database statement
                END CATCH

                -- Start data sync
                ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = [GlobalPrimaryAGName];

                GO

-- ********* Run everything above in one batch and everything below in the second batch to prevent strange SQLCMD connection errors. *********

--  2.  Add database to forwarder AG

        -- Forwarder Primary    

            --  Add Database to the Dist AG 

                :Connect ForwarderPrimary_Machine\Instance -- Forwarder Primary
                USE master 
                ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = DistAGName

                GO

        -- Forwarder Secondary
            
            --  Start syncing

                :Connect ForwarderSecondary_Machine\Instance -- Forwarder Secondary
                USE master 
                ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = ForwarderAGName;

                GO


--  3.  Enable tlog backups on the Global Primary