Skip to: Site menu | Main content

Email LinkedIn


SQL AlwaysOn Availability Group Secondary Replica Disconnects Immediately After Setup

While setting up a SQL AlwaysOn Availability Group I ran into a very interesting problem.

I had created the AG while preparing for an upcoming speaking engagement so I'd setup two VMs (Node A and Node B) and checkpointed them immediately before creating the AG. This way I could just go back to the checkpoint and start the demo during my presentation.

This turned out to be very helpful because for the very first time I ran into an issue setting up a SQL AlwaysOn Availability Group. All previous configurations had gone flawlessly.

After a lot of head scratching and reverting the VMs to try again; the only error I could get from the AG Dashboard is: "This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED." Node B would simply NOT connect to the AG.

Not very helpful.

While searching around for possible solutions I stumbled upon this article discussing troubleshooting SQL AlwaysOn Availability Group joining issues. The author provided a very handy SQL statement to get some additional details about the Availability Group:

--Run below command to check endpoint state
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
Per the authors instructions I run on both nodes. When run against the problematic Node B I received this:
An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.
Further searching based on this error didn't turn up much related to SQL Availability Groups, but I did find this article discussing the same issue with Database Mirroring. The author suggests there are issues when using the default Local System SQL server service account.

I reverted to my checkpoint and changed the service account to a domain account PRIOR to configuring the AG. The AG configuration completed successfully and the AG shows no issues.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)