疑難排解 AlwaysOn 可用性群組組態 (SQL Server)

適用於：SQL Server

本文提供資訊，幫助你排除設定 Always On 可用性群組伺服器實例時常見的問題。 一般設定問題包括 Always On 可用性群組未啟用、不正確地設定帳戶、資料庫鏡像端點不存在、端點無法存取 (SQL Server 錯誤 1418)、網路存取不存在，以及聯結資料庫命令失敗 (SQL Server 錯誤 35250)。

注意

確保你符合 Always On 群組的可用性要求。 如需詳細資訊，請參閱 Always On 可用性群組的必要條件、限制和建議 (SQL Server)。

本主題內容：

區段	描述
AlwaysOn 可用性群組未啟用	如果 SQL Server 實例未啟用 Always On 可用性群組，該實例不支援建立可用性群組，也無法承載任何可用性副本。
帳戶	討論設定 SQL Server 執行帳戶的正確要求。
端點	討論如何診斷伺服器執行個體的資料庫鏡像端點問題。
網路存取	文件說明每個承載可用性副本的伺服器實例必須能透過 TCP 存取其他伺服器實例的埠口。
接聽程式	說明如何建立監聽器的 IP 位址和埠，並確保它正在運行並監聽傳入的連線。
端點存取 (SQL Server 錯誤 1418)	包含有關此 SQL Server 錯誤訊息的資訊。
聯結資料庫失敗 (SQL Server 錯誤 35250)	討論因連接至主要複本的連線未啟動，導致次要資料庫無法加入可用性群組的可能原因和解決方案。
Read-Only 路由無法正常運作
相關工作	包含與故障排除可用性群組組別相關的 SQL Server Books Online 任務導向文章清單。
相關內容	包含《SQL Server 線上叢書》外部的相關資源清單。

Always On 可用性群組未啟用

Always On 可用性群組功能必須在每個 SQL Server 執行個體上啟用。

如果未啟用 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，並且啟用了 Always On 可用性群組功能，當你嘗試建立 Always On AG 時，可能會遇到這個錯誤。
2. 如果您移除現有的 Windows 容錯移轉叢集功能，並在 SQL Server 仍設定 Always On 時重建，則在您嘗試再次使用 AG 時，便可能會發生此錯誤。

在這種情況下，您可以採取下列步驟來解決此問題：

1. 停用 AG 功能
2. 重新啟動 SQL Server 服務
3. 重新啟用 AG 功能
4. 重新啟動 SQL 服務

如需詳細資訊，請參閱啟用和停用 AlwaysOn 可用性群組 (SQL Server)。

帳戶

必須正確設定用來執行 SQL Server 的帳戶。

1. 帳戶有正確的權限嗎？

   1. 如果夥伴是以相同的網域帳戶身分執行，則兩個 master 資料庫中會自動存在正確的使用者登入。 如此可簡化安全性設定，建議您使用。

   2. 如果兩個伺服器執行個體是以不同的帳戶身分執行，則必須在遠端伺服器執行個體的 master 中建立每一個帳戶的登入，且該伺服器主體必須被授與 CONNECT 權限，才能連接到該伺服器執行個體的資料庫鏡像端點。 如需詳細資訊，請參閱 設定資料庫鏡像或 AlwaysOn 可用性群組的登入帳戶 (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 在內建帳戶下執行 (例如本機系統、本機服務或網路服務，或是非網域帳戶)，您必須將憑證用於端點驗證。 如果服務帳戶使用同一個網域中的網域帳戶，您可以選擇在所有複本位置上授與每一個服務帳戶的 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. 對於難以解釋的 AlwaysOn 可用性群組設定問題，我們建議您檢查每個伺服器執行個體，以判斷它是否正在正確的連接埠上接聽。

4. 請確保這些端點已經啟動（狀態=啟動）。 在每個伺服器執行個體上，使用下列 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)。

注意

若要針對可用性群組 (AG) 端點之間的通訊使用 Kerberos 驗證，請為 AG 所使用的資料庫鏡像端點註冊 Kerberos 連線的服務主體名稱。

網路存取

每個承載可用性副本的伺服器實例，必須能透過 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 無法連線，請尋找防火牆或防病毒軟體，可能正在阻擋該端點埠。如果 Test-NetConnection 無法連線，請尋找防火牆或防病毒軟體，可能正在阻擋該端點埠。 檢查防火牆設定是否允許裝載主要複本和次要複本的伺服器執行個體之間的端點連接埠通訊 (預設為連接埠 5022)。 執行下列 PowerShell 指令碼來檢查已停用的輸入流量規則

• 如果你在 Azure VM 上執行 SQL Server，還需要 確保網路安全群組（Network Security Group，NSG）允許流量傳送到端點埠。 檢查防火牆設定（以及在 Azure VM 上檢查 NSG）以確保其允許裝載主要副本和次要副本的伺服器實例之間通過端點埠進行通訊（預設為埠 5022）

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

• 從 Get-NetTCPConnection cmdlet 擷取輸出（等效於 NETSTAT -a），並確認指定端點的 IP:Port 狀態為 LISTENING 或 ESTABLISHED

  Get-NetTCPConnection 
  

接聽程式

要正確設定可用性群組監聽器，請依照「為 Always On 可用性群組配置監聽器」

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. 檢查網路服務帳戶是否具有端點的連線權限。
6. 檢查可能的名稱解析問題
7. 請確定您的 SQL Server 正在執行最近的組建 (最好是最新的組建)，以避免遇到已修正的問題。

Read-Only 路由不正常

1. 遵循設定唯讀路由文件，確定您已設定唯讀路由。

2. 確定用戶端驅動程式支援

   用戶端應用程式必須使用支援 ApplicationIntent 參數的用戶端提供者。 請參閱可用性群組的驅動程式和用戶端連線支援

   注意

   如果您連接到分散式網路名稱（DNN）監聽器，提供者也必須支援MultiSubnetFailover參數。

3. 確定已正確設定連接字串屬性

   若要讓唯讀路由正常運作，用戶端應用程式必須在連接字串中使用這些屬性：

   • 屬於 AG 的資料庫名稱
   • 可用性群組接聽程式名稱
     • 如果您使用 DNN，則必須指定 DNN 接聽程式名稱和 DNN 連接埠號碼 <DNN name,DNN port>
   • ApplicationIntent 設為唯讀
   • 分散式網路名稱 (DNN) 需要 MultiSubnetFailover 設定為 true

   範例

   此範例說明了虛擬網路名稱（VNN）監聽器 .NET Microsoft.Data.SqlClient 或 System.Data.SqlClient 提供者的連線字串：

   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，必須採取額外的設定步驟。 確保每個副本虛擬機的網路安全群組（NSG）允許流量流向端點埠和 DNN 埠（如果你使用 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 小組部落格
• 資料庫鏡像和 AlwaysOn 可用性群組的傳輸安全性 (SQL Server)
• 用戶端網路組態
• AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)