How to Fix Inactive Connections to Your Primary Replica in SQL Server

You might have added databases to your always-on group. When you use multi-subnet cluster and set up SQL Server Always On, you might receive an error message stating that the connection to your primary is inactive. In this article, we will learn how to fix that and synchronize databases participating in Always On.

Using Wizard or script

Inactive Connections To Your Primary ReplicaSQL Server always-on availability group can be created using the new availability group wizard or a TSQL script. One common reason that the action might fail is due to the issues in the connection between the primary replica and secondary replica. Service accounts on replicas

Review permission of SQL Service accounts on each replica. If the service account of the secondary replica is not added at the primary replica, then an attempt to contact the primary replica through the availability group wizard would fail.

-- Step 1 Identify the SQL service account
Select Top 1 service_account from sys.dm_server_services where servicename like '%SQL Server%'
-- Step 2 Check if this service account was added on the other replica
If not Exists (select loginname from master.dbo.syslogins 
   where name = 'SERVICE ACCOUNT FROM STEP 1')
Begin
PRINT 'This account does not exist in this replica. Please add it'
End

Endpoints

For always-on availability groups to functions properly, endpoints must have been created and their status should be “Started” on all secondary replicas and on the primary replica.

-- To View all end points, run this query
select * from sys.endpoints

-- To View HADR endpoint, please run this query
select * from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'

SQL Server Always On availability GroupIn some cases, though the endpoints were not started, their state_description might appear as ‘Started’. So it is recommended to execute the command ‘ALTER ENDPOINT’ for each HADR endpoint at all available replicas.
Many times, endpoints of SQL server are configured not to use SQL Authentication but use Windows Authentication. In such cases, verify and confirm that domain accounts are used as startup accounts by SQL Services. If you hace missed to verify and fix this, do not worry, this would be handled by the Availability Group Wizard. When the SQL Server Always-ON Availability Group Wizard identifies that the startup account is a local account, it would report this to you as a pop-up message. If you use TSQL script instead of the Wizard, commands like ‘SET HADR AVAILABILITY GROUP’ would fail.

SQL Server Always On availability group help you to quickly recover from corrupt SQL Server database pages by recieciving the stable version from other replicas.

Issue with ports

If a replica is not configured to listen on port 5022 your attempt to create an availability group would fail. To identify the port of your SQL server, you can use the query below:

SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO

If all of above listed methods did not help you, try this query and it will help you to diagnose further.

select tbl2.replica_server_name as 'Server Name', tbl2.endpoint_url as 'ENDPOINT',
tbl1.connected_state_desc as 'Endpoint Status', tbl1.last_connect_error_description as 'Error description', 
tbl1.last_connect_error_number as 'Error number', tbl1.last_connect_error_timestamp as 'Time Stamp' 
from sys.dm_hadr_availability_replica_states tbl1 join sys.availability_replicas tbl2
on tbl1.replica_id=tbl2.replica_id
where tbl1.is_local=1

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com