June 8, 2020 at 11:31 am
I've been trying to setup AlwaysON AG on two standalone default sql instances (SQL 2017 Enterprise Edition - 14.0.1000.169).
I've checked, port numbers (1433 and 5022) on both the servers are open and there's no issue with DNS. (Hostname is not more than 15 characters.)
Can anyone help me with this error please.
Msg 47106, Level 16, State 3, Server ''
Cannot join availability group ''. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation.
Msg 41158, Level 16, State 3, Server ''
Failed to join local availability replica to availability group ''. The operation encountered SQL Server error 47106 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
**I've created this post in incorrect section, please move this thread to SQL 2017 Administration**
June 8, 2020 at 3:56 pm
Are the two servers part of the same WSFC (Windows Server failover cluster)? I think that's a pre-requisite for availability groups. What exactly are you trying to do when you get the error?
On a slightly different subject, I recommend that you install the latest CU so that you're fully patched for the latest security vulnerabilities, features, bugs and so on.
John
June 9, 2020 at 4:28 am
Thanks John, I'll get the lastest CU installed.
The error is comingup when I try to create AG with AG wizard. It was trying to add secondary replica and is failing. Both the servers are standalone servers, SQL 2017 supports Clusterless Availability Groups.
June 9, 2020 at 10:29 am
Yes, SQL server instance starttup account is a domain service account. I've added that account to both servers as local admin and to the instances as Sysadmin. Hadr endpoint port (5022) is open, still getting same error.
June 10, 2020 at 10:54 am
Try running the below command in the master database on the instances involved.
Grant Alter Any Availability Group to [NT Authority\System];
after that, try to recreate the availability group again. I find it better to script the creation and run commands manually to see where it is failing.
June 10, 2020 at 1:02 pm
I tried with granting sysadmin role to Nt Authority\System, getting same error.
Also modified the owner of hadr_endpoint to sa, same error.
I see this error, when I checked eventlog on PrimaryReplica server:
(Eventid 41144) The local availability replica of availability group 'AG_TEST' is in a failed state. The replica failed to read or update the persisted configuration data (SQL Server error: 2905). To recover from this failure, either restart the local Windows Server Failover Clustering (WSFC) service or restart the local instance of SQL Server.
June 10, 2020 at 1:42 pm
What is the operating system in use here, is it Server 2016 or above?
You also aware that clusterless availability groups wont provide the automatic HA/DR, they are purely designed for read scale out setups. If you ever did need to manually fail over you would have to force failover with data loss.
The AlwaysOn option has been enabled at the service level also?
The endpoints for 5022 created? Can you telnet both ways on 1433 and 5022?
Verified you can login as the services account both ways to each machine from each machine and each account is a sysadmin and local admin?
Can you post the script from the Wizard to see what options its trying to set?
June 12, 2020 at 11:18 am
Update:
This has been fixed. I've installed latest Cumulative update on both SQL instances, rebooted the servers and it is working now.
Thankyou all for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply