Always On 可用性グループの読み取り専用ルーティングの構成

[アーティクル]
03/18/2023

適用対象: SQL Server

SQL Server で読み取り専用ルーティングをサポートするように AlwaysOn 可用性グループを構成するには、Transact-SQL または PowerShell を使用します。 読み取り専用ルーティング は、対象の読み取り専用接続要求を、AlwaysOn の SQL Server 読み取り可能なセカンダリレプリカ (セカンダリロールで実行されているときに、読み取り専用ワークロードを許可するように構成されているレプリカ) にルーティングするの機能です。読み取り専用ルーティングをサポートするには、可用性グループに可用性グループリスナーが存在する必要があります。読み取り専用クライアントは、このリスナーに接続要求を送信する必要があります。クライアントの接続文字列では、アプリケーションの目的として "読み取り専用" を指定する必要があります。つまり、読み取りを目的とした接続要求であることが必要です。

読み取り専用ルーティングは、SQL Server 2016 (13.x) 以降で使用できます。

注意

読み取り可能なセカンダリレプリカを構成する方法については、「可用性レプリカでの読み取り専用アクセスの構成 (SQL Server)」が存在する必要があります。

前提条件

可用性グループに可用性グループリスナーが存在している必要があります。詳細については、可用性グループのリスナーの作成および構成 (SQL Server) に関するページを参照してください。
1 つ以上の可用性レプリカを、セカンダリロールで読み取り専用を受け入れるように (つまり、読み取り可能なセカンダリレプリカになるように) 構成する必要があります。詳細については、可用性レプリカへの読み取り専用アクセスの構成 (SQL Server) に関するページを参照してください。
現在のプライマリレプリカをホストするサーバーインスタンスに接続されている必要があります。
SQL ログインを使用している場合は、アカウントが正しく構成されていることを確認します。詳しくは、「可用性グループのデータベースのためのログインとジョブの管理 (SQL Server)」をご覧ください。

読み取り専用ルーティングをサポートするために構成する必要があるレプリカのプロパティ

読み取り専用ルーティングをサポートしようとしている読み取り可能なセカンダリレプリカに対して、 読み取り専用ルーティングの URLを指定する必要があります。この URL は、ローカルレプリカがセカンダリロールで実行されている場合にのみ有効です。読み取り専用ルーティングの URL は、必要に応じてレプリカごとに指定する必要があります。各読み取り専用ルーティングの URL は、読み取りを目的とした接続要求を特定の読み取り可能なセカンダリレプリカにルーティングする際に使用されます。通常は、読み取り可能なすべてのセカンダリレプリカに読み取り専用ルーティングの URL が割り当てられます。

可用性レプリカの読み取り専用ルーティングの URL の計算の詳細については、「 AlwaysOn の read_only_routing_url の計算
可用性レプリカがプライマリレプリカである場合に読み取り専用ルーティングをサポートするには、その可用性レプリカに対して 読み取り専用ルーティングリストを指定する必要があります。読み取り専用ルーティングリストは、ローカルレプリカがプライマリロールで実行されている場合にのみ有効です。このリストは、必要に応じてレプリカごとに指定する必要があります。通常、各読み取り専用ルーティングリストには、すべての読み取り専用ルーティングの URL が含まれており、リストの末尾にローカルレプリカの URL が示されています。

Note

読み取りを目的とした接続要求は、現在のプライマリレプリカの読み取り専用ルーティングリスト内の最初に使用できるエントリにルーティングされます。ただし、読み取り専用レプリカ間の負荷分散がサポートされています。詳細については、「読み取り専用レプリカ間の負荷分散の構成」を参照してください。

Note

可用性グループリスナーと読み取り専用ルーティングの詳細については、「可用性グループリスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)」を参照してください。

アクセス許可

タスク	アクセス許可
可用性グループの作成時にレプリカを構成する	sysadmin 固定サーバーロールのメンバーシップと、CREATE AVAILABILITY GROUP サーバー権限、ALTER ANY AVAILABILITY GROUP 権限、CONTROL SERVER 権限のいずれかが必要です。
可用性レプリカを変更する	可用性グループの ALTER AVAILABILITY GROUP 権限、CONTROL AVAILABILITY GROUP 権限、ALTER ANY AVAILABILITY GROUP 権限、または CONTROL SERVER 権限が必要です。

Transact-SQL の使用

読み取り専用ルーティングリストの構成

Transact-SQL を使用して読み取り専用ルーティングを構成するには、次の手順を使用します。コード例については、このセクションの後半の「例 (Transact-SQL)」を参照してください。

プライマリレプリカをホストするサーバーインスタンスに接続します。
新しい可用性グループのレプリカを指定する場合は、CREATE AVAILABILITY GROUP Transact-SQL ステートメントを使用します。既存の可用性グループのレプリカを追加または変更する場合は、ALTER AVAILABILITY GROUP Transact-SQL ステートメントを使用します。
- セカンダリロールの読み取り専用ルーティングを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、SECONDARY_ROLE オプションを次のように指定します。
  
  SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')
  
  読み取り専用ルーティング URL のパラメーターは次のとおりです。
  
  system-address
  システム名、完全修飾ドメイン名では、対象のコンピューターシステムを明確に識別する、IP アドレスなどの文字列です。
  
  port
  SQL Server インスタンスのデータベースエンジンによって使用されるポート番号です。
  
  例: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')
  
  レプリカが既に読み取り専用接続を許可するように構成されている場合、MODIFY REPLICA 句の ALLOW_CONNECTIONS は省略可能です。
  
  詳細については、「 AlwaysOn の read_only_routing_url の計算」を参照してください。
- プライマリロールの読み取り専用ルーティングを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、PRIMARY_ROLE オプションを次のように指定します。
  
  PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('server' [ , ...n ] ))
  
  server は、可用性グループの読み取り専用セカンダリレプリカをホストするサーバーインスタンスを識別します。
  
  例: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))
  
  注意
  
  読み取り専用ルーティングリストを構成する前に、読み取り専用ルーティング URL を設定する必要があります。

読み取り専用レプリカ間の負荷分散の構成

SQL Server 2016 (13.x)より、読み取り専用レプリカのセット間に負荷分散を構成できるようになりました。以前の読み取り専用ルーティングでは、トラフィックは常にルーティングリストで最初に使用可能なレプリカに転送されていました。この機能を利用するには、 CREATE AVAILABILITY GROUP または ALTER AVAILABILITY GROUP コマンドで、 READ_ONLY_ROUTING_LIST サーバーインスタンスを囲む、1 レベルの入れ子になったかっこを使用します。

たとえば、次のルーティングリストを使用すると、 Server1 と Server2の 2 つの読み取り専用レプリカ間で読み取りを目的とした接続要求の負荷分散が行われます。これらのサーバーを囲む入れ子になったかっこは、負荷分散セットを識別します。このセット内のどちらのレプリカも使用できない場合は、読み取り専用ルーティングリストに含まれている他のレプリカ ( Server3 と Server4) に順に接続が試行されます。

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')

ルーティングリストのエントリには、負荷分散読み取り専用レプリカのセット自体を指定できます。次に例を示します。

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')

サポートされる入れ子になったかっこのレベルは、1 レベルのみです。

例 (Transact-SQL)

次の例では、既存の可用性グループ AG1 の 2 つの可用性レプリカを、これらのレプリカのいずれかが現在プライマリロールを所有している場合に読み取り専用ルーティングをサポートするように変更します。可用性レプリカをホストするサーバーインスタンスを識別するために、この例ではインスタンス名 COMPUTER01 および COMPUTER02 を指定します。

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO

PowerShell の使用

読み取り専用ルーティングリストの構成

PowerShell を使用して読み取り専用ルーティングを構成するには、次の手順を使用します。コード例については、このセクションの後半の「例 (PowerShell)」を参照してください。

既定 (cd) を、プライマリレプリカをホストするサーバーインスタンスに設定します。
可用性グループに可用性レプリカを追加する場合は、 New-SqlAvailabilityReplica コマンドレットを使用します。既存の可用性レプリカを変更する場合は、 Set-SqlAvailabilityReplica コマンドレットを使用します。関連するパラメーターは次のとおりです。
- セカンダリロールの読み取り専用ルーティングを構成するには、 ReadonlyRoutingConnectionUrl"url" パラメーターを指定します。
  
  url は、読み取り専用接続のためにレプリカにルーティングするときに使用する、接続の完全修飾ドメイン名 (FQDN) およびポートです。例: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"
  
  詳細については、「 AlwaysOn の read_only_routing_url の計算」を参照してください。
- プライマリロールの接続アクセスを構成するには、 ReadonlyRoutingList"server" [ , ...n ] を指定します。 server は、可用性グループの読み取り専用セカンダリレプリカをホストするサーバーインスタンスを識別します。例: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"
  
  注意
  
  レプリカの読み取り専用ルーティングリストを構成する前に、レプリカの読み取り専用ルーティング URL を設定する必要があります。
Note

コマンドレットの構文を表示するには、 PowerShell 環境で Get-Help SQL Server コマンドレットを使用します。詳細については、「 Get Help SQL Server PowerShell」を参照してください。

SQL Server PowerShell プロバイダーの設定および使用

例 (PowerShell)

次の例では、可用性グループ内のプライマリレプリカと 1 つのセカンダリレプリカを読み取り専用ルーティング用に構成します。まず、読み取り専用ルーティングの URL が各レプリカに割り当てられます。次に、プライマリレプリカで読み取り専用ルーティングリストを設定します。接続文字列で "ReadOnly" プロパティが設定された接続は、セカンダリレプリカにリダイレクトされます。 ( ConnectionModeInSecondaryRole 設定によって指定されたように) このセカンダリレプリカを読み取ることができない場合、接続はプライマリレプリカに戻されます。

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  
  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

補足情報: 読み取り専用ルーティングを構成した後

現在のプライマリレプリカと読み取り可能なセカンダリレプリカをそれぞれのロールで読み取り専用ルーティングをサポートするように設定すると、読み取り可能なセカンダリレプリカは、可用性グループリスナーを介して接続するクライアントからの読み取りを目的とした接続要求を受信できるようになります。

ヒント

bcp ユーティリティまたは sqlcmd ユーティリティを使用する場合、 -K ReadOnly スイッチを指定することによって、読み取り専用アクセスが有効になっている任意のセカンダリレプリカへの読み取り専用アクセスを指定できます。

クライアント接続文字列の要件および推奨事項

クライアントアプリケーションで読み取り専用ルーティングを使用するには、クライアントの接続文字列が次の要件を満たしている必要があります。

TCP プロトコルを使用する。
アプリケーションの目的の属性またはプロパティを読み取り専用に設定する。
読み取り専用ルーティングをサポートするように構成された可用性グループのリスナーを参照する。
その可用性グループ内のデータベースを参照する。

さらに、接続文字列でマルチサブネットフェールオーバーを有効にすることをお勧めします。マルチサブネットフェールオーバーは、各サブネット上の各レプリカについて並行クライアントスレッドをサポートします。これにより、フェールオーバー後のクライアント再接続時間が最小化されます。

接続文字列の構文は、アプリケーションが使用している SQL Server プロバイダーに依存します。 .NET Framework Data Provider 4.0.2 for SQL Server 用の次の接続文字列例は、読み取り専用ルーティング用に機能することが必須および推奨される接続文字列の一部を示しています。

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

読み取り専用のアプリケーションの目的と読み取り専用のルーティングの詳細については、「可用性グループリスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)」を参照してください。

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

読み取り専用ルーティング構成のトラブルシューティングの詳細については、「読み取り専用ルーティングが正常に動作しない」を参照してください。

次の手順

読み取り専用ルーティングの構成を表示するには

sys.availability_read_only_routing_lists (Transact-SQL)
sys.availability_replicas (Transact-SQL) (read_only_routing_url 列)

クライアント接続アクセスを構成するには

アプリケーションで接続文字列を使用するには

ブログ:

ホワイトペーパー:

SQL Server ユーザー諮問チームのホワイトペーパー

その他のコンテンツ

次の方法で共有

Always On 可用性グループの読み取り専用ルーティングの構成

前提条件

読み取り専用ルーティングをサポートするために構成する必要があるレプリカのプロパティ

アクセス許可

Transact-SQL の使用

読み取り専用ルーティングリストの構成

読み取り専用レプリカ間の負荷分散の構成

例 (Transact-SQL)

PowerShell の使用

読み取り専用ルーティングリストの構成

SQL Server PowerShell プロバイダーの設定および使用

例 (PowerShell)

補足情報: 読み取り専用ルーティングを構成した後

クライアント接続文字列の要件および推奨事項

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

次の手順

フィードバック

その他のリソース

次の方法で共有

Always On 可用性グループの読み取り専用ルーティングの構成

前提条件

読み取り専用ルーティングをサポートするために構成する必要があるレプリカのプロパティ

アクセス許可

Transact-SQL の使用

読み取り専用ルーティング リストの構成

読み取り専用レプリカ間の負荷分散の構成

例 (Transact-SQL)

PowerShell の使用

読み取り専用ルーティング リストの構成

SQL Server PowerShell プロバイダーの設定および使用

例 (PowerShell)

補足情報: 読み取り専用ルーティングを構成した後

クライアント接続文字列の要件および推奨事項

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

次の手順

フィードバック

その他のリソース

読み取り専用ルーティングリストの構成

読み取り専用ルーティングリストの構成