AlwaysOn 可用性组配置疑难解答 (SQL Server)

适用于：SQL Server

本文提供的信息有助于排查为 AlwaysOn 可用性组配置服务器实例时遇到的典型问题。 典型配置问题包括 Always On 可用性组禁用、帐户配置不当、数据库镜像终结点不存在、终结点无法访问（SQL Server 错误 1418）、网络访问不存在，以及联接数据库命令失败（SQL Server 错误 35250）。

本主题内容：

未启用 AlwaysOn 可用性组

必须在每个 SQL Server 实例上启用 Always On 可用性组功能。

如果未启用 Always On 可用性组功能，尝试在 SQL Server 上创建可用性组时会收到此错误消息。

The Always On Availability Groups feature must be enabled for server instance 'SQL1VM' before you can create an availability group on this instance. To enable this feature, open the SQL Server Configuration Manager, select SQL Server Services, right-click on the SQL Server service name, select Properties, and use the Always On Availability Groups tab of the Server Properties dialog. Enabling Always On Availability Groups may require that the server instance is hosted by a Windows Server Failover Cluster (WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

该错误消息明确指出未启用 AG 功能，同时提供有关启用该功能的指导。 除了最初没有启用 AG 的明显情况之外，还有另外两种情况下您可能会进入这种状态。

1. 如果在安装 Windows 故障转移群集功能之前启用了 SQL Server 并启用了 AlwaysOn 可用性组功能，则尝试创建 AlwaysOn AG 时可能会遇到此错误。
2. 如果在 SQL Server 仍配置 Always On 的情况下删除现有的 Windows 故障转移群集功能并重新生成该功能，则再次尝试使用 AG 时，可能会出现此错误。

在这种情况下，可以执行以下步骤来解决此错误：

1. 禁用 AG 功能
2. 重启 SQL Server 服务
3. 重新启用 AG 功能
4. 再次重启 SQL 服务

有关详细信息，请参阅启用和禁用 AlwaysOn 可用性组 (SQL Server)。

帐户

必须正确配置运行 SQL Server 所用的帐户。

1. 帐户是否具有正确的权限？

   1. 如果合作伙伴以相同的域用户帐户运行，则正确的用户登录名将自动存在于全部两个 master 数据库中。 这样可简化安全配置，建议这样做。

   2. 如果两个服务器实例以不同帐户运行，则必须在远程服务器实例上的 master 数据库中创建每个帐户，并且必须向该服务器主体授予 CONNECT 权限，以便连接到该服务器实例的数据库镜像终结点。 有关详细信息，请参阅 设置用于数据库镜像或 Always On 可用性组的登入帐户 (SQL Server)。 可对每个实例使用以下查询来检查登录名是否具有 CONNECT 权限：

   SELECT 
     perm.class_desc,
     prin.name,
     perm.permission_name,
     perm.state_desc,
     prin.type_desc as PrincipalType,
     prin.is_disabled
   FROM sys.server_permissions perm
     LEFT JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
     LEFT JOIN sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id
   WHERE 
     perm.class_desc = 'ENDPOINT'
     AND perm.permission_name = 'CONNECT'
     AND tep.type = 4    
   

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

端点

必须正确配置端点。

1. 确保要托管可用性副本（每个 副本位置）的每个 SQL Server 实例都有一个数据库镜像终结点。 若要确定给定服务器实例上是否存在数据库镜像终结点，请使用 sys.database_mirroring_endpoints 目录视图：

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   有关创建终结点的详细信息，请参阅创建 Windows 身份验证的数据库镜像终结点 (Transact-SQL) 或允许数据库镜像终结点使用证书进行出站连接 (Transact-SQL)。

2. 检查端口号是否正确。

   若要标识当前与服务器实例的数据库镜像端点关联的端口，请使用以下 Transact-SQL 语句：

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. 对于难以解释的 Always On 可用性组 设置问题，建议你检查每个服务器实例以确定它是否正在侦听相应的端口。

4. 确保端点处于启动状态 (STATE=STARTED)。 对于各个服务器实例，使用以下 Transact-SQL 语句：

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   有关 state_desc 列的详细信息，请参阅 sys.database_mirroring_endpoints (Transact-SQL)。

   若要启动端点，请使用以下 Transact-SQL 语句：

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

   有关详细信息，请参阅 ALTER ENDPOINT (Transact-SQL)。

   注意

   在某些情况下，如果终结点已启动，但 AG 副本未通信，请尝试停止并重启终结点。 可以使用 ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED，然后使用 ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED

5. 确保其他服务器的登录帐户具有 CONNECT 权限。 若要确定哪个登录帐户拥有对网络端点的 CONNECT 权限，请在每个服务器实例上使用以下 Transact-SQL 语句：

   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;   
   

6. 确保终结点 URL 中使用了正确的服务器名称

   对于终结点 URL 中的服务器名称，建议使用完全限定的域名 (FQDN)，不过你可以使用可唯一标识计算机的任何名称。 服务器地址可以是 Netbios 名称（如果系统位于同一域中）、完全限定的域名 (FQDN) 或 IP 地址（最好是静态 IP 地址）。 建议选择使用完全限定的域名。

   如果已定义终结点 URL，则可以使用以下内容对其进行查询：

   select endpoint_url from sys.availability_replicas
   

   接下来，将 endpoint_url 输出与服务器名称（NetBIOS 名称或 FQDN）进行比较。 要查询服务器名称，请在本地副本的 PowerShell 中运行以下命令：

   $env:COMPUTERNAME
   [System.Net.Dns]::GetHostEntry([string]$env:computername).HostName
   

   要验证远程计算机上的服务器名称，请使用 PowerShell 运行以下命令。

   $servername_from_endpoint_url = "server_from_endpoint_url_output"
   
   Test-NetConnection -ComputerName $servername_from_endpoint_url
   

   有关详细信息，请参阅在添加或修改可用性副本时指定终结点 URL (SQL Server)。

网络访问

承载可用性副本的每个服务器实例必须能够通过 TCP 访问其他服务器实例的端口。 当服务器实例位于相互不信任的不同域（不可信的域）中时，这尤为重要。 按照以下步骤检查你能否连接到终结点：

• 使用 Test-NetConnection（等效于 Telnet）验证连接性。 下面是可使用的命令示例：

  $server_name = "your_server_name"
  $IP_address = "your_ip_address"
  $port_number = "your_port_number"
  
  Test-NetConnection -ComputerName $server_name -Port $port_number
  Test-NetConnection -ComputerName $IP_address -Port $port_number
  

• 如果终结点正在侦听且连接成功，则会看到“TcpTestSucceeded ： True”。 如果没有，则会收到“TcpTestSucceeded ： False”。

• 如果到 IP 地址的 Test-NetConnection (Telnet) 连接正常，但到 ServerName 的连接不正常，则可能是 DNS 或名称解析问题

• 如果连接按 ServerName 而不是 IP 地址工作，则该服务器上可能定义了多个终结点（另一个 SQL 实例可能正在侦听该端口）。 尽管问题实例上的终结点状态显示为“STARTED”，但实际上可能是另一个实例在绑定端口，这会阻止正确的实例侦听和建立 TCP 连接。

• 如果 Test-NetConnection 无法连接，请查找可能阻止有问题的终结点端口的防火墙和/或防病毒软件。 检查防火墙设置，查看是否允许在承载主副本的服务器实例与辅助副本之间进行终结点端口通信（默认情况下为端口 5022）。 运行以下 PowerShell 脚本，检查是否有已禁用的入站流量规则

• 如果在 Azure VM 上运行 SQL Server，还需要 确保网络安全组（NSG）允许流量到终结点端口。 检查防火墙设置（对于 Azure VM，请查看 NSG 设置），查看是否允许在承载主副本的服务器实例与辅助副本之间进行终结点端口通信（默认情况下为端口 5022）

  Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
  

• 获取 Get-NetTCPConnection 命令（等效于 NETSTAT -a）的输出，验证在指定终结点的 IP:端口 上状态是否为 LISTENING 或 ESTABLISHED

  Get-NetTCPConnection 
  

侦听器

若要正确配置可用性组侦听器，请遵循“为 AlwaysOn 可用性组配置侦听器”

1. 配置侦听器后，可以使用以下查询验证它正在侦听的 IP 地址和端口：

   $server_name = $env:computername  #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S$server_name -Q"SELECT dns_name AS AG_listener_name, port, ip_configuration_string_from_cluster 
   FROM sys.availability_group_listeners"
   

2. 还可使用此查询查找侦听器信息以及 SQL Server 端口：

   $server_name = $env:computername      #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S($server_name) -Q("SELECT  convert(varchar(32), SERVERPROPERTY ('servername')) servername, convert(varchar(32),ip_address) ip_address, port, type_desc,state_desc, start_time 
   FROM sys.dm_tcp_listener_states 
   WHERE ip_address not in ('127.0.0.1', '::1') and type <> 2")
   

3. 如果需要建立与侦听器的连接并怀疑某个端口被阻止，可使用 PowerShell Test-NetConnection cmdlet（等效于 telnet）执行测试。

   $listener_name = "your_ag_listener"
   $IP_address = "your_ip_address"
   $port_number = "your_port_number"
   
   Test-NetConnection -ComputerName $listener_name -Port $port_number
   Test-NetConnection -ComputerName $IP_address -Port $port_number
   

4. 最后，检查侦听器是否正在侦听指定的端口：

   $port_number = "your_port_number"
   
   Get-NetTCPConnection -LocalPort $port_number -State Listen
   

端点访问（SQL Server 错误 1418）

此 SQL Server 消息指示无法到达端点 URL 中指定的服务器网络地址或该地址不存在，同时建议确认网络地址名称并重新发出命令。

联接数据库失败（SQL Server 错误 35250）

此部分介绍由于与主副本的连接处于非活动状态而导致未能将辅助数据库联接到可用性组的可能原因和解决方法。 以下是完整的错误消息：

Msg 35250 The connection to the primary replica is not active. The command cannot be processed.

解决方法：

步骤概述如下。

有关详细的分步说明，请参阅引擎错误 MSSQLSERVER_35250

1. 请确保终结点已创建且已启动。
2. 检查是否可以通过 Telnet 连接到终结点，并确保没有防火墙规则阻止连接
3. 检查系统中是否有错误。 可以查询 sys.dm_hadr_availability_replica_states 以获取 last_connect_error_number，该错误号可能有助于诊断联接问题。
4. 确保定义了终结点，使其与 AG 使用的 IP/端口正确匹配。
5. 检查网络服务帐户是否拥有对终结点的 CONNECT 权限。
6. 检查是否存在潜在的名称解析问题
7. 请确保 SQL Server 运行的是最近的版本（最好是最新版本），以防止出现已修复的问题。

Read-Only 路由无法正常工作

1. 请确保你已按照配置只读路由文档中的内容设置只读路由。

2. 保证客户端驱动程序支持

   客户端应用程序必须使用支持 ApplicationIntent 参数的客户端提供程序。 请参阅可用性组的驱动程序和客户端连接支持

   注意

   如果要连接到分布式网络名称（DNN）侦听器，则提供程序还必须支持 MultiSubnetFailover 参数

3. 请务必正确设置连接字符串属性

   若要使只读路由正常工作，客户端应用程序必须在连接字符串中使用以下属性：

   • 属于 AG 的数据库名称
   • 可用性组监听器名称
     • 如果使用 DNN，则必须指定 DNN 侦听器名称和 DNN 端口号 <DNN name,DNN port>
   • ApplicationIntent 设置为只读
   • 使用分布式网络名称 (DNN) 时，需要将 MultiSubnetFailover 设置为 true。

   示例

   示例演示 .NET Microsoft.Data.SqlClient 或 System.Data.SqlClient 提供程序的连接字符串，以用于虚拟网络名称（VNN）侦听器：

   Server=tcp:VNN_AgListener,1433;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   此示例展示用于分布式网络名称（DNN）侦听器的 .NET Microsoft.Data.SqlClient 或 System.Data.SqlClient 提供程序的连接字符串：

   Server=tcp:DNN_AgListener,DNN_Port;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   注意

   如果使用 SQLCMD 等命令行程序，请确保为服务器名称指定正确的开关。 例如，在 SQLCMD 中，必须使用大写参数 -S 来指定服务器名称，而不是用于列分隔符的小写参数 -s。
   示例：sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. 确保可用性组侦听器处于联机状态。 为确保可用性组侦听器处于联机状态，请在主要副本上运行以下查询：

   SELECT * FROM sys.dm_tcp_listener_states;
   

   如果发现侦听器处于脱机状态，可以尝试使用以下命令使其联机：

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. 确保 READ_ONLY_ROUTING_LIST 被正确填充。 在主要副本上，确保 READ_ONLY_ROUTING_LIST 仅包含托管可读次要副本的服务器实例。

   若要查看每个副本的属性，可以运行此查询并检查只读副本的连接终结点（URL）。

   SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url 
   FROM sys.availability_replicas;   
   

   查看只读路由列表并与终结点 URL 进行比较：

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   若要更改只读路由列表，可以使用如下所示的查询：

   ALTER AVAILABILITY GROUP [AG1]   
   MODIFY REPLICA ON  
   N'COMPUTER02' WITH   
   (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
   

   有关详细信息，请参阅 为可用性组配置只读路由 - SQL Server Always On

6. 检查 READ_ONLY_ROUTING_URL 端口是否打开。 确保 Windows 防火墙未阻止READ_ONLY_ROUTING_URL端口。 为read_only_routing_list中的每个副本以及连接到这些副本的客户端配置 Windows 防火墙以访问数据库引擎。

   注意

   如果在 Azure VM 上运行 SQL Server，则必须执行其他配置步骤。 如果使用 DNN 侦听器，请确保每个副本 VM 的网络安全组 (NSG) 允许流量通过终结点端口和 DNN 端口。 如果使用 VNN 侦听器，则必须确保 负载均衡器配置正确。

7. 确保 READ_ONLY_ROUTING_URL (TCP://system-address:port) 包含正确的完全限定的域名 (FQDN) 和端口号。 请参阅：

   • 计算 AlwaysOn 的 read_only_routing_url
   • sys.availability_replicas（Transact-SQL）
   • 更改可用性组 (Transact-SQL)

8. 确保 SQL Server 配置管理器中的 SQL Server 网络配置正确。

   在 read_only_routing_list 中的每个副本上验证以下内容：

   • 已启用 SQL Server 远程连接
   • 已启用 TCP/IP
   • 已正确配置 IP 地址

   注意

   如果你可以使用 TCP:SQL_Instance 语法从远程计算机连接到目标次要副本的 SQL Server 实例名称，则可以快速验证所有这些配置是否正确。

请参阅：配置服务器以侦听特定 TCP 端口（SQL Server 配置管理器）和查看或更改服务器属性 (SQL Server)

相关任务

• 创建和配置可用性组 (SQL Server)

• 创建使用 Windows 身份验证的数据库镜像端点 (Transact-SQL)

• 在添加或修改可用性副本时指定端点 URL (SQL Server)

• 为可用性组手动准备辅助数据库 (SQL Server)

• 解决失败的添加文件操作问题（AlwaysOn 可用性组）

• 管理可用性组中数据库的用户登录和任务 (SQL Server)

• 在将数据库设为在其他服务器实例上可用时管理元数据 (SQL Server)

相关内容

• 查看故障转移群集的事件和日志
• Get-ClusterLog 故障转移群集命令
• SQL Server Always On 团队博客：SQL Server Always On 团队官方博客
• 针对数据库镜像和 Always On 可用性组的传输安全性 (SQL Server)
• 客户端网络配置
• Always On 可用性组的前提条件、限制和建议 (SQL Server)