Share via

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)

  • Article

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).

Note

Ensure that you are meeting the Always On Availability Groups prerequisites. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

In This Topic:

Section Description
AlwaysOn Availability Groups Is Not Enabled If an instance of SQL Server is not enabled for Always On Availability Groups, the instance does not support availability group creation and cannot host any availability replicas.
Accounts Discusses requirements for correctly configuring the accounts under which SQL Server is running.
Endpoints Discusses how to diagnose issues with the database mirroring endpoint of a server instance.
System name Summarizes the alternatives for specifying the system name of a server instance in an endpoint URL.
Network access Documents the requirement that each server instance that is hosting an availability replica must be able to access the port of each of the other server instances over TCP.
Endpoint Access (SQL Server Error 1418) Contains information about this SQL Server error message.
Join Database Fails (SQL Server Error 35250) Discusses the possible causes and resolution of a failure to join secondary databases to an availability group because the connection to the primary replica is not active.
Read-Only Routing is Not Working Correctly
Related Tasks Contains a list of task-oriented topics in SQL Server 2014 Books Online that are particularly relevant to troubleshooting an availability group configuration.
Related Content Contains a list of relevant resources that are external to SQL Server Books Online.

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
Checkbox 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)
Checkbox 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)
Checkbox 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
Checkbox 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)
Checkbox 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)
Checkbox Client system Verify that the client driver supports read-only routing. - AlwaysOn Client Connectivity (SQL Server)

Related Tasks

Related Content

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)