可用性レプリカでの読み取り専用アクセスの構成 (SQL Server)

既定では、プライマリ レプリカへの読み取り/書き込みアクセスと読み取りを目的としたアクセスの両方が許可され、AlwaysOn 可用性グループのセカンダリ レプリカへの接続は許可されません。 このトピックでは、SQL Server Management Studio、Transact-SQL、または PowerShell を使用して、SQL Server 2012 の AlwaysOn 可用性グループの可用性レプリカに対して接続アクセスを構成する方法について説明します。

セカンダリ レプリカに対して読み取り専用アクセスを有効にすることによる影響と、接続アクセスの概要については、「可用性レプリカに対するクライアント接続アクセスについて (SQL Server)」および「アクティブなセカンダリ: 読み取り可能なセカンダリ レプリカ (AlwaysOn 可用性グループ)」を参照してください。

• 作業を開始する準備:  

  前提条件と制限

  セキュリティ

• 可用性レプリカに対してアクセスを構成するために使用するもの: 

  SQL Server Management Studio

  Transact-SQL

  PowerShell

• 補足情報: 可用性レプリカに対する読み取り専用アクセスの構成後

• 関連タスク

• 関連コンテンツ

作業を開始する準備

前提条件と制限

• 別の接続アクセスを構成するには、プライマリ レプリカをホストするサーバー インスタンスに接続している必要があります。

セキュリティ

権限

[先頭に戻る]

SQL Server Management Studio の使用

可用性レプリカに対してアクセスを構成するには

1. オブジェクト エクスプローラーで、プライマリ レプリカをホストするサーバー インスタンスに接続し、サーバー ツリーを展開します。

2. [AlwaysOn 高可用性] ノードと [可用性グループ] ノードを展開します。

3. 変更するレプリカが含まれる可用性グループをクリックします。

4. 可用性レプリカを右クリックし、[プロパティ] をクリックします。

5. [可用性レプリカ プロパティ] ダイアログ ボックスで、プライマリ ロールおよびセカンダリ ロールの接続アクセスを、次のように変更できます。

   • セカンダリ ロールの場合は、[読み取り可能セカンダリ] ボックスの一覧から新しい値を選択します。値は次のとおりです。

     • [許可しない]
       このレプリカのセカンダリ データベースに対するユーザー接続は禁止されます。 読み取りアクセスで利用することはできません。 これは既定の設定です。

     • [読み取り目的のみ]
       このレプリカのセカンダリ データベースに対する接続は、読み取り専用でのみ許可されます。 セカンダリ データベースはすべて読み取りアクセスで利用できます。

     • [許可する]
       読み取りアクセスに限り、このレプリカのセカンダリ データベースに対するすべての接続が許可されます。 セカンダリ データベースはすべて読み取りアクセスで利用できます。

   • プライマリ ロールの場合は、[プライマリ ロールの接続] ボックスの一覧から新しい値を選択します。値は次のとおりです。

     • [すべての接続を許可]
       プライマリ レプリカのデータベースに対するすべての接続が許可されます。 これは既定の設定です。

     • [読み取り/書き込みの接続を許可]
       Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。 これにより、読み取りを目的としたワークロードが誤ってプライマリ レプリカに接続されるのを防ぐことができます。 Application Intent 接続プロパティの詳細については、「SQL Server Native Client での接続文字列キーワードの使用」を参照してください。

[先頭に戻る]

Transact-SQL の使用

可用性レプリカに対してアクセスを構成するには

注
この手順の例については、このセクションの後半の「例 (Transact-SQL)」を参照してください。

1. プライマリ レプリカをホストするサーバー インスタンスに接続します。

2. 新しい可用性グループのレプリカを指定する場合は、CREATE AVAILABILITY GROUP Transact-SQL ステートメントを使用します。 既存の可用性グループのレプリカを追加または変更する場合は、ALTER AVAILABILITY GROUP Transact-SQL ステートメントを使用します。

   • セカンダリ ロールの接続アクセスを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、SECONDARY_ROLE オプションを次のように指定します。

     SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

     パラメーターの説明

     • NO
       このレプリカのセカンダリ データベースに対する直接接続は禁止されます。 読み取りアクセスで利用することはできません。 これは既定の設定です。

     • READ_ONLY
       このレプリカのセカンダリ データベースに対する接続は、読み取り専用でのみ許可されます。 セカンダリ データベースはすべて読み取りアクセスで利用できます。

     • ALL
       読み取りアクセスに限り、このレプリカのセカンダリ データベースに対するすべての接続が許可されます。 セカンダリ データベースはすべて読み取りアクセスで利用できます。

3. プライマリ ロールの接続アクセスを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、PRIMARY_ROLE オプションを次のように指定します。

   PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   パラメーターの説明

   • READ_WRITE
     Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。 Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 Application Intent 接続プロパティの詳細については、「SQL Server Native Client での接続文字列キーワードの使用」を参照してください。

   • ALL
     プライマリ レプリカのデータベースに対するすべての接続が許可されます。 これは既定の設定です。

例 (Transact-SQL)

次の例では、セカンダリ レプリカを AG2 という名前の可用性グループに追加します。 新しい可用性レプリカをホストするために、スタンドアロン サーバー インスタンス COMPUTER03\HADR_INSTANCE を指定します。 このレプリカは、プライマリ ロールに対してのみ読み取り/書き込み接続を許可し、セカンダリ ロールに対しては読み取りを目的とした接続のみを許可するように構成されています。

ALTER AVAILABILITY GROUP AG2 
   ADD REPLICA ON 
      'COMPUTER03\HADR_INSTANCE' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )
         ); 
GO

[先頭に戻る]

PowerShell の使用

可用性レプリカに対してアクセスを構成するには

注
コード例については、このセクションの後半の「例 (PowerShell)」を参照してください。

1. プライマリ レプリカをホストするサーバー インスタンスにディレクトリを変更 (cd) します。

2. 可用性グループに可用性レプリカを追加する場合は、New-SqlAvailabilityReplica コマンドレットを使用します。 既存の可用性レプリカを変更する場合は、Set-SqlAvailabilityReplica コマンドレットを使用します。 関連するパラメーターは次のとおりです。

   • セカンダリ ロールの接続アクセスを構成するには、ConnectionModeInSecondaryRole の secondary_role_keyword パラメーターを指定します。secondary_role_keyword は次のいずれかの値になります。

     • AllowNoConnections
       セカンダリ レプリカのデータベースに対する直接接続は許可されず、データベースに対して読み取りアクセスを実行できません。 これは既定の設定です。

     • AllowReadIntentConnectionsOnly
       Application Intent プロパティが ReadOnly に設定されている場合に限り、セカンダリ レプリカのデータベースに対する接続が許可されます。 このプロパティの詳細については、「SQL Server Native Client での接続文字列キーワードの使用」を参照してください。

     • AllowAllConnections
       読み取り専用アクセスに限り、セカンダリ レプリカのデータベースに対するすべての接続が許可されます。

   • プライマリ ロールの接続アクセスを構成するには、ConnectionModeInPrimaryRole の primary_role_keyword を指定します。primary_role_keyword は次のいずれかの値になります。

     • AllowReadWriteConnections
       Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。 Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 Application Intent 接続プロパティの詳細については、「SQL Server Native Client での接続文字列キーワードの使用」を参照してください。

     • AllowAllConnections
       プライマリ レプリカのデータベースに対するすべての接続が許可されます。 これは既定の設定です。

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

SQL Server PowerShell プロバイダーを設定して使用するには

• SQL Server PowerShell プロバイダー

例 (PowerShell)

以下の例は、ConnectionModeInSecondaryRole パラメーターと ConnectionModeInPrimaryRole パラメーターの両方を AllowAllConnections に設定しています。

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica

[先頭に戻る]

補足情報: 可用性レプリカに対する読み取り専用アクセスの構成後

読み取り可能なセカンダリ レプリカに対する読み取り専用アクセス

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

• 読み取り可能なセカンダリ レプリカに接続するクライアント アプリケーションを有効にするには:

  	前提条件	リンク
  	可用性グループにリスナーがあることを確認する。	可用性グループ リスナーの作成または構成 (SQL Server)
  	可用性グループの読み取り専用ルーティングを構成する。	可用性グループの読み取り専用ルーティングの構成 (SQL Server)

フェールオーバー後にトリガーとジョブに影響する可能性がある要因

読み取り可能でないセカンダリ データベースまたは読み取り可能なセカンダリ データベースで実行されたときに失敗するトリガーとジョブがある場合は、トリガーとジョブをスクリプト化して、特定のレプリカに対して、データベースがプライマリ データベースか読み取り可能なセカンダリ データベースかを確認する必要があります。 この情報を入手するには、データベースの Updatability プロパティを返す DATABASEPROPERTYEX 関数を使用します。 読み取り専用データベースを識別するには、次のように、値として READ_ONLY を指定します。

DATABASEPROPERTYEX([db name],’Updatability’) = N’READ_ONLY’

読み取り/書き込みデータベースを識別するには、値として READ_WRITE を指定します。

[先頭に戻る]

関連タスク

• 可用性グループの読み取り専用ルーティングの構成 (SQL Server)

• 可用性グループ リスナーの作成または構成 (SQL Server)

[先頭に戻る]

関連コンテンツ

• AlwaysOn: Value Proposition of Readable Secondary

• AlwaysOn: Why there are two options to enable a secondary replica for read workload?

• AlwaysOn: Setting up Readable Seconary Replica

• AlwaysOn: I just enabled Readable Secondary but my query is blocked?

• AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

• AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica

• AlwaysOn: Impact on the primary workload when you run reporting workload on the secondary replica

• AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation

• AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

• AlwaysOn: Readable Secondary and data latency

[先頭に戻る]

関連項目

概念

AlwaysOn 可用性グループの概要 (SQL Server)

アクティブなセカンダリ: 読み取り可能なセカンダリ レプリカ (AlwaysOn 可用性グループ)

可用性レプリカに対するクライアント接続アクセスについて (SQL Server)