11

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

2
  • 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 Shah
    Jun 26, 2018 at 17:57
  • @Kin it's all from scratch. New AG's and new distributed AG's.
    – Alf47
    Jun 26, 2018 at 19:41

3 Answers 3

10
+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
5
  • 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, 2018 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. Jun 27, 2018 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)). Jun 27, 2018 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, 2018 at 17:18
  • No they're the same version on the same patch level
    – Alf47
    Jun 27, 2018 at 17:19
1

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.
0
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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