Troubleshoot Always On Availability Groups Configuration (SQL Server)
Published: May 17, 2016
Updated: May 17, 2016
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
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:
|Always On 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 2016 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.|
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.
Do the accounts have the correct permissions?
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.
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).
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.
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).
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
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.
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).
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.
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).
Check whether the network service account has connect permission to the endpoint.
Verify the following configuration values settings and correct them if necessary.
|Current primary replica||Ensure that the availability group listener is online.||To verify whether the listener is online:|
To restart an offline listener:
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
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 Always On|
ALTER AVAILABILITY GROUP (Transact-SQL)
|Client system||Verify 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)