SQL Server AlwaysOn – Rolling Upgrade Migration from 2016 to 2019

migrationsql serversql-server-2016sql-server-2017sql-server-2019

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

Image for 2016

Image for 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.

Best Answer

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

Related Question