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.
An information technology professional with twenty one year's experience in systems administration, computer programming, requirements gathering, customer service, and technical support.