Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)

This topic provides information to help you troubleshoot typical problems with configuring server instances for Always On Availability Groups. Typical configuration problems include Always On Availability Groups is disabled, accounts are incorrectly configured, the database mirroring endpoint does not exist, the endpoint is inaccessible (SQL Server Error 1418), network access does not exist, and a join database command fails (SQL Server Error 35250).

In This Topic:

AlwaysOn Availability Groups Is Not Enabled

The Always On Availability Groups feature must be enabled on each of the instances of SQL Server 2014. For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server).

Accounts

The accounts under which SQL Server is running must be correctly configured.

1. Do the accounts have the correct permissions?

   1. If the partners run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration the database and is recommended.

   2. If two server instances run as different accounts, the login each account must be created in master on the remote server instance, and that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance. For more information, seeSet Up Login Accounts for Database Mirroring or AlwaysOn Availability Groups (SQL Server).

2. If SQL Server is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If your service accounts are using domain accounts in the same domain, you can choose to grant CONNECT access for each service account on all the replica locations or you can use certificates. For more information, seeUse Certificates for a Database Mirroring Endpoint (Transact-SQL).

Endpoints

Endpoints must be correctly configured.

1. Make sure that each instance of SQL Server that is going to host an availability replica (each replica location) has a database mirroring endpoint. To determine whether a database mirroring endpoint exists on a given server instance, use the sys.database_mirroring_endpoints catalog view. For more information, see either Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL) or Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

2. Check that the port numbers are correct.

   To identify the port currently associated with database mirroring endpoint of a server instance, use the following Transact-SQL statement:

   SELECT type_desc, port FROM sys.tcp_endpoints;
   GO
   

3. For Always On Availability Groups setup issues that are difficult to explain, we recommend that you inspect each server instance to determine whether it is listening on the correct ports. For information about verifying port availability, see MSSQLSERVER_1418.

4. Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement:

   SELECT state_desc FROM sys.database_mirroring_endpoints
   

   For more information about the state_desc column, see sys.database_mirroring_endpoints (Transact-SQL).

   To start an endpoint, use the following Transact-SQL statement:

   ALTER ENDPOINT Endpoint_Mirroring 
   STATE = STARTED 
   AS TCP (LISTENER_PORT = <port_number>)
   FOR database_mirroring (ROLE = ALL);
   GO
   

   For more information, see ALTER ENDPOINT (Transact-SQL).

5. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement:

   SELECT 'Metadata Check';
   SELECT EP.name, SP.STATE, 
      CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
         AS GRANTOR, 
      SP.TYPE AS PERMISSION,
      CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
         AS GRANTEE 
      FROM sys.server_permissions SP , sys.endpoints EP
      WHERE SP.major_id = EP.endpoint_id
      ORDER BY Permission,grantor, grantee; 
   GO
   
   

System Name

For the system name of a server instance in an endpoint URL, you can use any name that unambiguously identifies the system. The server address can be a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address (preferably, a static IP address). Using the fully qualified domain name is guaranteed to work. For more information, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

Network Access

Each server instance that is hosting an availability replica must be able to access the port of each of the other server instance over TCP. This is especially important if the server instances are in different domains that do not trust each other (untrusted domains).

Endpoint Access (SQL Server Error 1418)

This SQL Server message indicates that the server network address specified in the endpoint URL cannot be reached or does not exist, and it suggests that you verify the network address name and reissue the command. For more information, see MSSQLSERVER_1418.

Join Database Fails (SQL Server Error 35250)

This section discusses the possible causes and resolution of a failure to join secondary databases to the availability group because the connection to the primary replica is not active.

Resolution:

1. Check the firewall setting to see if whether allows the endpoint port communication between the server instances that host primary replica and the secondary replica (port 5022 by default).

2. Check whether the network service account has connect permission to the endpoint.

Read-Only Routing is Not Working Correctly

Verify the following configuration values settings and correct them if necessary.

	On...	Action	Comments	Link
	Current primary replica	Ensure that the availability group listener is online.	To verify whether the listener is online: SELECT * FROM sys.dm_tcp_listener_states; To restart an offline listener: ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'myAG_Listener';	sys.dm_tcp_listener_states (Transact-SQL) ALTER AVAILABILITY GROUP (Transact-SQL)
	Current primary replica	Ensure that the READ_ONLY_ROUTING_LIST contains only server instances that are hosting a readable secondary replica.	To identify readable secondary replicas: sys.availability_replicas (secondary_role_allow_connections_desc column) To view a read-only routing list: sys.availability_read_only_routing_lists To change a read-only routing list: ALTER AVAILABILITY GROUP	sys.availability_replicas (Transact-SQL) sys.availability_read_only_routing_lists (Transact-SQL) ALTER AVAILABILITY GROUP (Transact-SQL)
	Every replica in the read_only_routing_list	Ensure that the Windows firewall is not blocking the READ_ONLY_ROUTING_URL port.	-	Configure a Windows Firewall for Database Engine Access
	Every replica in the read_only_routing_list	In SQL Server Configuration Manager, verify that: SQL Server remote connectivity is enabled. TCP/IP is enabled. The IP addresses are configured correctly.	-	View or Change Server Properties (SQL Server) Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
	Every replica in the read_only_routing_list	Ensure that the READ_ONLY_ROUTING_URL (TCP://system-address:port) contains the correct fully-qualified domain name (FQDN) and port number.	-	Calculating read_only_routing_url for AlwaysOn sys.availability_replicas (Transact-SQL) ALTER AVAILABILITY GROUP (Transact-SQL)
	Client system	Verify that the client driver supports read-only routing.	-	AlwaysOn Client Connectivity (SQL Server)

Related Tasks

• Creation and Configuration of Availability Groups (SQL Server)

• Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)

• Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server)

• Manually Prepare a Secondary Database for an Availability Group (SQL Server)

• Troubleshoot a Failed Add-File Operation (AlwaysOn Availability Groups)

• Management of Logins and Jobs for the Databases of an Availability Group (SQL Server)

• Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)

Related Content

• View Events and Logs for a Failover Cluster

• Get-ClusterLog Failover Cluster Cmdlet

• SQL Server AlwaysOn Team Blog: The official SQL Server AlwaysOn Team Blog

See Also

Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server) Client Network Configuration Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)