The Connection to the Primary Replica is not Active

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.

Read My Complete Article “Here





Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com

Linkedin 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads