Issue:
Unable to join restored database to Availability Group
You can create an availability group using the New Availability Group wizard or using a series of transact-sql commands. A common failure can occur when attempting to create an availability group using either method. The error is as follows and is caused because the target primary and secondary replicas are unable to communicate:
Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.
If creating the availability group using the New Availability Group wizard, the ‘Joining <availability database> to availability group <availability group> at <replica>’ will fail. Clicking the Error link, message 35250 is reported.
If you used transact-sql to create the availability group instead of the New Availability Group wizard, the ALTER DATABASE…SET HADR AVAILABILITY GROUP… command fails with message 35250.
Query:
select name, type, port from sys.tcp_endpoints where type_desc='database_mirroring'
go
select name, state_desc,port from sys.tcp_endpoints where name = 'hadr_endpoint'
go
--connect secondary replica
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=1Fix:
Reason – Must use domain account to run SQL Server service.
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=1Make sure you create a domain service account and run SQL Service service using that account. Else you will get this error…
The program issued a command but the command length is incorrect.Reason – Inbound Port 5022 Traffic is Blocked
IMPORTANT : By default, AlwaysOn configures the database mirroring endpoints to use port 5022, and the following document will use 5022 when discussing the database mirroring endpoint in use for availability groups. However, this may not be the configured port for the database mirroring endpoints in your environment. Query sys.tcp_endpoints on each replica to confirm which port is being used:
select name, type, port from sys.tcp_endpoints where type_desc=’database_mirroring’
go
By default inbound traffic is blocked in Windows firewall.
Comments