0

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.

  • When you failed over to AG2, was all BDs in the AG in sync with AG1 ? Can you provide the step you did ? – Dominique Boucher Jul 29 '20 at 12:39
  • Hello, well.. in AG1 all Databases are sync within servers in AG1. But the problem is the sync into AG2, only database names are present but no "database is imported". The steps are: (1 create AG1, and sync databases, there are 2 servers in automatic sync mode), 2: Create AG2 but without any databases. 3: Create Distributed AG with AG1 and AG2 but then ... databases aren't in AG2 imported .. – Arno Jul 30 '20 at 12:42
0

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

  • Hello thanks for documentation .. I will do a new try with every Microsoft steps and give you a feedback if the problem still exists but i'm pretty sure i was close this recommendation :) – Arno Jul 31 '20 at 6:35
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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