SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Connection to the Primary Replica is not Active

By Ganapathi varma Chekuri,

In this post, I’m going to talk an issue that I found when joining replica or database on secondary replica to availability group.  This error mostly appears when we try to join the adding replica to availability group or database to availability group using GUI and T-SQL.

Error 1

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
  Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
  ------------------------------

  ADDITIONAL INFORMATION:
  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'SQL2'. (Microsoft.SqlServer.Smo)
  ------------------------------
  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ------------------------------
  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 

In below screenshot, joining the database abc to SQL2 failed with this error when creating a new Availability Group.

Error 2

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'TREDSHOST08'. (Microsoft.SqlServer.Smo)
  ------------------------------
  ADDITIONAL INFORMATION:
  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ------------------------------
  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

In the below screenshot, joining database abc to SQL2 failed with this error.

Resolution

To solve the issues, follow these steps.

Step 1

Query the sys.dm_hadr_availability_replica_states for the last_connect_error_number which may help you diagnose the join issue. Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica

--connect secondary replica
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
 where rs.is_local=1

In the below screenshot, the secondary instance SQL2 was not able to communicate with SQL1

Step 2

The endpoints were created with the two dedicated IP's but the CREATE AVAILABILITY GROUP statement used the FQDN in the ENDPOINT_URL section. Once I changed the ENDPOINT_URL to use the IP's of the dedicated NIC (TCP://172.16.35.11:5022), the AG started working properly with the dedicated network.

--connect  primary replica
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST07' WITH (ENDPOINT_URL = 'TCP://172.16.35.10:5022')
GO
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST08' WITH (ENDPOINT_URL = 'TCP://172.16.35.11:5022')
GO

In the below screenshot, I have modified the endpoint URL on both replicas.

In below screenshot, I have modified the endpoint URL with the IP's of the dedicated NIC.

Step 3

Join the database to Always on availabiltiy group using GUI or T-SQL command as shown below.

--connect secondary replica
ALTER DATABASE AGTEST SET HADR AVAILABILITY GROUP = [AG01];  

In the below screenshot, I have successfully joined the database, AGTEST, to the Always On Availabiltiy Group.

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

 
Total article views: 218 | Views in the last 30 days: 16
 
Related Articles
BLOG

Monitor availability groups and availability replicas status information using T-SQL

Just a quick blog post to share a query, which I wrote to monitor availability groups and replicas a...

BLOG

Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.)

After power surge last night, I realized few availability databases (also known as a “database repli...

BLOG

Log File Full–Availability Group Database

I run into an interesting issue with an Availability Group database recently. I was running a data l...

SCRIPT

Backup databases in an Availability Group

Use this script to backup databases based on their role in an availability group

BLOG

Where Is My Primary Replica Again?

We have many clients with multi-node Availability Groups - that is, AGs with more than two replicas....

Tags
 
Contribute