可用性レプリカでの Read-Only アクセスの構成 (SQL Server)

2017-10-27

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

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

開始する前に

前提条件と制限

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

安全

権限

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

SQL Server Management Studio の使用

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

オブジェクトエクスプローラーで、プライマリレプリカをホストするサーバーインスタンスに接続し、サーバーツリーを展開します。
[AlwaysOn 高可用性] ノードと [可用性グループ] ノードを展開します。
変更するレプリカが含まれる可用性グループをクリックします。
可用性レプリカを右クリックし、 [プロパティ] をクリックします。
[可用性レプリカプロパティ] ダイアログボックスで、プライマリロールおよびセカンダリロールの接続アクセスを、次のように変更できます。
- セカンダリロールの場合は、 [読み取り可能セカンダリ] ボックスの一覧から新しい値を選択します。値は次のとおりです。
  
  いいえ
  このレプリカのセカンダリデータベースに対するユーザー接続は禁止されます。読み取りアクセスで利用することはできません。これは既定の設定です。
  
  [読み取り目的のみ]
  このレプリカのセカンダリデータベースに対する接続は、読み取り専用でのみ許可されます。セカンダリデータベースはすべて読み取りアクセスで利用できます。
  
  はい
  読み取りアクセスに限り、このレプリカのセカンダリデータベースに対するすべての接続が許可されます。セカンダリデータベースはすべて読み取りアクセスで利用できます。
- プライマリロールの場合は、 [プライマリロールの接続] ボックスの一覧から新しい値を選択します。値は次のとおりです。
  
  [すべての接続を許可]
  プライマリレプリカのデータベースに対するすべての接続が許可されます。これは既定の設定です。
  
  [読み取り/書き込みの接続を許可]
  Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。これにより、読み取りを目的としたワークロードが誤ってプライマリレプリカに接続されるのを防ぐことができます。 "アプリケーションの目的" 接続プロパティの詳細については、「 Using Connection String Keywords with SQL Server Native Client」を参照してください。

Transact-SQL の使用

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

注

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

プライマリレプリカをホストするサーバーインスタンスに接続します。
新しい可用性グループのレプリカを指定する場合は、CREATE AVAILABILITY GROUP Transact-SQL ステートメントを使用します。既存の可用性グループのレプリカを追加または変更する場合は、ALTER AVAILABILITY GROUP Transact-SQL ステートメントを使用します。
- セカンダリロールの接続アクセスを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、SECONDARY_ROLE オプションを次のように指定します。
  
  セカンダリ役割 ( 接続許可 = { なし | 読み取り専用 | すべて } )
  
  ここで、
  
  いいえ
  このレプリカのセカンダリデータベースに対する直接接続は禁止されます。読み取りアクセスで利用することはできません。これは既定の設定です。
  
  読み取り専用
  このレプリカのセカンダリデータベースに対する接続は、読み取り専用でのみ許可されます。セカンダリデータベースはすべて読み取りアクセスで利用できます。
  
  全て
  読み取りアクセスに限り、このレプリカのセカンダリデータベースに対するすべての接続が許可されます。セカンダリデータベースはすべて読み取りアクセスで利用できます。
プライマリロールの接続アクセスを構成するには、ADD REPLICA 句または MODIFY REPLICA WITH 句で、PRIMARY_ROLE オプションを次のように指定します。

PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE |ALL } )

ここで、

読み書き
Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。 Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 "アプリケーションの目的" 接続プロパティの詳細については、「 Using Connection String Keywords with SQL Server Native Client」を参照してください。

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

例 (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 の例を参照してください。

プライマリレプリカをホストするサーバーインスタンスにディレクトリを変更 (cd) します。
可用性レプリカを可用性グループに追加する場合は、 New-SqlAvailabilityReplica コマンドレットを使用します。既存の可用性レプリカを変更する場合は、 Set-SqlAvailabilityReplica コマンドレットを使用します。関連するパラメーターは次のとおりです。
- セカンダリロールの接続アクセスを構成するには、 ConnectionModeInSecondaryRolesecondary_role_keyword パラメーターを指定します。 ここで、secondary_role_keyword は次のいずれかの値になります。
  
  AllowNoConnections
  セカンダリレプリカのデータベースに対する直接接続は許可されず、データベースに対して読み取りアクセスを実行できません。これは既定の設定です。
  
  AllowReadIntentConnectionsOnly
  Application Intent プロパティが ReadOnlyに設定されている場合に限り、セカンダリレプリカのデータベースに対する接続が許可されます。このプロパティの詳細については、「 Using Connection String Keywords with SQL Server Native Client」を参照してください。
  
  AllowAllConnections
  読み取り専用アクセスに限り、セカンダリレプリカのデータベースに対するすべての接続が許可されます。
- プライマリロールの接続アクセスを構成するには、 ConnectionModeInPrimaryRoleprimary_role_keywordを指定 します。ここで、primary_role_keyword は次のいずれかの値になります。
  
  AllowReadWriteConnections
  Application Intent 接続プロパティが ReadOnly に設定されている接続は許可されません。 Application Intent プロパティが ReadWrite に設定されている場合、または Application Intent 接続プロパティが設定されていない場合は、接続が許可されます。 "アプリケーションの目的" 接続プロパティの詳細については、「 Using Connection String Keywords with SQL Server Native Client」を参照してください。
  
  AllowAllConnections
  プライマリレプリカのデータベースに対するすべての接続が許可されます。これは既定の設定です。
注

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

SQL Server PowerShell プロバイダーを設定して使用するには、「 SQL Server 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)

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

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

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

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

こちらもご覧ください

AlwaysOn 可用性グループの概要 (SQL Server)
アクティブなセカンダリ: 読み取り可能なセカンダリレプリカ (AlwaysOn 可用性グループ)
可用性レプリカに対するクライアント接続アクセスについて (SQL Server)