availability-group

Is it possible to re-trigger database seeding to a distributed availability group without impacting the source availability group?

I have 3 on-prem SQL servers and my databases are replicated across the 3 using an AOAG. I am in the process of setting up a distributed availability group to seed these database to 2 azure nodes which can then be used for BI reporting purposes.

My first priority is the on-prem AOAG, ensuring these databases are unaffected by any seeding to Azure activity.

Here is an example of the set up:

When the DAGs were created, seeding from on-prem to Azure started and in the main it was successful. However, I have 2 databases that have failed to seed.

error

Both databases exist on the Azure nodes in a restoring state. I need to either continue seeding (doubtful) or retry for the 2 failed DB's. I don't want to have to do anything to the on-prem versions.

I haven't found any solution online, however I have found that if I drop the (restoring) DB's from the Azure nodes and then fail over the Azure AOAG over to its secondary node, it triggers the seeding of the now missing DB's. Albeit this is now seeding firstly to the node that I want to be secondary, but I can wait for it to complete and then fail back again.

Problem solved. However, is it possible to trigger this re-seeding without having to failover the Azure nodes?

I cannot find anything online regarding this so any input would be greatly appreciated.

Solution

  • You can modify the distributed AG on the primary and that triggers the retry for autoseed. I forgot to copy some TDE cert and 2 databases failed. Running a "Modify" on the distributed AG, keeping the current context on the primary AG forced the auto seed to retry. This was on a SQL 2019 environment with one primary AG and one secondary AG.

    ALTER AVAILABILITY GROUP [test-dag-01]   
MODIFY   
AVAILABILITY GROUP ON  

'pri-ag-01' WITH (
   LISTENER_URL = 'tcp://pripdbag01.contoso.com:5022',    
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC  
),
'sec-ag-01' WITH (
    LISTENER_URL = 'tcp://secpdbag01.contoso.com:5022',    
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
    FAILOVER_MODE = MANUAL,   
    SEEDING_MODE = AUTOMATIC  
 );    
 GO