Always On 可用性グループの構成のトラブルシューティング (SQL Server)

適用対象: はいSQL Server (サポートされているすべてのバージョン)

このトピックでは、サーバー インスタンスでの Always On 可用性グループの構成に関する一般的な問題のトラブルシューティングに役立つ情報を提供します。 構成に関する一般的な問題には、 Always On 可用性グループ が無効になっている、アカウントが適切に構成されていない、データベース ミラーリング エンドポイントが存在しない、エンドポイントにアクセスできない (SQL Server エラー 1418)、ネットワーク アクセスが存在しない、データベース参加コマンドが失敗する (SQL Server エラー 35250) などがあります。

注意

Always On 可用性グループ の前提条件を満たしていることを確認してください。 詳細については、「 Always On 可用性グループの前提条件、制限事項、および推奨事項 (SQL Server)の構成に関する一般的な問題のトラブルシューティングに役立つ情報を提供します。

このトピックの内容

Section 説明
Always On 可用性グループが有効になっていない SQL Server のインスタンスで Always On 可用性グループが有効になっていない場合、そのインスタンスでは可用性グループの作成がサポートされず、可用性レプリカをホストできません。
Accounts SQL Server を実行しているアカウントを適切に構成するための要件について説明します。
エンドポイント サーバー インスタンスのデータベース ミラーリング エンドポイントに関する問題の診断方法について説明します。
ネットワーク アクセス 可用性レプリカをホストしている各サーバー インスタンスが TCP で他の各サーバー インスタンスのポートにアクセスできる必要があるという要件について説明します。
エンドポイント アクセス (SQLServer エラー 1418) この SQL Server エラー メッセージに関する情報が含まれます。
データベースの参加の失敗 (SQL Server エラー 35250) プライマリ レプリカへの接続がアクティブでないためにセカンダリ データベースを可用性グループに参加させることができない問題について、考え得る原因と解決策について説明します。
読み取り専用ルーティングが正常に動作しない
関連タスク SQL Server オンライン ブックの中の、可用性グループ構成のトラブルシューティングに特に関連するタスク指向のトピックの一覧が含まれます。
関連コンテンツ SQL Server オンライン ブックの外部にある関連したリソースの一覧が含まれます。

Always On 可用性グループが有効になっていない

Always On 可用性グループ 機能は、 SQL Serverの各インスタンスで有効になっている必要があります。 詳細については、「Always On 可用性グループの有効化と無効化 (SQL Server)」を参照してください。

Accounts

SQL Server の実行に使用するアカウントは、正しく構成されている必要があります。

  1. アカウントに適切な権限が与えられていることを確認します。

    1. パートナーを同じドメイン アカウントで実行している場合は、両方の master データベースに正しいユーザー ログインが自動的に存在します。 この場合は、セキュリティ構成が単純になるため、望ましいといえます。

    2. 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)」を参照してください。

Endpoints

エンドポイントが正しく構成されている必要があります。

  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)」を参照してください。

  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 または Netbios) と比較します。 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 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 ポートが開いていることを確認します。 READ_ONLY_ROUTING_URL ポートが Windows ファイアウォールでブロックされていないことを確認します。 read_only_routing_list 内のすべてのレプリカと、それらのレプリカに接続される任意のクライアントへのデータベース エンジン アクセスに対して Windows ファイアウォールを構成します。

    注意

    Azure VM 上で SQL Server を実行している場合は、追加の構成手順を使用する必要があります。 DNN リスナーを使用している場合は、エンドポイント ポートおよび DNN ポートへのトラフィックが、各レプリカ VM のネットワーク セキュリティ グループ (NSG) によって許可されるようにします。 VNN リスナーを使用している場合は、ロード バランサーが正しく構成されていることを確認する必要があります。

  7. READ_ONLY_ROUTING_URL (TCP://system-address:port) に正しい完全修飾ドメイン名 (FQDN) とポート番号が含まれていることを確認します。 参照トピック

  8. SQL Server Configuration Manager で SQL Server ネットワーク構成が適切であることを確認します。

    read_only_routing_list にあるすべてのレプリカが次のようになっていることを確認します。

    • SQL Server のリモート接続が有効になっている
    • TCP/IP が有効になっている
    • IP アドレスが正しく構成されている

    注意

    TCP:SQL_Instance 構文を使用してリモート マシンからターゲット セカンダリ レプリカの SQL Server インスタンス名に接続できる場合、それらすべてが適切に構成されていることをすばやく確認できます。

特定の TCP ポートでリッスンするサーバーの構成 (SQL Server 構成マネージャー) およびサーバーのプロパティの表示または変更 (SQL Server) に関するページを参照してください。

参照

データベース ミラーリングと Always On 可用性グループのトランスポート セキュリティ (SQL Server)
クライアント ネットワーク構成
AlwaysOn 可用性グループの前提条件、制限事項、および推奨事項 (SQL Server)