SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

Question

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks.

So with internet searching (MSDN, mssqltips...) I oriented my config toward "Distributed Groups".

My configuration is: AG1 (2 SQL Server with 2016) and AG2 (2 SQL Server with 2019).

I do the whole tutorial from mssqltips, Failover and then I have every Databases names with Warning (Image for 2019) but nothing imported.. why? I didn't find answer.

By the way ... The workaround is to install same SQL Server 2016 on the new windows 2019.. Do the all process with Failover throught the Distributed Group and then ... Install manually SQL Server 2019 on the windows 2019. I find it weird that we have these steps and try to understand if I missed something or whatever?

Both editions I used are Developer.

Answer 1

This is from Microsoft books online :

Initialize secondary availability groups in a distributed availability group 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. When you add the second availability group's primary replica to the distributed availability group, the replica is checked against the first availability group's primary databases, and seeding catches the database up to the source. There are a few caveats:

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of the second availability group will display a current_state of FAILED with the reason "Seeding Check Message Timeout."

The current SQL Server log on the primary replica of the second availability group will show that seeding worked and that the LSNs were synchronized.

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of the first availability group will show a current_state of COMPLETED.

Seeding also has different behavior with distributed availability groups. For seeding to begin on the second replica, you must issue the command ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE command on the replica. Although this condition is still true of any secondary replica that participates in the underlying availability group, the primary replica of the second availability group already has the right permissions to allow seeding to begin after it is added to the distributed availability group.

Source : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15

Answer 2

well yet it works! :) i did one more time the whole process and in the second group AG2 on the Primary Global i did (if that can help someone oneday too..):

CREATE AVAILABILITY GROUP [DistributedGroupName] WITH (DISTRIBUTED)
AVAILABILITY GROUP ON 'AG1' WITH
(
LISTENER_URL = 'TCP://ListenerAG1:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'AG2' WITH
(
LISTENER_URL = 'TCP://ListenerAG2:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

and into the forwarder :

ALTER AVAILABILITY GROUP [DistributedGroupName]
JOIN
AVAILABILITY GROUP ON 'AG1' WITH
(
LISTENER_URL = 'TCP://ListenerAG1:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'AG2' WITH
(
LISTENER_URL = 'TCP://ListenerAG2:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

Only one thing was different! -> I executed the first script from the Global Primary Server and not the AG1 Listener, and the second script was executed from the forwarder Server and not the AG2 Listener connection.. Well thank you very much for tips and help nowit works even there still have points i haven't fully understood with the Failover Behavior into Distributed Groups :)