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

適用範圍: 是SQL Server (所有支援的版本)

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

注意

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

本主題內容:

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

AlwaysOn 可用性群組未啟用

Always On 可用性群組 功能必須在每個 SQL Server執行個體上啟用。 如需詳細資訊,請參閱啟用和停用 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)

Endpoints

必須正確設定端點。

  1. 確定將要裝載可用性複本的每個 SQL Server 執行個體 (每個「複本位置」),都擁有資料庫鏡像端點。 若要判斷給定的伺服器實例上是否有資料庫鏡像端點,請使用 sys.database_mirroring_endpoints 目錄檢視:

    SELECT name, state_desc FROM sys.database_mirroring_endpoints  
    

    如需有關建立端點的詳細資訊,請參閱Windows 驗證建立資料庫鏡像端點 (SQL)允許資料庫鏡像端點使用憑證進行輸出連接 (transact-sql 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)

  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) 的伺服器名稱進行比較。 若要查詢 Netbios 和 FQDN,請在本機複本的命令提示字元中執行下列命令:

    hostname & echo %COMPUTERNAME%.%USERDNSDOMAIN%
    

    若要查詢遠端電腦的伺服器名稱,請從命令提示字元執行此命令。 然後比較 endpoint_url

    ping -a servername_from_endpoint_url
    

    如需詳細資訊,請參閱 在加入或修改可用性複本時指定端點 URL (SQL Server)設定伺服器執行個體時常見的問題。

Network Access

裝載可用性複本的每個伺服器執行個體必須要透過 TCP 存取其他伺服器執行個體之通訊埠。 如果伺服器執行個體位在互不信任的不同網域 (不受信任的網域) 中,這點尤其重要。 遵循下列步驟來檢查您是否可以連線到端點:

  • 使用 Telnet 來驗證連線能力。 以下是您可以使用的命令範例:

    telnet ServerName Port
    telnet IP_Address Port
    
    
  • If the Endpoint is listening and connection is successful, then you will see a blank screen. If not, you will receive a connection error from Telnet

  • If Telnet connection to the IP address works but to the ServerName it does not, there is likely a DNS or name resolution issue

  • If connection works by ServerName and not by IP address, then there could be more than one endpoint defined on that server (another SQL instance perhaps) that is listening on that port. Though the status of the endpoint on the instance in question shows "STARTED" another instance may actually have the port binding and prevent the correct instance from listening and establishing TCP connections.

  • If Telnet fails to connect, look for Firewall and/or Anti-virus software that may be blocking the endpoint port in question. Check the firewall setting to see if it allows the endpoint port communication between the server instances that host primary replica and the secondary replica (port 5022 by default). Run the following PowerShell script to examine for disabled inbound traffic rules

  • If Telnet fails to connect, look for Firewall and/or antivirus software that may be blocking the endpoint port in question. If you are running SQL Server on Azure VM, additionally you would need to ensure Network Security Group (NSG) allows the traffic to endpoint port. Check the firewall (and NSG, for Azure VM) setting to see if it allows the endpoint port communication between the server instances that host primary replica and the secondary replica (port 5022 by default)

    Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
    
    
  • Capture a NETSTAT -a output and verify the status is a LISTENING or ESTABLISHED on the IP:Port for the endpoint specified

    netstat -a
    
    
    

Endpoint Access (SQL Server Error 1418)

This 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. This is the full error message:

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

Resolution:

Summary of steps is outlined below.

For detailed step-by-step instructions, please refer to Engine error MSSQLSERVER_35250

  1. Ensure the endpoint is created and started.
  2. Check if you can connect to the endpoint via Telnet and ensure no firewall rules are blocking connectivity
  3. Check for errors in the system. You can query the sys.dm_hadr_availability_replica_states for the last_connect_error_number that may help you diagnose the join issue.
  4. Ensure the endpoint is defined so it correctly matches the IP/port that AG is using.
  5. Check whether the network service account has CONNECT permission to the endpoint.
  6. Check for possible name resolution issues
  7. Ensure your SQL Server is running a recent build (preferably the latest build to protect from running into fixed issues.

Read-Only Routing is Not Working Correctly

  1. Ensure that you have set up read-only routing by following Configure read-only routing document.

  2. Ensure Client Driver Support

    The client application must use a client providers that support ApplicationIntent parameter. See Driver and client connectivity support for availability groups

    注意

    If you are connecting to a distributed network name (DNN) Listener, the provider must also support MultiSubnetFailover parameter

  3. Ensure connection string properties are set correctly

    For read-only routing to work properly, your client application must use these properties in the connection string:

    • A database name that belongs to the AG
    • An availability group listener name
      • If you are using DNN, you must specify DNN listener name and DNN port number <DNN name,DNN port>
    • ApplicationIntent set to ReadOnly
    • MultiSubnetFailover set to true is required for Distributed network name (DNN)

    Examples

    This illustrates the connection string for .NET System.Data.SqlClient provider for a virtual network name (VNN) listener:

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

    這會說明 (DNN) 接聽程式的分散式網路名稱之 .NET 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 中的每個複本上設定 database engine 存取 Windows 防火牆,以及將連接到這些複本的用戶端設定。

    注意

    如果您是 SQL Server 在 AZURE VM 上執行,您必須採取額外的設定步驟。 如果您使用 DNN 接聽程式,請確定每個複本 VM (NSG) 的網路安全性群組允許流量流向端點埠和 DNN 埠。 如果您使用 VNN 接聽程式,您必須確定 已正確設定負載平衡器

  7. 確定 READ_ONLY_ROUTING_URL (TCP://system-address:port) 包含正確的完整網域名稱 (FQDN) 和通訊埠編號。 請參閱:

  8. 確定設定管理員中有適當的 SQL Server 網路功能設定 SQL Server 。

    確認 read_only_routing_list 中的每個複本:

    • SQL Server 遠端連線已啟用
    • TCP/IP 已啟用
    • IP 位址已正確設定

    注意

    如果您可以使用語法從遠端電腦連接到目標次要複本的實例名稱,您可以快速確認所有這些都已正確設定 SQL Server TCP:SQL_Instance

請參閱:將伺服器設定為在特定 TCP 通訊埠上接聽 (SQL Server 組態管理員) 以及查看或變更伺服器屬性 (SQL Server)

另請參閱

資料庫鏡像和 Always On 可用性群組的傳輸安全性 (SQL Server)
用戶端網路組態
AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)