Always On 可用性グループの読み取り専用ルーティングの構成Configure read-only routing for an Always On availability group

適用対象: ○SQL Server XAzure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Serverで読み取り専用ルーティングをサポートするように AlwaysOn 可用性グループを構成するには、 Transact-SQLTransact-SQL または PowerShell を使用します。To configure an Always On availability group to support read-only routing in SQL ServerSQL Server, you can use either Transact-SQLTransact-SQL or PowerShell. 読み取り専用ルーティング は、対象の読み取り専用接続要求を、AlwaysOn の SQL ServerSQL Server 読み取り可能なセカンダリ レプリカ (セカンダリ ロールで実行されているときに、読み取り専用ワークロードを許可するように構成されているレプリカ) にルーティングする の機能です。Read-only routing refers to the ability of SQL ServerSQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). 読み取り専用ルーティングをサポートするには、可用性グループに 可用性グループ リスナーが存在する必要があります。To support read-only routing, the availability group must possess an availability group listener. 読み取り専用クライアントは、このリスナーに接続要求を送信する必要があります。クライアントの接続文字列では、アプリケーションの目的として "読み取り専用" を指定する必要があります。Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." つまり、 読み取りを目的とした接続要求であることが必要です。That is, they must be read-intent connection requests.

読み取り専用ルーティングは、SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降で使用できます。Read-only routing is available in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

注意

読み取り可能なセカンダリ レプリカを構成する方法については、「 可用性レプリカでの読み取り専用アクセスの構成 (SQL Server)が存在する必要があります。For information about how to configure a readable secondary replica, see Configure Read-Only Access on an Availability Replica (SQL Server).

前提条件Prerequisites

読み取り専用ルーティングをサポートするために構成する必要があるレプリカのプロパティWhat Replica Properties Do you Need to Configure to Support Read-Only Routing?

  • 読み取り専用ルーティングをサポートしようとしている読み取り可能なセカンダリ レプリカに対して、 読み取り専用ルーティングの URLを指定する必要があります。For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. この URL は、ローカル レプリカがセカンダリ ロールで実行されている場合にのみ有効です。This URL takes effect only when the local replica is running under the secondary role. 読み取り専用ルーティングの URL は、必要に応じてレプリカごとに指定する必要があります。The read-only routing URL must be specified on a replica-by-replica basis, as needed. 各読み取り専用ルーティングの URL は、読み取りを目的とした接続要求を特定の読み取り可能なセカンダリ レプリカにルーティングする際に使用されます。Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. 通常は、読み取り可能なすべてのセカンダリ レプリカに読み取り専用ルーティングの URL が割り当てられます。Typically, every readable secondary replica is assigned a read-only routing URL.

    可用性レプリカの読み取り専用ルーティングの URL の計算の詳細については、「 AlwaysOn の read_only_routing_url の計算For information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for Always On

  • 可用性レプリカがプライマリ レプリカである場合に読み取り専用ルーティングをサポートするには、その可用性レプリカに対して 読み取り専用ルーティング リストを指定する必要があります。For each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. 読み取り専用ルーティング リストは、ローカル レプリカがプライマリ ロールで実行されている場合にのみ有効です。A given read-only routing list takes effect only when the local replica is running under the primary role. このリストは、必要に応じてレプリカごとに指定する必要があります。This list must be specified on a replica-by-replica basis, as needed. 通常、各読み取り専用ルーティング リストには、すべての読み取り専用ルーティングの URL が含まれており、リストの末尾にローカル レプリカの URL が示されています。Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

    注意

    読み取りを目的とした接続要求は、現在のプライマリ レプリカの読み取り専用ルーティング リスト内の最初に使用できるエントリにルーティングされます。Read-intent connection requests are routed to the first available entry on the read-only routing list of the current primary replica. ただし、読み取り専用レプリカ間の負荷分散がサポートされています。However, load-balancing across read-only replicas is supported. 詳細については、「 読み取り専用レプリカ間の負荷分散の構成」を参照してください。For more information, see Configure load-balancing across read-only replicas.

注意

可用性グループ リスナーと読み取り専用ルーティングの詳細については、「 可用性グループ リスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)が存在する必要があります。For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

PermissionsPermissions

タスクTask アクセス許可Permissions
可用性グループの作成時にレプリカを構成するTo configure replicas when creating an availability group sysadmin 固定サーバー ロールのメンバーシップと、CREATE AVAILABILITY GROUP サーバー権限、ALTER ANY AVAILABILITY GROUP 権限、CONTROL SERVER 権限のいずれかが必要です。Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
可用性レプリカを変更するTo modify an availability replica 可用性グループの ALTER AVAILABILITY GROUP 権限、CONTROL AVAILABILITY GROUP 権限、ALTER ANY AVAILABILITY GROUP 権限、または CONTROL SERVER 権限が必要です。Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Transact-SQL の使用Using Transact-SQL

読み取り専用ルーティング リストの構成Configure a read-only routing list

Transact-SQL を使用して読み取り専用ルーティングを構成するには、次の手順を使用します。Use the following steps to configure read-only routing using Transact-SQL. コード例については、このセクションの後半の「 例 (Transact-SQL)」を参照してください。For a code example, see Example (Transact-SQL), later in this section.

  1. プライマリ レプリカをホストするサーバー インスタンスに接続します。Connect to the server instance that hosts the primary replica.

  2. 新しい可用性グループのレプリカを指定する場合は、 CREATE AVAILABILITY GROUPTransact-SQLTransact-SQL ステートメントを使用します。If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUPTransact-SQLTransact-SQL statement. 既存の可用性グループのレプリカを追加または変更する場合は、 ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL ステートメントを使用します。If you are adding or modifying a replica for an existing availability group, use the ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.

    • セカンダリ ロールの読み取り専用ルーティングを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、SECONDARY_ROLE オプションを次のように指定します。To configure read-only routing for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL =' TCP :// system-address : port ')SECONDARY_ROLE ( READ_ONLY_ROUTING_URL =' TCP ://system-address:port')

      読み取り専用ルーティング URL のパラメーターは次のとおりです。The parameters of the read-only routing URL are as follows:

      system-addresssystem-address
      システム名、完全修飾ドメイン名では、対象のコンピューター システムを明確に識別する、IP アドレスなどの文字列です。Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

      portport
      SQL ServerSQL Server インスタンスのデータベース エンジンによって使用されるポート番号です。Is a port number that is used by the Database Engine of the SQL ServerSQL Server instance.

      例: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')For example: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      レプリカが既に読み取り専用接続を許可するように構成されている場合、MODIFY REPLICA 句の ALLOW_CONNECTIONS は省略可能です。In a MODIFY REPLICA clause the ALLOW_CONNECTIONS is optional if the replica is already configured to allow read-only connections.

      詳細については、「 AlwaysOn の read_only_routing_url の計算」を参照してください。For more information, see Calculating read_only_routing_url for Always On.

    • プライマリ ロールの読み取り専用ルーティングを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、PRIMARY_ROLE オプションを次のように指定します。To configure read-only routing for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

      PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(' server ' [ , ...n ] ))PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('server' [ ,...n ] ))

      server は、可用性グループの読み取り専用セカンダリ レプリカをホストするサーバー インスタンスを識別します。where, server identifies a server instance that hosts a read-only secondary replica in the availability group.

      例: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))For example: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      注意

      読み取り専用ルーティング リストを構成する前に、読み取り専用ルーティング URL を設定する必要があります。You must set the read-only routing URL before configuring the read-only routing list.

読み取り専用レプリカ間の負荷分散の構成Configure load-balancing across read-only replicas

SQL Server 2016 (13.x)SQL Server 2016 (13.x)より、読み取り専用レプリカのセット間に負荷分散を構成できるようになりました。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can configure load-balancing across a set of read-only replicas. 以前の読み取り専用ルーティングでは、トラフィックは常にルーティング リストで最初に使用可能なレプリカに転送されていました。Previously, read-only routing always directed traffic to the first available replica in the routing list. この機能を利用するには、 CREATE AVAILABILITY GROUP または ALTER AVAILABILITY GROUP コマンドで、 READ_ONLY_ROUTING_LIST サーバー インスタンスを囲む、1 レベルの入れ子になったかっこを使用します。To take advantage of this feature, use one level of nested parentheses around the READ_ONLY_ROUTING_LIST server instances in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP commands.

たとえば、次のルーティング リストを使用すると、 Server1Server2の 2 つの読み取り専用レプリカ間で読み取りを目的とした接続要求の負荷分散が行われます。For example, the following routing list load balances read-intent connection request across two read-only replicas, Server1 and Server2. これらのサーバーを囲む入れ子になったかっこは、負荷分散セットを識別します。The nested parentheses that surround these servers identify the load-balanced set. このセット内のどちらのレプリカも使用できない場合は、読み取り専用ルーティング リストに含まれている他のレプリカ ( Server3Server4) に順に接続が試行されます。If neither replica is available in that set, it will proceed to attempt to sequentially connect to the other replicas, Server3 and Server4, in the read-only routing list.

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

ルーティング リストのエントリには、負荷分散読み取り専用レプリカのセット自体を指定できます。Note that each entry in the routing list can itself be a set of load-balanced read-only replicas. 例を次に示します。The following example demonstrates this.

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

サポートされる入れ子になったかっこのレベルは、1 レベルのみです。Only one level of nested parentheses is supported.

例 (Transact-SQL)Example (Transact-SQL)

次の例では、既存の可用性グループ AG1 の 2 つの可用性レプリカを、これらのレプリカのいずれかが現在プライマリ ロールを所有している場合に読み取り専用ルーティングをサポートするように変更します。The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. 可用性レプリカをホストするサーバー インスタンスを識別するために、この例ではインスタンス名 COMPUTER01 および COMPUTER02 を指定します。To identify the server instances that host the availability replica, this example specifies the instance names-COMPUTER01 and 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 の使用Using PowerShell

読み取り専用ルーティング リストの構成Configure a read-only routing list

PowerShell を使用して読み取り専用ルーティングを構成するには、次の手順を使用します。Use the following steps to configure read-only routing using PowerShell. コード例については、このセクションの後半の「 例 (PowerShell)」を参照してください。For a code example, see Example (PowerShell), later in this section.

  1. 既定 (cd) を、プライマリ レプリカをホストするサーバー インスタンスに設定します。Set default (cd) to the server instance that hosts the primary replica.

  2. 可用性グループに可用性レプリカを追加する場合は、 New-SqlAvailabilityReplica コマンドレットを使用します。When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. 既存の可用性レプリカを変更する場合は、 Set-SqlAvailabilityReplica コマンドレットを使用します。When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. 関連するパラメーターは次のとおりです。The relevant parameters are as follows:

    • セカンダリ ロールの読み取り専用ルーティングを構成するには、 ReadonlyRoutingConnectionUrl" url " パラメーターを指定します。To configure read-only routing for the secondary role, specify the ReadonlyRoutingConnectionUrl"url" parameter.

      url は、読み取り専用接続のためにレプリカにルーティングするときに使用する、接続の完全修飾ドメイン名 (FQDN) およびポートです。where, url is the connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections. 例: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"For example: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      詳細については、「 AlwaysOn の read_only_routing_url の計算」を参照してください。For more information, see Calculating read_only_routing_url for Always On.

    • プライマリ ロールの接続アクセスを構成するには、 ReadonlyRoutingList" server " [ , ...n ] を指定します。 server は、可用性グループの読み取り専用セカンダリ レプリカをホストするサーバー インスタンスを識別します。To configure connection access for the primary role, specify ReadonlyRoutingList"server" [ ,...n ], where server identifies a server instance that hosts a read-only secondary replica in the availability group. 例: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"For example: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      注意

      レプリカの読み取り専用ルーティング リストを構成する前に、レプリカの読み取り専用ルーティング URL を設定する必要があります。You must set the read-only routing URL of a replica before configuring its read-only routing list.

    注意

    コマンドレットの構文を表示するには、 PowerShell 環境で Get-Help SQL ServerSQL Server コマンドレットを使用します。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 詳細については、「 Get Help SQL Server PowerShell」を参照してください。For more information, see Get Help SQL Server PowerShell.

SQL Server PowerShell プロバイダーの設定および使用Set up and use the SQL Server PowerShell provider

例 (PowerShell)Example (PowerShell)

次の例では、可用性グループ内のプライマリ レプリカと 1 つのセカンダリ レプリカを読み取り専用ルーティング用に構成します。The following example configures the primary replica and one secondary replica in an availability group for read-only routing. まず、読み取り専用ルーティングの URL が各レプリカに割り当てられます。First, the example assigns a read-only routing URL to each replica. 次に、プライマリ レプリカで読み取り専用ルーティング リストを設定します。Then it sets the read-only routing list on the primary replica. 接続文字列で "ReadOnly" プロパティが設定された接続は、セカンダリ レプリカにリダイレクトされます。Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. ( ConnectionModeInSecondaryRole 設定によって指定されたように) このセカンダリ レプリカを読み取ることができない場合、接続はプライマリ レプリカに戻されます。If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

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  

補足情報:読み取り専用ルーティングを構成した後Follow Up: After Configuring Read-Only Routing

現在のプライマリ レプリカと読み取り可能なセカンダリ レプリカをそれぞれのロールで読み取り専用ルーティングをサポートするように設定すると、読み取り可能なセカンダリ レプリカは、可用性グループ リスナーを介して接続するクライアントからの読み取りを目的とした接続要求を受信できるようになります。Once the current primary replica and the readable secondary replicas are configured to support read-only routing in both roles, the readable secondary replicas can receive read read-intent connection requests from clients that connect via the availability group listener.

ヒント

bcp ユーティリティ または sqlcmd ユーティリティを使用する場合、 -K ReadOnly スイッチを指定することによって、読み取り専用アクセスが有効になっている任意のセカンダリ レプリカへの読み取り専用アクセスを指定できます。When using the bcp Utility or sqlcmd Utility, you can specify read-only access to any secondary replica that is enabled for read-only access by specifying the -K ReadOnly switch.

クライアント接続文字列の要件および推奨事項Requirements and Recommendations for Client Connection-Strings

クライアント アプリケーションで読み取り専用ルーティングを使用するには、クライアントの接続文字列が次の要件を満たしている必要があります。For a client application to use read-only routing, its connection string must satisfy the following requirements:

  • TCP プロトコルを使用する。Use the TCP protocol.

  • アプリケーションの目的の属性またはプロパティを読み取り専用に設定する。Set the application intent attribute/property to readonly.

  • 読み取り専用ルーティングをサポートするように構成された可用性グループのリスナーを参照する。Reference the listener of an availability group that is configured to support read-only routing.

  • その可用性グループ内のデータベースを参照する。Reference a database in that availability group.

さらに、接続文字列でマルチサブネット フェールオーバーを有効にすることをお勧めします。マルチサブネット フェールオーバーは、各サブネット上の各レプリカについて並行クライアント スレッドをサポートします。In addition, we recommend that connection strings enable multi-subnet failover, which supports a parallel client thread for each replica on each subnet. これにより、フェールオーバー後のクライアント再接続時間が最小化されます。This minimizes client reconnection time after a failover.

接続文字列の構文は、アプリケーションが使用している SQL Server プロバイダーに依存します。The syntax for a connection string depends on the SQL Server provider an application is using. .NET Framework Data Provider 4.0.2 for SQL Server 用の次の接続文字列例は、読み取り専用ルーティング用に機能することが必須および推奨される接続文字列の一部を示しています。The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing.

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

読み取り専用のアプリケーションの目的と読み取り専用のルーティングの詳細については、「 可用性グループ リスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)が存在する必要があります。For more information about read-only application intent and read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

読み取り専用ルーティングが正常に動作しない場合If Read-Only Routing is Not Working Correctly

読み取り専用ルーティング構成のトラブルシューティングの詳細については、「 読み取り専用ルーティングが正常に動作しない」を参照してください。For information about troubleshooting a read-only routing configuration, see Read-Only Routing is Not Working Correctly.

次の手順Next Steps

読み取り専用ルーティングの構成を表示するにはTo view read-only routing configurations

クライアント接続アクセスを構成するにはTo configure client connection access

アプリケーションで接続文字列を使用するにはTo use connection strings in applications

ブログ:Blogs:

ホワイト ペーパー:White papers:

その他のコンテンツAdditional content