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
