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

Ganapathi's MSSQLLover

Ganapathi Varma is an MCP Lead Database Administrator and Blogger. He works as Lead SQL Server DBA for CtrlS Datacenters pvt ltd, Asia's only Tier 4 datacenter. He is responsible for managing thousands of SQL instances providing database managed services, High Availability and Disaster Recovery. Other than SQL Server, he is also expertised in different datacenter technologies. He is the owner of MSSQLLover blog and he is fully dedicated to the SQL Server community. You can also visit the mssqllover fb page for more SQL blogs and follow FB group: SQLHyderabad.

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 

Comments

Leave a comment on the original post [mssqllover.blogspot.com, opens in a new window]

Loading comments...