测试环境上安装了AlwaysOn，但是发现只读连接后数据还是连接到主库，没有连接到只读库，经检查发现是因为只读副本库的端口没有配置为1433，后通过只读库的SQl Server配置管理器-Sql Server网络配置-MSSQLSERVER的协议-TCP/IP中将TCP/IP属性-IP地址-IPAll的TCP DynamicPorts设置为1433，问题解决。

以下是微软的针对AlwaysOn的常见问题解答

Troubleshoot Always On Availability Groups Configuration (SQL Server)

• 未启用 AlwaysOn 可用性组

1. 帐户
2. 端点
3. System Name
4. Network Access
5. 端点访问（SQL Server 错误 1418）
6. 联接数据库失败（SQL Server 错误 35250）
7. 只读路由未正确工作
8. 相关任务
9. 相关内容
10. 另请参阅

SQL ServerAzure SQL 数据库Azure SQL 数据仓库并行数据仓库SQL ServerAzure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

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

Enable and Disable Always On Availability Groups (SQL Server).

Accounts

SQL Server is running must be correctly configured.

1. Do the accounts have the correct permissions?

   1. This simplifies the security configuration the database and is recommended.

   2. Set Up Login Accounts for Database Mirroring or Always On Availability Groups (SQL Server).

2. Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

Endpoints

Endpoints must be correctly configured.

1. Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

2. Check that the port numbers are correct.

   Transact-SQL statement:

   复制

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

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

4. Transact-SQL statement:

   复制

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   sys.database_mirroring_endpoints (Transact-SQL).

   Transact-SQL statement:

   复制

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

   ALTER ENDPOINT (Transact-SQL).

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

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

Network Access

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)

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.

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.

　　

---恢复内容结束---

---恢复内容结束---