Blog

SQL AlwaysOn Availability Group Secondary Replica Disconnects Immediately After Setup

May 6, 2018

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.