alwayson只读副本失效问题

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

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

 

AlwaysOn 可用性组配置疑难解答 (SQL Server)Troubleshoot Always On Availability Groups Configuration (SQL Server)

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

本主题适用于：是SQL Server否Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库THIS TOPIC APPLIES TO:yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

本主题提供的信息可帮助您解决在为 AlwaysOn 可用性组Always On availability groups配置服务器实例时遇到的典型问题。This topic provides information to help you troubleshoot typical problems with configuring server instances for AlwaysOn 可用性组Always On availability groups. 典型配置问题包括 AlwaysOn 可用性组Always On availability groups 被禁用、帐户配置不当、数据库镜像端点不存在、端点无法访问（SQL Server 错误 1418）、网络访问不存在，以及联接数据库命令失败（SQL Server 错误 35250）。Typical configuration problems include AlwaysOn 可用性组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).

备注

确保您满足 AlwaysOn 可用性组Always On availability groups 的先决条件。Ensure that you are meeting the AlwaysOn 可用性组Always On availability groups prerequisites. 有关详细信息，请参阅 针对 AlwaysOn 可用性组的先决条件、限制和建议 (SQL Server)配置服务器实例时遇到的典型问题。For more information, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

本主题内容：In This Topic:

部分Section描述Description
未启用 AlwaysOn 可用性组Always On Availability Groups Is Not Enabled 如果 SQL ServerSQL Server 实例未启用 AlwaysOn 可用性组Always On availability groups，该实例则不支持创建可用性组，也无法承载任何可用性副本。If an instance of SQL ServerSQL Server is not enabled for AlwaysOn 可用性组Always On availability groups, the instance does not support availability group creation and cannot host any availability replicas.
帐户Accounts 介绍了正确配置运行 SQL ServerSQL Server 所用的帐户的相关要求。Discusses requirements for correctly configuring the accounts under which SQL ServerSQL Server is running.
端点Endpoints 介绍如何诊断与服务器实例的数据库镜像端点有关的问题。Discusses how to diagnose issues with the database mirroring endpoint of a server instance.
系统名称System name 概述了在端点 URL 中指定服务器实例的系统名称的备选方法。Summarizes the alternatives for specifying the system name of a server instance in an endpoint URL.
网络访问Network access 记录了承载可用性副本的每个服务器实例必须能够通过 TCP 访问其他各个服务器实例的端口的要求。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.
端点访问（SQL Server 错误 1418）Endpoint Access (SQL Server Error 1418) 包含有关此 SQL ServerSQL Server 错误消息的信息。Contains information about this SQL ServerSQL Server error message.
联接数据库失败（SQL Server 错误 35250）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 包含 SQL Server 2017SQL Server 2017 联机丛书中专门针对排除可用性组配置问题的面向任务的主题列表。Contains a list of task-oriented topics in SQL Server 2017SQL Server 2017 Books Online that are particularly relevant to troubleshooting an availability group configuration.
相关内容Related Content 包含 SQL ServerSQL Server 联机丛书以外的相关资源的列表。Contains a list of relevant resources that are external to SQL ServerSQL Server Books Online.

未启用 AlwaysOn 可用性组Always On Availability Groups Is Not Enabled

必须在每个 AlwaysOn 可用性组Always On availability groups 实例上启用 SQL Server 2017SQL Server 2017功能。The AlwaysOn 可用性组Always On availability groups feature must be enabled on each of the instances of SQL Server 2017SQL Server 2017. 有关详细信息，请参阅启用和禁用 AlwaysOn 可用性组 (SQL Server)。For more information, see Enable and Disable Always On Availability Groups (SQL Server).

帐户Accounts

必须正确配置运行 SQL ServerSQL Server 所用的帐户。The accounts under which SQL ServerSQL Server is running must be correctly configured.

  1. 帐户是否具有正确的权限？Do the accounts have the correct permissions?

    1. 如果伙伴使用相同的域用户帐户运行，则正确的用户登录名将自动存在于全部两个 master 数据库中。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. 如果两个服务器实例使用不同的帐户运行，则必须在远程服务器实例上的 master 数据库中创建每个登录帐户，并且必须向该登录帐户授予 CONNECT 权限，以便连接到该服务器实例的数据库镜像端点。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. 有关详细信息，请参阅设置数据库镜像或 AlwaysOn 可用性组的登录帐户 (SQL Server)。For more information, seeSet Up Login Accounts for Database Mirroring or Always On Availability Groups (SQL Server).

  2. 如果 SQL ServerSQL Server 正在以内置帐户（例如 Local System、Local Service 或 Network Service）或非域帐户运行，则您必须使用证书来进行端点身份验证。If SQL ServerSQL 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. 如果您的服务帐户使用的是同一个域中的域帐户，则您可以选择为所有副本位置上的每个服务帐户授予 CONNECT 访问权限，或者您可以使用证书。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. 有关详细信息，请参阅使用数据库镜像终结点证书 (Transact-SQL)。For more information, seeUse Certificates for a Database Mirroring Endpoint (Transact-SQL).

端点Endpoints

必须正确配置端点。Endpoints must be correctly configured.

  1. 确保要托管可用性副本（每个副本位置）的各个 SQL ServerSQL Server 实例都具有数据库镜像终结点。Make sure that each instance of SQL ServerSQL Server that is going to host an availability replica (each replica location) has a database mirroring endpoint. 若要确定给定服务器实例上是否存在数据库镜像终结点，请使用 sys.database_mirroring_endpoints 目录视图。To determine whether a database mirroring endpoint exists on a given server instance, use the sys.database_mirroring_endpoints catalog view. 有关详细信息，请参阅创建 Windows 身份验证的数据库镜像终结点 (Transact-SQL) 或允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL)。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.

    若要标识当前与服务器实例的数据库镜像端点关联的端口，请使用以下 Transact-SQLTransact-SQL 语句：To identify the port currently associated with database mirroring endpoint of a server instance, use the following Transact-SQLTransact-SQL statement: 

    SELECT type_desc, port FROM sys.tcp_endpoints;  
GO

  3. 对于难以解释的 AlwaysOn 可用性组Always On availability groups 设置问题，建议您检查每个服务器实例以确定它是否正在侦听相应的端口。For AlwaysOn 可用性组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. 确保已启动端点 (STATE = STARTED)。Make sure that the endpoints are started (STATE=STARTED). 对于各个服务器实例，使用以下 Transact-SQLTransact-SQL 语句：On each server instance, use the following Transact-SQLTransact-SQL statement: 

    SELECT state_desc FROM sys.database_mirroring_endpoints

    有关 state_desc 列的详细信息，请参阅 sys.database_mirroring_endpoints (Transact-SQL)。For more information about the state_desc column, see sys.database_mirroring_endpoints (Transact-SQL).

    若要启动端点，请使用以下 Transact-SQLTransact-SQL 语句：To start an endpoint, use the following Transact-SQLTransact-SQL statement: 

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

    有关详细信息，请参阅 ALTER ENDPOINT (Transact-SQL)。For more information, see ALTER ENDPOINT (Transact-SQL).

  5. 确保其他服务器的登录帐户具有 CONNECT 权限。Make sure that the login from the other server has CONNECT permission. 若要确定哪个登录帐户拥有对端点的 CONNECT 权限，请对每个服务器实例使用以下 Transact-SQLTransact-SQL 语句：To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQLTransact-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 NameSystem Name

对于端点 URL 中服务器实例的系统名称，可以使用明确标识系统的任何名称。For the system name of a server instance in an endpoint URL, you can use any name that unambiguously identifies the system. 服务器地址可以是系统名称（如果各系统都在同一个域中）、完全限定域名或 IP 地址（最好是静态 IP 地址）。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. 有关详细信息，请参阅 在添加或修改可用性副本时指定终结点 URL (SQL Server)配置服务器实例时遇到的典型问题。For more information, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

Network AccessNetwork Access

要承载可用性副本的每个服务器实例必须能够通过 TCP 访问其他各个服务器实例的端口。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).

端点访问（SQL Server 错误 1418）Endpoint Access (SQL Server Error 1418)

此 SQL ServerSQL Server 消息指示无法到达端点 URL 中指定的服务器网络地址或该地址不存在，同时建议您确认网络地址名称并重新发出命令。This SQL ServerSQL 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.

联接数据库失败（SQL Server 错误 35250）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. 检查防火墙设置，确定是否允许在承载主副本的服务器实例与辅助副本之间进行端点端口通信（默认情况下为端口 5022）。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. 检查网络服务帐户是否拥有对端点的 CONNECT 权限。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)sys.dm_tcp_listener_states (Transact-SQL)

ALTER AVAILABILITY GROUP (Transact-SQL)ALTER AVAILABILITY GROUP (Transact-SQL)
复选框Checkbox 当前主副本Current primary replica 确保 READ_ONLY_ROUTING_LIST 仅包含承载可读辅助副本的服务器实例。Ensure that the READ_ONLY_ROUTING_LIST contains only server instances that are hosting a readable secondary replica. 标识可读次要副本： sys.availability_replicas（secondary_role_allow_connections_desc 列）To identify readable secondary replicas: sys.availability_replicas (secondary_role_allow_connections_desc column)

查看只读路由列表： sys.availability_read_only_routing_listsTo view a read-only routing list: sys.availability_read_only_routing_lists

更改只读路由列表： ALTER AVAILABILITY GROUPTo change a read-only routing list: ALTER AVAILABILITY GROUP		 sys.availability_replicas (Transact-SQL)sys.availability_replicas (Transact-SQL)

sys.availability_read_only_routing_lists (Transact-SQL)sys.availability_read_only_routing_lists (Transact-SQL)

ALTER AVAILABILITY GROUP (Transact-SQL)ALTER AVAILABILITY GROUP (Transact-SQL)
复选框Checkbox read_only_routing_list 中的每个副本Every replica in the read_only_routing_list 请确保 Windows 防火墙未在阻止 READ_ONLY_ROUTING_URL 端口。Ensure that the Windows firewall is not blocking the READ_ONLY_ROUTING_URL port. —— 为数据库引擎访问配置 Windows 防火墙Configure a Windows Firewall for Database Engine Access
复选框Checkbox read_only_routing_list 中的每个副本Every replica in the read_only_routing_list 在 SQL ServerSQL Server 配置管理器中，请确认：In SQL ServerSQL Server Configuration Manager, verify that:

已启用 SQL Server 远程连接。SQL Server remote connectivity is enabled.

已启用 TCP/IP。TCP/IP is enabled.

IP 地址已正确配置。The IP addresses are configured correctly.		 —— 查看或更改服务器属性 (SQL Server)View or Change Server Properties (SQL Server)

配置服务器以侦听特定 TCP 端口（SQL Server 配置管理器）Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
复选框Checkbox read_only_routing_list 中的每个副本Every replica in the read_only_routing_list 确保 READ_ONLY_ROUTING_URL (TCP://system-address:port)** 包含正确的完全限定的域名 (FQDN) 和端口号。Ensure that the READ_ONLY_ROUTING_URL (TCP://system-address:port) contains the correct fully-qualified domain name (FQDN) and port number. —— 计算 AlwaysOn 的 read_only_routing_urlCalculating read_only_routing_url for Always On

sys.availability_replicas (Transact-SQL)sys.availability_replicas (Transact-SQL)

ALTER AVAILABILITY GROUP (Transact-SQL)ALTER AVAILABILITY GROUP (Transact-SQL)
复选框Checkbox 客户端系统Client system 确认客户端驱动程序支持只读路由。

　　

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

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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xiaojwang/p/9584065.html