Distributed Availability Group between SQL Server 2016 and SQL Server 2019 doesn't seed

Question

Appologies for a long first post :-)

I'm planning on implementing a distributed availability group between an existing SQL2016 AG and a new SQL Server 2019 AG, and am validating the design in a LAB prior to building it. From what I've heard this Dist AG should be supported, but I can't get it working.

The lab consists of:

1. LAB1SQL01 - Windows Server Core 2016 (eval license), running SQL Server 2016 (13.0.5026.0)
2. LAB1SQL02 - Windows Server Core 2016 (eval license), running SQL Server 2016 (13.0.5026.0)
3. LAB1SQL03 - Windows Server 2019 (eval license), running SQL Server 2019 (15.0.4023.6)
4. LAB1SQL04 - Windows Server 2019 (eval license), running SQL Server 2019 (15.0.4023.6)

I am using the same group managed service account across all servers

I have deployed the following AlwaysOn Availability Groups:

1. LAB1SQLAG1 - Servers: LAB1SQL01 & LAB1SQL02, Listener: LAB1SQLAG1L1
2. LAB1SQLAG3 - Servers: LAB1SQL03 & LAB1SQL04, Listener: LAB1SQLAG3L1

-- Create endpoints
-- Run on LAB1SQL01
USE [master]
GO
CREATE ENDPOINT [Endpoint_AG]
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
       , ENCRYPTION = REQUIRED ALGORITHM AES)
GO 

-- Run onLAB1SQL02
USE [master]
GO
CREATE ENDPOINT [Endpoint_AG]
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
       , ENCRYPTION = REQUIRED ALGORITHM AES)
GO

-- Create login and grant the SQL Server service account CONNECT permissions to the endpoint
-- Run on LAB1SQL01
USE master
GO
CREATE LOGIN [LAB1\svc.sql01.lab1$] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::Endpoint_AG
TO [LAB1\svc.sql01.lab1$]; 
GO

-- Run on LAB1SQL02
USE master
GO
CREATE LOGIN [LAB1\svc.sql01.lab1$] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::Endpoint_AG
TO [LAB1\svc.sql01.lab1$]; 
GO


-- Create an Availability Group LAB1SQLAG1
-- Run on LAB1SQL01
CREATE AVAILABILITY GROUP [LAB1SQLAG1]
FOR 
REPLICA ON
N'LAB1SQL01' WITH
(
   ENDPOINT_URL = N'TCP://LAB1SQL01.lab1.local:5022', 
   FAILOVER_MODE = MANUAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC
), 
N'LAB1SQL02' WITH
(  ENDPOINT_URL = N'TCP://LAB1SQL02.lab1.local:5022', 
   FAILOVER_MODE = MANUAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC
)
LISTENER 'LAB1SQLAG1L1'
(
   WITH IP ( ('10.16.16.34','255.255.255.0') ) ,
   PORT = 1433
); 
GO

-- Run on LAB1SQL02
ALTER AVAILABILITY GROUP [LAB1SQLAG1] JOIN
--Allow the Availability Group to create databases on behalf of the primary replica
ALTER AVAILABILITY GROUP [LAB1SQLAG1] GRANT CREATE ANY DATABASE
GO

---------------------------------------------------------------------------
-- Create endpoints
-- Run on LAB1SQL03
USE [master]
GO
CREATE ENDPOINT [Endpoint_AG]
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
       , ENCRYPTION = REQUIRED ALGORITHM AES)
GO 

-- Run on LAB1SQL04
USE [master]
GO
CREATE ENDPOINT [Endpoint_AG]
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
       , ENCRYPTION = REQUIRED ALGORITHM AES)
GO

-- Create login and grant the SQL Server service account CONNECT permissions to the endpoint
-- Run on LAB1SQL03
USE master
GO
CREATE LOGIN [LAB1\svc.sql01.lab1$] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::Endpoint_AG
TO [LAB1\svc.sql01.lab1$]; 
GO

-- Run on LAB1SQL04
USE master
GO
CREATE LOGIN [LAB1\svc.sql01.lab1$] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::Endpoint_AG
TO [LAB1\svc.sql01.lab1$]; 
GO


-- Create an Availability Group LAB1SQLAG3
-- Run on LAB1SQL03
CREATE AVAILABILITY GROUP [LAB1SQLAG3]
FOR 
REPLICA ON
N'LAB1SQL03' WITH
(
   ENDPOINT_URL = N'TCP://LAB1SQL03.lab1.local:5022', 
   FAILOVER_MODE = MANUAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC
), 
N'LAB1SQL04' WITH
(  ENDPOINT_URL = N'TCP://LAB1SQL04.lab1.local:5022', 
   FAILOVER_MODE = MANUAL, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SEEDING_MODE = AUTOMATIC
)
LISTENER 'LAB1SQLAG3L1'
(
   WITH IP ( ('10.16.16.54','255.255.255.0') ) ,
   PORT = 1433
); 
GO

-- Run on LAB1SQL04
ALTER AVAILABILITY GROUP [LAB1SQLAG3] JOIN
--Allow the Availability Group to create databases on behalf of the primary replica
ALTER AVAILABILITY GROUP [LAB1SQLAG3] GRANT CREATE ANY DATABASE
GO

I then tested the 2 x AGs by creating test databases on LAB1SQL01 (for AG LAB1SQLAG1) and LAB1SQL03 (for LAB1SQLAG3) and confirmed that both databases synchronised to their respective replicas. I then removed the databases.

And then the following Distributed Availability Group was created:

1. DistAG_AG1_AG3 - Dist AG from LAB1SQLAG1 to LAB1SQLAG3

--Run on LAB1SQL01
CREATE AVAILABILITY GROUP [DistAG_AG1_AG3] 
WITH (DISTRIBUTED)  
AVAILABILITY GROUP ON 
'LAB1SQLAG1' WITH   
(  
   LISTENER_URL = 'TCP://LAB1SQLAG1L1.LAB1.LOCAL:5022',   
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
   FAILOVER_MODE = MANUAL,  
   SEEDING_MODE = AUTOMATIC  
),  
'LAB1SQLAG3' WITH   
(  
   LISTENER_URL = 'TCP://LAB1SQLAG3L1.LAB1.LOCAL:5022',  
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
   FAILOVER_MODE = MANUAL,  
   SEEDING_MODE = AUTOMATIC  
);   
GO 

--Run on LAB1SQL03
ALTER AVAILABILITY GROUP [DistAG_AG1_AG3]  
JOIN  
AVAILABILITY GROUP ON 
'LAB1SQLAG1' WITH   
(  
   LISTENER_URL = 'TCP://LAB1SQLAG1L1.LAB1.LOCAL:5022',   
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
   FAILOVER_MODE = MANUAL,  
   SEEDING_MODE = AUTOMATIC  
),  
'LAB1SQLAG3' WITH   
(  
   LISTENER_URL = 'TCP://LAB1SQLAG3L1.LAB1.LOCAL:5022',  
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
   FAILOVER_MODE = MANUAL,  
   SEEDING_MODE = AUTOMATIC  
);   
GO    

However this time when I create a test database on LAB1SQL01, then add to the local AG LAB1SQLAG1, strange things happen with the secondary AG.

The database on appears LAB1SQL03 but stays in the "Synchronized / In Recovery" state, and it doesn't appear as a database on LAB1SQL04 (but does appear under the availability databases with an asterix beside it.

When I look through the logs on LAB1SQL04, there's absolutely nothing. When I look through the logs on LAB1SQL03 I can see the database restoring, followed by these errors:

Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [9DAB7230-0E72-450E-ADFC-ECCDAE1349E7]->[0B7ABD01-D534-41AB-AFEB-91F6A3D89D46], database [SQL01TestDB1], remote endpoint [TCP://LAB1SQL04.lab1.local:5022], source operation [497E843D-F993-49CD-9816-C3800DCF18B4]: Seeding encountered a transient failure, state '20', retrying...'
Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [9DAB7230-0E72-450E-ADFC-ECCDAE1349E7]->[0B7ABD01-D534-41AB-AFEB-91F6A3D89D46], database [SQL01TestDB1], remote endpoint [TCP://LAB1SQL04.lab1.local:5022], source operation [7781AE86-A91F-4523-9F3C-32D5A5201AE8]: Transitioning from [PENDING] to [CHECK_IF_SEEDING_NEEDED].'
Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [9DAB7230-0E72-450E-ADFC-ECCDAE1349E7]->[0B7ABD01-D534-41AB-AFEB-91F6A3D89D46], database [SQL01TestDB1], remote endpoint [TCP://LAB1SQL04.lab1.local:5022], source operation [7781AE86-A91F-4523-9F3C-32D5A5201AE8]: Seeding task failed with result 0x80040e19.'
Always On: DebugTraceVarArgs AR 'Seeding is canceled with cancelReason = 20'
Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [9DAB7230-0E72-450E-ADFC-ECCDAE1349E7]->[0B7ABD01-D534-41AB-AFEB-91F6A3D89D46], database [SQL01TestDB1], remote endpoint [TCP://LAB1SQL04.lab1.local:5022], source operation [7781AE86-A91F-4523-9F3C-32D5A5201AE8]: Transitioning from [CHECK_IF_SEEDING_NEEDED] to [FAILED].'
Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [9DAB7230-0E72-450E-ADFC-ECCDAE1349E7]->[0B7ABD01-D534-41AB-AFEB-91F6A3D89D46], database [SQL01TestDB1], remote endpoint [TCP://LAB1SQL04.lab1.local:5022], source operation [7781AE86-A91F-4523-9F3C-32D5A5201AE8]: Seeding encountered a transient failure, state '20', maximum retries exceeded'

I've tested the above configuration SQL 2016 AG to SQL 2016 AG and it works perfectly. So I don't think that it's my configuration. All servers are built with the same configfile.ini and have the same disk layout. I've also tried with firewall disabled on all servers.

The OSs are all eval licenses. I started with eval SQL licenses, thought that may be causing the problem so grabbed the Enterprise copies from work for testing, but it didn't help.

Any suggestions on what I need to do to get this to work? I haven't found anyone else with the error codes that I've encountered.

Thanks in advance to all those who read to the bottom :-)

Answer 1

The database on appears LAB1SQL03 but stays in the "Synchronized / In Recovery" state, and it doesn't appear as a database on LAB1SQL04 (but does appear under the availability databases with an asterix beside it.

This is basically by design, even though it doesn't feel that way. Let me explain. The database on the 2016 is 2016 format, it is online and available, it can seed to all secondary replicas in the normal AG, it can also seed to the forwarder as the base database is coming from the primary replica. When it comes to the forwarder side of the Distributed AG, the database is not available, it's awaiting a database upgrade to 2019, it's not readable or otherwise accessible and the secondary replicas on the forward side get their database copies from the forwarder, not the global primary, which isn't available as it needs to be upgraded first.

Your option is to manually seed the secondary replicas on the forwarder side for now.

Answer 2

I did the same distributed AG configuration from SQL2016 AG to a SQL2019 AG. The first time I used the Active directory connection, and I am not lucky, the connection to secondary is not established although there is no error from sql statement. Then I used the certificate method to connect the primary and forwarder, it is good this time. I can see the connection is established. I also noticed that from the secondary distributed replica, the database state is Synchronized/In Recovery. And the database is not accessible. I think this is the normal behavior when you implement a DAG from sql2016 to Sql2019. Then I did fail over test, the DAG is successfully failover to forwarder, the secondary from sql2019 is automatically synchronized after failover. So I see it is possible to configure a DAG from SQL2016 to SQL 2019.