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

適用於：SQL Server

本主題提供資訊以協助您針對設定 Always On 可用性群組的伺服器執行個體時常遇到的問題進行疑難排解。 一般設定問題包括 Always On 可用性群組未啟用、不正確地設定帳戶、資料庫鏡像端點不存在、端點無法存取 (SQL Server 錯誤 1418)、網路存取不存在，以及聯結資料庫命令失敗 (SQL Server 錯誤 35250)。

注意

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

本主題內容：

區段	描述
AlwaysOn 可用性群組未啟用	如果未針對 Always On 可用性群組啟用 SQL Server 執行個體，則執行個體不支援可用性群組建立，且無法裝載任何可用性複本。
帳戶	討論正確設定在底下執行 SQL Server 之帳戶的需求。
端點	討論如何診斷伺服器執行個體的資料庫鏡像端點問題。
網路存取	說明裝載可用性複本的每個伺服器執行個體必須要透過 TCP 存取其他伺服器執行個體之通訊埠的需求。
接聽程式	文件說明如何建立接聽程式的 IP 位址和連接埠，並確定其正在執行並接聽連入連線
端點存取 (SQL Server 錯誤 1418)	包含有關此 SQL Server 錯誤訊息的資訊。
聯結資料庫失敗 (SQL Server 錯誤 35250)	討論因為與主要複本的連接不在作用中，次要資料庫聯結至可用性群組失敗的可能原因和解決方案。
唯讀路由未正確運作
相關工作	在《SQL Server 線上叢書》中包含工作導向主題的清單，這些主題與疑難排解可用性群組組態相關。
相關內容	包含《SQL Server 線上叢書》外部的相關資源清單。

AlwaysOn 可用性群組未啟用

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. 如果已安裝 SQL Server，且在您安裝 Windows 容錯移轉叢集功能之前已啟用 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. 確定端點已啟動 (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)。

注意

若要針對可用性群組 (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 無法連線，請尋找可能封鎖有問題端點連接埠的防火牆和/或防毒軟體。 檢查防火牆設定是否允許裝載主要複本和次要複本的伺服器執行個體之間的端點連接埠通訊 (預設為連接埠 5022)。 執行下列 PowerShell 指令碼來檢查已停用的輸入流量規則

• 如果您在 Azure VM 上執行SQL Server，則還需要確保網路安全性群組 (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. 檢查系統中的錯誤。 您可以針對 last_connect_error_number 查詢 sys.dm_hadr_availability_replica_states，可能可以協助您診斷聯結問題。
4. 請確定端點已定義，使其正確符合 AG 所使用的 IP/連接埠。
5. 檢查網路服務帳戶是否具有端點的連線權限。
6. 檢查可能的名稱解析問題
7. 請確定您的 SQL Server 正在執行最近的組建 (最好是最新的組建)，以避免遇到已修正的問題。

唯讀路由未正確運作

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

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

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

   注意

   如果您要連線至分散式網路名稱 (DNN) 接聽程式，提供者也必須支援 MultiSubnetFailover 參數

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

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

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

   範例

   此範例說明虛擬網路名稱 (VNN) 接聽程式的 .NET System.Data.SqlClient 提供者連接字串：

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

   此說明分散式網路名稱 (DNN) 接聽程式的 .NET 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) 和連接埠編號。 請參閱：

   • Calculating read_only_routing_url for Always On
   • sys.availability_replicas (Transact-SQL)
   • ALTER AVAILABILITY GROUP (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)