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

[アーティクル]
04/26/2023

適用対象: SQL Server

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

Note

Always On 可用性グループの前提条件を満たしていることを確認してください。詳細については、「Always On 可用性グループの前提条件、制限事項、および推奨事項 (SQL Server)」を参照してください。

このトピックの内容

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

Windows フェールオーバークラスタリング機能をインストールする前に、SQL Server がインストールされ、Always On 可用性グループ機能が有効になっていた場合は、Always On AG を作成しようとすると、このエラーが表示される可能性があります。
既存の Windows フェールオーバークラスタリング機能を削除し、SQL Server でまだ Always On が構成済みのときにそれを再構築すると、AG をもう一度使用しようとしたときにこのエラーが発生する可能性があります。

このような場合は、次の手順を行って解決できます。

AG 機能を無効にする
SQL Server サービスを再起動する
AG 機能を有効に戻す
もう一度 SQL サービスを再起動する

詳細については、「Always On 可用性グループの有効化と無効化 (SQL Server)」を参照してください。

アカウント

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

アカウントに適切な権限が与えられていることを確認します。
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    
```
SQL Server がビルトインアカウント (Local System、Local Service、Network Service など) で実行されている場合、または非ドメインアカウントで実行されている場合は、エンドポイント認証に証明書を使用する必要があります。サービスアカウントで同じドメインのドメインアカウントを使用している場合は、すべてのレプリカの場所の各サービスアカウントに対して CONNECT アクセスを付与するか、証明書を使用できます。詳しくは、「データベースミラーリングエンドポイントでの証明書の使用 (Transact-SQL)」をご覧ください。

エンドポイント

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

可用性レプリカ (各 SQL Server レプリカの場所 ) をホストするの各インスタンスにデータベースミラーリングエンドポイントがあることを確認します。データベースミラーリングエンドポイントが特定のサーバーインスタンスに存在するかどうかを確認するには、sys.database_mirroring_endpoints カタログビューを使用します。
```
SELECT name, state_desc FROM sys.database_mirroring_endpoints  
```
エンドポイントの作成に関する詳細については、「Windows 認証でのデータベースミラーリングエンドポイントの作成 (Transact-SQL)」または「データベースミラーリングエンドポイントで発信接続に証明書を使用できるようにする (Transact-SQL)」を参照してください。
ポート番号が適切であることを確認します。

サーバーインスタンスのデータベースミラーリングエンドポイントに現在関連付けられているポートを識別するには、次の Transact-SQL ステートメントを使用します。
```
SELECT type_desc, port FROM sys.tcp_endpoints;  
GO  
```
説明が困難な Always On 可用性グループのセットアップに関する問題については、各サーバーインスタンスを調査して、それぞれが正しいポートでリッスンしているかどうかを確認することをお勧めします。
エンドポイントが開始されていること (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 を指定します

他のサーバーからのログインに、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;

エンドポイント 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 または名前解決の問題が発生している可能性があります
IP アドレスではなく ServerName で接続が機能する場合、そのポートでリッスンしているサーバー (おそらく別の 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:Port の状態が LISTENING または ESTABLISHED であることを確認します
```
Get-NetTCPConnection 
```

リスナー

可用性グループリスナーの正しい構成については、「Always On 可用性グループのリスナーの構成」に従います

リスナーを構成し終えたら、次のクエリを使用して、リッスンしている 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"

次のクエリを使用して、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")

リスナーへの接続を確立する必要があり、ポートがブロックされている可能性がある場合は、PowerShell Test-NetConnection コマンドレット (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
```
最後に、指定したポート上でリスナーがリッスンしているかどうかを次のように検査します。
```
$port_number = "your_port_number"

Get-NetTCPConnection -LocalPort $port_number -State Listen
```

エンドポイントアクセス (SQLServer エラー 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 を参照してください

エンドポイントが作成され、開始されていることを確認します。
Telnet 経由でエンドポイントに接続できるかどうかを確認し、ファイアウォール規則によって接続がブロックされていないことを確認します。
システム内のエラーを確認します。 last_connect_error_number に対して sys.dm_hadr_availability_replica_states のクエリを実行できます。これは、結合の問題の診断に役立ちます。
AG で使用されている IP/ポートと正しく一致するようにエンドポイントが定義されていることを確認します。
ネットワークサービスアカウントにエンドポイントへの CONNECT 権限があるかどうかを確認します。
名前解決の問題の可能性がないかどうかを確認します
SQL Server で最新のビルドが実行されていることを確認します (修正した問題の発生を防ぐには、最新のビルドが推奨されます)。

読み取り専用ルーティングが正常に動作しない

読み取り専用ルーティングの構成に関するドキュメントに従って、読み取り専用ルーティングを設定していることを確認します。
クライアントドライバーのサポートを確認します

クライアントアプリケーションでは、ApplicationIntent パラメーターがサポートされるクライアントプロバイダーを使用する必要があります。「可用性グループのドライバーとクライアント接続のサポート」を参照してください。

Note

分散ネットワーク名 (DNN) リスナーに接続する場合、プロバイダーで MultiSubnetFailover パラメーターもサポートされる必要があります。
接続文字列のプロパティが正しく設定されていることを確認します

読み取り専用ルーティングを正しく機能させるには、クライアントアプリケーションの接続文字列で次のプロパティを使用する必要があります。
- AG に属するデータベース名
- 可用性グループリスナーの名前
  - DNN を使用している場合は、DNN リスナー名と DNN ポート番号 <DNN name,DNN port> を指定する必要があります
- ReadOnly に設定された ApplicationIntent
- true に設定された MultiSubnetFailover (分散ネットワーク名 (DNN) に必要)
例

この例は、仮想ネットワーク名 (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
```
Note

SQLCMD などのコマンドラインプログラムを使用している場合は、サーバー名に正しいスイッチを指定するようにしてください。たとえば、SQLCMD では、列区切り記号として使用される小文字の -s スイッチではなく、サーバー名を指定する大文字の -S スイッチを使用する必要があります。
例: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M
可用性グループリスナーがオンラインであることを確認します。可用性グループリスナーがオンラインになっているようにするには、プライマリレプリカに対して次のクエリを実行します。
```
SELECT * FROM sys.dm_tcp_listener_states;
```
リスナーがオフラインになっている場合は、次のようなコマンドを使用してオンラインにすることを試みることができます。
```
ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
```
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) に関する記事を参照してください
READ_ONLY_ROUTING_URL ポートが開いていることを確認します。 READ_ONLY_ROUTING_URL ポートが Windows ファイアウォールでブロックされていないことを確認します。 read_only_routing_list 内のすべてのレプリカと、それらのレプリカに接続される任意のクライアントへのデータベースエンジンアクセスに対して Windows ファイアウォールを構成します。

Note

Azure VM 上で SQL Server を実行している場合は、追加の構成手順を使用する必要があります。 DNN リスナーを使用している場合は、エンドポイントポートおよび DNN ポートへのトラフィックが、各レプリカ VM のネットワークセキュリティグループ (NSG) によって許可されるようにします。 VNN リスナーを使用している場合は、ロードバランサーが正しく構成されていることを確認する必要があります。
READ_ONLY_ROUTING_URL (TCP://system-address:port) に正しい完全修飾ドメイン名 (FQDN) とポート番号が含まれていることを確認します。参照トピック
SQL Server Configuration Manager で SQL Server ネットワーク構成が適切であることを確認します。

read_only_routing_list にあるすべてのレプリカが次のようになっていることを確認します。
- SQL Server のリモート接続が有効になっている
- TCP/IP が有効になっている
- IP アドレスが正しく構成されている
Note

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

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

Related Tasks

参照

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

次の方法で共有

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

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

アカウント

エンドポイント

ネットワークアクセス

リスナー

エンドポイントアクセス (SQLServer エラー 1418)

データベースの参加の失敗 (SQL Server エラー 35250)

読み取り専用ルーティングが正常に動作しない

例

Related Tasks

関連コンテンツ

参照

フィードバック

その他のリソース

次の方法で共有

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

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

アカウント

エンドポイント

ネットワーク アクセス

リスナー

エンドポイント アクセス (SQLServer エラー 1418)

データベースの参加の失敗 (SQL Server エラー 35250)

読み取り専用ルーティングが正常に動作しない

例

Related Tasks

関連コンテンツ

参照

フィードバック

その他のリソース

ネットワークアクセス

エンドポイントアクセス (SQLServer エラー 1418)