Error 35250 is 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:
Following is a list of common causes for this
failure and steps to diagnose each.
CAUSE 1: 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.
Port 5022 is used by the primary and secondary
replicas for synchronization and communication purposes. Traffic must be
allowed inbound on this port. Testing has shown that if port 5022 inbound
traffic is blocked at the primary, at the secondary or at both, you will be
unable to create the availability group and message 35250 will be reported.
For additional settings of SQL Server in
Windows Firewall, please follow the article “Configure a Windows Firewall for
Database Engine Access” (https://technet.microsoft.com/en-us/library/ms175043(v=sql.110).aspx).
CAUSE 2: ENDPOINT IS NOT CREATED OR STARTED
Ensure the mirroring endpoints are created and
started on the primary and the secondary replicas.
To detect if the endpoints are created, query
for database_mirroring endpoints at the primary and the secondary.
:Connect SQLNODE1
select name, state_desc, port from sys.tcp_endpoints
where type_desc='DATABASE_MIRRORING'
go
:Connect SQLNODE2
select name, state_desc, port from sys.tcp_endpoints
where type_desc='DATABASE_MIRRORING'
go
If you find an endpoint does not exist, create
it:
:Connect SQLNODE1
create endpoint [Hadr_endpoint]
state=started
as tcp (listener_port = 5022, listener_ip = all)
for database_mirroring (role = all, authentication =
windows negotiate, encryption = required algorithm aes)
go
To detect if the endpoints are started, query them
at the primary and the secondary.
:Connect SQLNODE1
select name, state_desc, port FROM sys.tcp_endpoints
where name='hadr_endpoint'
go
:Connect SQLNODE2
select name, state_desc, port from sys.tcp_endpoints
where name='hadr_endpoint'
go
If you find an endpoint is not running, start
it:
:Connect SQLNODE1
alter endpoint [Hadr_endpoint] state = started
IMPORTANT: There is a known issue in which
sys.tcp_endpoints.state_desc may incorrectly report the endpoint as STARTED
when it is not started. It is a good idea to execute the ALTER ENDPOINT command
for the endpoint at each replica, despite the report given by
sys.tcp_endpoints, queried above.
CAUSE 3: ENDPOINT PERMISSIONS
If database mirroring endpoints are configured
to use Windows authentication, ensure that the SQL Server instances hosting
your availability replicas run with a SQL Server startup account are domain
accounts.
The New Availability Group wizard catches this
misconfiguration, and reports it. Note that the Endpoints tab is displayed and
the SQL Server Service Account reports that one of the replica's startup
account is configured for LocalSystem.
If you proceed by clicking Yes and complete the
wizard, it will fail during the 'Joining <availability database> to
availability group <availability group> at <replica>.'
If you are attempting to create an availability
group using transact-sql, this configuration problem will not be detected and
command ALTER DATABASE...SET HADR AVAILABILITY GROUP... will fail with 35250.
CAUSE 4: ADDITIONAL ENDPOINT PERMISSIONS
If the instances of SQL Server run as the
Network Service account, the login of the each host computer account
(DomainName\ComputerName$) must be created in master on each of the remote
server instances and that login must be granted CONNECT permissions on the
endpoint. This is because a server instance running under the Network Service
account authenticates using the domain account of the host computer.
Here are steps to identify the endpoint and how
to give CONNECT permissions to a computer account.
To
identify the endpoint:
USE master;
GO
SELECT name, state_desc, port from sys.tcp_endpoints
where type_desc='DATABASE_MIRRORING'
GO
To
give CONNECT permissions on the endpoint for SQL Servers. Execute below for
each server on each server:
USE master;
GO
CREATE LOGIN [Contoso\SQL1$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Hadr_endpoint TO
[Contoso\SQL1$];
GO
CAUSE 5: SQL SERVER IS NOT LISTENING ON PORT 5022
If SQL Server is unable to listen on port 5022,
the New Availability Group wizard and the transact-sql command ALTER
DATABASE...SET HADR AVAILABILITY GROUP...
will fail with 35250.
To determine if SQL Server is listening on port
5022, review the SQL Server error log. You should find the following message(s)
in the SQL Server error log:
2013-12-09 08:52:25.47 spid23s
Server is listening on [ 'any' <ipv6> 5022].
2013-12-09 08:52:25.47 spid23s
Server is listening on [ 'any' <ipv4> 5022].
SQL Server may not be able to listen on port
5022 if another application is already listening on the port. If you find that
SQL Server is not listening on port 5022 because it is already being used, run
'netstat -a' to determine what application is using the port.
ADDITIONAL DIAGNOSTICS - QUERY SYS.HADR_AVAILABILITY_REPLICA_STATES ON
THE SECONDARY
If the above settings check out, query the
sys.dm_hadr_availability_replica_states for the last_connect_error_number which
may help you diagnose the join issue. Depending on which replica was having
difficulty communicating, you should query both the primary and secondary for
the local 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=1
For example, if the secondary was unable to
communicate with the DNS server or if a replica's endpoint_url was configured
incorrectly when creating the availability group, you may get the following
results:
SOURCE:
- Create Availability Group Fails With Error 35250 'Failed to join the database': http://blogs.msdn.com/b/alwaysonpro/archive/2013/12/09/trouble-shoot-error.aspx
- SQL AlwaysOn: Failed to join the database to the Availability Group (Error 35250): http://blogs.msdn.com/b/svarukala/archive/2014/03/31/sql-alwayson-failed-to-join-the-database-to-the-availability-group-error-35250.aspx
