I’m running various labs in the last few months, and often like to bring up SQL AlwaysON.For the last LAB, I had fresh clear VMs, new SQL installs, but after I configured AlwaysON on SQL – secondary node would not synchronize – I would get “Secondary database is not joined to the availability group error.”
Here is how I resolved it. I done it on SQL Server 2017.
Before we begin
These are two guides on my blog for reference, that I usually use when I have t bring up new SQL install.
“Server Basics 15: How to install database server – install SQL”
and for Always ON
“Create Highly Available SQL installation – Configure SQL AlwaysON”
I do have automated LAB samples that i usually just bring up when I want to test something, so I don’t waste too much time.
But, sometimes, I like to install and set things from scratch, just too keep my skills in shape.
This was the case for the last lab – I went on to install complete lab from the scratch – I installed domain, sql, and set Always ON on SQL (2017).
However, it didn’t work.
After I set Always ON, secondary node would not sync
I tried to right click on ImportantDB under Availability Databases which is sub-menu of Availability Groups and select to Join database – but that also produced errors.
Ok, I got two warnings
“Secondary database is not joined to the availability group error.”
“The data synchronization state of this availability database is unhealthy”
Strange.
I found following query on Microsoft site and run it on a secondary node (SQL2)
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=1I got following
DISCONNECTED “An error occured while receiving data> 24(The program issued a command but the command length is incorrect)”
Ok, so that will probably point to the firewall problems. As I specified in my “Create Highly Available SQL installation – Configure SQL AlwaysON” guide, you need to have two inbound rules opened in firewall TCP 5022 and TCP 1433
So this error hints that maybe I haven’t configured firewall. You need to check that out – I did configured mine.
But anyways – I got these errors.
There is only one difference I did while installing SQL from the usual cookbook I do. Since I was in a hurry, while installing SQL under Server Configuration I left default values for Service Accounts provided by system, while I usually use domain accounts like depicted below.
So, I quickly uninstalled SQL and reinstalled it (I didn’t want to just change accounts in services and sql – which is what I could do).
This time I ser SQL service accounts with domain accounts as depicted above (it is also part of my SQL installation guide I mentioned above) and after that everything worked – I did Always ON setup again, and this time without errors.
So, it looks like Always ON does not like local SQL Service accounts.
If you don’t wish to use domain accounts, I guess you can set machine accounts, but I don’t have details on that, you will have to look that up further.
Conclusion
To wrap up, if you get this error, look to you firewall settings and service account settings.