This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED

Environment

-> Database server JBSAG2 and JBSAG1 are on primary datacentre located in East US.

-> Database server JBSAG3 is on secondary datacentre in Korea Central.

-> Database servers JBSAG1, JBSAG2 and JBSAG3 are part of failover cluster without shared storage.

-> Database in Asynchronous secondary replica JBSAG3 exhibits a status of “Not Synchronizing”. Below is the snapshot of Always on Dashboard,

-> Below are the messages that can be found,

This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.

The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.

-> Details from SQL Server Error log in Primary JBSAG2.

2021-02-09 15:46:54.270 spid34s Automatic seeding of availability database ‘JBDB’ in availability group ‘JBSwiki’ failed with a transient error. The operation will be retried.
2021-02-09 15:46:54.270 spid34s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [698E9144-D01D-4D34-B898-7FBC32873ABD]->[9B4A72A5-618B-4A9B-81D0-A54826AAEE36], database [JBDB], remote endpoint [TCP://11.0.2.41:5022], source operation [0EFB986D-7156-4BEE-82DC-162265BC3CD8]: Seeding task failed with result 0x80000000.’
2021-02-09 15:46:54.270 spid34s Always On: DebugTraceVarArgs AR ‘Seeding is canceled with cancelReason = 108
2021-02-09 15:46:54.270 spid34s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [698E9144-D01D-4D34-B898-7FBC32873ABD]->[9B4A72A5-618B-4A9B-81D0-A54826AAEE36], database [JBDB], remote endpoint [TCP://11.0.2.41:5022], source operation [0EFB986D-7156-4BEE-82DC-162265BC3CD8]: Transitioning from [CHECK_IF_SEEDING_NEEDED] to [FAILED].’
2021-02-09 15:46:54.280 spid34s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [698E9144-D01D-4D34-B898-7FBC32873ABD]->[9B4A72A5-618B-4A9B-81D0-A54826AAEE36], database [JBDB], remote endpoint [TCP://11.0.2.41:5022], source operation [0EFB986D-7156-4BEE-82DC-162265BC3CD8]: Seeding encountered a transient failure, state ‘108’, maximum retries exceeded’
2021-02-09 15:47:23.960 spid14s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [698E9144-D01D-4D34-B898-7FBC32873ABD]->[9B4A72A5-618B-4A9B-81D0-A54826AAEE36], database [JBDB], remote endpoint [TCP://11.0.2.41:5022], source operation [D9301C18-F2D7-4CBE-A2AD-D688D02EA614]: Transitioning from [PENDING] to [CHECK_IF_SEEDING_NEEDED].’

-> Executing below query and checking the status of all replicas,

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

-> Last_Connect_error_description is as below,

Connection attempt failed with error: ‘10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)’.

-> Last_Connect_error_number is 10060 which refers to “connection timed out”.

-> It seems like connection to secondary replica cannot be made by availability group.

-> Messages in SQL server error log shows that there is an issue with endpoint URL [TCP://11.0.2.41:5022] which is for secondary replica JBSAG3.

-> I checked the hosts file in location C:\Windows\System32\drivers\etc to make sure there are no invalid entries for JBSAG3 replica. As expected, there were no bad entries for JBSAG3 in hosts file.

-> I tried pinging JBSAG3 from command prompt to see if it is resolving to correctly.

-> The IP address for replica JBSAG3 is 11.0.2.4. But it seems like the endpoint URL is TCP://11.0.2.41:5022. It seems like the endpoint URL is wrong.

-> I changed it to reflect the correct IP using below query on Primary replica JBSAG2,

USE MASTER
GO
ALTER AVAILABILITY GROUP [JBSwiki] 
MODIFY REPLICA ON 'JBSAG3' WITH (ENDPOINT_URL = 'TCP://11.0.2.4:5022');

-> Once after the above changes. Always On availability group turned healthy.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Creating a Linked Server in Azure SQL Managed Instance to Azure SQL Database

Requirement

-> Create a linked server to Azure SQL Database jbsqldbserver in Azure SQL Managed Instance jbmi-sql01.

-> Azure SQL Managed instance and Azure SQL Database are on different Resource group.

-> Azure SQL Database will have “Deny public network access” set to Yes.


-> Before creating a linked server in Azure SQL Managed Instance. I tried connecting to Azure SQL database using SQL Server management studio and got below error,

===================================
Cannot connect to jbmi-sqldb.database.windows.net.
===================================

Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database). (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=47073&LinkId=20476

Server Name: jbmi-sqldb.database.windows.net
Error Number: 47073
Severity: 14
State: 1
Line Number: 65536


-> It seems like the private endpoint is not configured correctly. I followed below procedure to get it created properly,

-> Opened the required Azure SQL Database from Azure portal and clicked on “Firewalls and virtual networks”, Clicked on “Create Private Endpoint”.

-> Complete the “Basics” tab as below,

-> Complete the “Resource” tab,

-> Complete the “Configuration” tab. Select the Vnet related to Azure SQL Managed instance,

-> It is to be noted that you cannot select the subnet used by Azure SQL managed instance, you will receive below error when you do that.

The selected subnet ‘MI’ has a delegation and cannot be used with a private endpoint.

-> Select a different subnet for MI and proceed further,

-> Complete the “Tags” tab and create the private endpoint,

-> Now I am able to connect to the Azure SQL Database,

-> Try creating Linked server now,

-> Right click created linked server and click on “test connection” and it connects just fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.