Troubleshoot Always On Availability Groups Configuration

Troubleshoot Always On Availability Groups Configuration (SQL Server)

 

Published: May 17, 2016

Updated: May 17, 2016

Applies To: SQL Server 2016

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

System_CAPS_ICON_note.jpg Note


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

In This Topic:

SectionDescription
Always On Availability Groups Is Not EnabledIf 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.
AccountsDiscusses requirements for correctly configuring the accounts under which SQL Server is running.
EndpointsDiscusses how to diagnose issues with the database mirroring endpoint of a server instance.
System nameSummarizes the alternatives for specifying the system name of a server instance in an endpoint URL.
Network accessDocuments 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 TasksContains a list of task-oriented topics in SQL Server 2016 Books Online that are particularly relevant to troubleshooting an availability group configuration.
Related ContentContains a list of relevant resources that are external to SQL Server Books Online.

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

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 Always On 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 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  
    
    
    

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

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

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.

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.

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

On…ActionCommentsLink
CheckboxCurrent primary replicaEnsure 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)
CheckboxCurrent primary replicaEnsure 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)
CheckboxEvery replica in the read_only_routing_listEnsure that the Windows firewall is not blocking the READ_ONLY_ROUTING_URL port.Configure a Windows Firewall for Database Engine Access
CheckboxEvery replica in the read_only_routing_listIn 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)
CheckboxEvery replica in the read_only_routing_listEnsure 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 Always On

 sys.availability_replicas (Transact-SQL)

 ALTER AVAILABILITY GROUP (Transact-SQL)
CheckboxClient systemVerify that the client driver supports read-only routing.Always On Client Connectivity (SQL Server)

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

Community Additions

ADD
Show:
© 2016 Microsoft