I have 2 servers in AWS Central joined with AWSCentralAG and I have 2 servers in AWS West joined with AWSWestAG. Obviously, I have the listener name and listener IP address and I able to connect to each other both ways without any problem. Now, I have already created DAG and currently stuck on the part where I cannot join the database to local AG. It returns the following error message.
Msg 35250, Level 16, State 7, Line 4
The connection to the primary replica is not active. The command cannot be processed
Overview of steps followed:
- Created Local AG in AWS Central. This is successful.
- Created Local AG in AWS West. This is successful
- Created Distributed AG connecting AWS Central and AWS West. This is successful.
- Now, the last piece, When I join the database on local AG on AWS West. The above error message about connection to replica is not active comes up.
Troubleshoot steps:
I am able to connect to AWS West Listener from AWS Central and Vice-versa.
I am able to ping all the nodes using both short name and FQDN.
I am able to telnet on Listener port to AWS West from AWS Central and Vice-versa.
I have verified the endpoint state is started. As per the KB, I have retried to start them manually and it was successful. As per the research, this was the common cause of the issue that I am facing.
I am able to failover back and forth on both AWS Central and AWS West.
The domain account for AWS Central(svcsqlawsc) and AWS West(svcsqlawsw) that SQL Services run on have appropriate permissions. They both have been created in all the nodes and verified they had connect endpoint permissions.
I queried
sys.availability_replicasandsys.dm_hadr_availability_replica_states. All of them are returning as CONNECTED.I ran a query impersonating as svc account and they all came back successfully from AWS Central node1.
:CONNECT AWSCENTRAL1NODE1 execute as login = 'AWSWest\svcsqlawswest' select name, state_desc, port from sys.tcp_endpoints where name = 'hadr_endpoint' -- started :CONNECT AWSCENTRAL1NODE2 execute as login = 'AWSWest\svcsqlawswest' select name, state_desc, port from sys.tcp_endpoints where name = 'hadr_endpoint' -- started :CONNECT AWSWEST1NODE1 execute as login = 'AWSCentral\svcsqlawscentral' select name, state_desc, port from sys.tcp_endpoints where name = 'hadr_endpoint' -- started :CONNECT AWSWEST1NODE2 execute as login = 'AWSCentral\svcsqlawscentral' select name, state_desc, port from sys.tcp_endpoints where name = 'hadr_endpoint' -- startedI have tried using the GUI method and I have tried with TSQL. Both of them are indicating the same problem.
I am not sure what else I am missing. Appreciate any help that can be provided or point in right direction.