Always On 可用性グループのセカンダリ レプリカに読み取り専用の負荷を移す

適用対象 SQL Server

Always On 可用性グループ のアクティブなセカンダリ機能では、1 つ以上のセカンダリ レプリカ (読み取り可能なセカンダリ レプリカ) への読み取り専用アクセスをサポートしています。 読み取り可能なセカンダリ レプリカは、同期コミット可用性モードまたは非同期コミット可用性モードのいずれにも指定できます。 読み取り可能なセカンダリ レプリカは、すべてのセカンダリ データベースへの読み取り専用アクセスを許可します。 ただし、読み取り可能なセカンダリ データベースは読み取り専用に設定されません。 これらは動的です。 セカンダリ データベースは、対応するプライマリ データベースに対する変更がそのセカンダリ データベースに適用されると変更されます。 一般的なセカンダリ レプリカでは、持続性のあるメモリ最適化テーブルを含めて、セカンダリ データベースのデータはほぼリアルタイムです。 また、フルテキスト インデックスはセカンダリ データベースと同期されます。 多くの場合、プライマリ データベースと対応するセカンダリ データベース間のデータ待機時間は数秒です。

プライマリ データベースに適用されるセキュリティ設定は、セカンダリ データベースに保存されます。 これには、ユーザー、データベース ロール、およびアプリケーション ロールと、それぞれの権限が含まれます。また、透過的なデータ暗号化 (TDE) も含まれます (プライマリ データベースで有効な場合)。

Note

セカンダリ データベースにデータを書き込むことはできませんが、ユーザー データベースやシステム データベース ( tempdbなど) を含め、セカンダリ レプリカをホストするサーバー インスタンス上の読み書き可能なデータベースには書き込むことができます。

Always On 可用性グループ また、読み取り可能なセカンダリ レプリカへの読み取りを目的とした接続要求の再ルーティング (読み取り専用ルーティング) もサポートしています。 読み取り専用ルーティングについては、「 リスナーを使用した読み取り専用セカンダリ レプリカ (読み取り専用ルーティング) への接続」を参照してください。

メリット

読み取り可能なセカンダリ レプリカに読み取り専用接続を割り当てることには、次の利点があります。

• プライマリ レプリカを読み取り専用ワークロードから解放することができ、リソースをミッション クリティカルなワークロード用に保護できます。 ミッション クリティカルな読み取りワークロードまたは待機時間が許容できないワークロードは、プライマリ上で実行する必要があります。

• 読み取り可能なセカンダリ レプリカをホストするシステムに対する投資収益率を向上させます。

また、読み取り可能なセカンダリは、次のように読み取り専用操作に対する堅牢なサポートを提供します。

• 読み取り可能なセカンダリ データベースの自動一時統計により、ディスク ベース テーブルに対する読み取り専用クエリが最適化されます。 メモリ最適化テーブルの場合、不足している統計は自動的に作成されます。 ただし、古い統計の自動更新はありません。 手動でプライマリ レプリカの統計を更新する必要があります。 詳細については、このトピックの後の「 読み取り専用アクセス データベースの統計」をご覧ください。

• ディスク ベース テーブルに対する読み取り専用ワークロードは、行のバージョン管理を使用してセカンダリ データベースでのブロック競合を除去します。 他のトランザクション分離レベルが明示的に設定されていても、セカンダリ データベースに対して実行されるすべてのクエリは、スナップショット分離トランザクション レベルに自動的にマップされます。 また、すべてのロック ヒントが無視されます。 これにより、リーダー/ライターの競合が解消されます。

• 持続性のあるメモリ最適化テーブルに対する読み取り専用ワークロードは、同じトランザクション分離レベル制限 (「 データベース エンジンにおける分離レベル」を参照) でネイティブ ストアド プロシージャまたは SQL 相互運用性を使用して、プライマリ データベースでのアクセスとまったく同じ方法でデータにアクセスします。 プライマリ レプリカで実行されるレポート ワークロードまたは読み取り専用クエリは、セカンダリ レプリカでも変更せずに実行できます。 同様に、セカンダリ レプリカで実行されるレポート ワークロードまたは読み取り専用クエリは、プライマリ レプリカでも変更せずに実行できます。 他のトランザクション分離レベルが明示的に設定されていても、セカンダリ データベースに対して実行されるすべてのクエリは、ディスク ベース テーブルと同様に、スナップショット分離トランザクション レベルに自動的にマップされます。

• セカンダリ レプリカ上では、ディスク ベースおよびメモリ最適化テーブル型の両方について、テーブル変数に対する DML 操作が許可されます。

可用性グループの前提条件

• 読み取り可能なセカンダリ レプリカ (必須)

  データベース管理者は、セカンダリ ロールで実行しているときに、すべての接続 (読み取り専用アクセスのみ) または読み取りを目的とした接続のみを許可するように 1 つ以上のレプリカを構成する必要があります。

  Note

  必要に応じて、プライマリ ロールで実行しているときに、読み取り専用接続を除外するように可用性レプリカを構成することもできます。

  詳細については、「可用性レプリカに対するクライアント接続アクセスについて (SQL Server)」を参照してください。

  警告

  SQL Server の同じメジャー ビルド上のレプリカのみが読み取り可能になります。 詳細については、ローリング アップグレードの基本に関するページを参照してください。

• 可用性グループ リスナー

  読み取り専用ルーティングをサポートするには、可用性グループに 可用性グループ リスナーが存在する必要があります。 読み取り専用クライアントは、このリスナーに接続要求を送信する必要があります。クライアントの接続文字列では、アプリケーションの目的として "読み取り専用" を指定する必要があります。つまり、読み取りを目的とした接続要求であることが必要です。

• 読み取り専用ルーティング

  読み取り専用ルーティング は、可用性グループ リスナーに送信される読み取りを目的とした受信接続要求を、使用できる読み取り可能なセカンダリ レプリカにルーティングする SQL Server の機能です。 読み取り専用ルーティングの前提条件は次のとおりです。

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

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

    Note

    読み取りを目的とした接続要求は、レプリカ間で負荷を分散することができます。 詳細については、「読み取り専用レプリカ間の負荷分散の構成」を参照してください。

  詳細については、可用性グループの読み取り専用ルーティングの構成 (SQL Server) に関する記事を参照してください

Note

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

制限事項と制約事項

次に示すように、一部の操作は完全にはサポートされていません。

• 読み取り可能なレプリカは、読み取り用に有効になるとすぐに、セカンダリ データベースへの接続の受け入れを開始できます。 ただし、プライマリ データベースにアクティブなトランザクションが存在する場合、行のバージョンは、対応するセカンダリ データベースで完全に使用可能にはなりません。 セカンダリ レプリカの構成時にプライマリ レプリカ上に存在していたアクティブなトランザクションは、コミットまたはロールバックする必要があります。 このプロセスが完了するまでは、セカンダリ データベースでのトランザクションの分離レベルのマッピングは完了せず、クエリは一時的にブロックされます。

  警告

  長いトランザクションを実行すると、ディスク ベース テーブルおよびメモリ最適化テーブルの両方について、バージョン管理される行の保持数に影響が及びます。

• メモリ最適化テーブルを含むセカンダリ データベースでは、メモリ最適化テーブルに対して行バージョンが常に生成されますが、セカンダリ レプリカが読み取り用として有効になった際にプライマリ レプリカに存在していたすべてのアクティブなトランザクションが完了するまで、クエリがブロックされます。 これにより、ディスク ベース テーブルとメモリ最適化テーブルの両方が同時にレポート ワークロードおよび読み取り専用クエリに使用できるようになります。

• 変更の追跡および変更データ キャプチャは、読み取り可能なセカンダリ レプリカに所属するセカンダリ データベースではサポートされません。

  • 変更の追跡は、セカンダリ データベースでは明示的に無効になります。

  • 変更データ キャプチャは、セカンダリ レプリカ データベースのみで有効にすることはできません。 変更データ キャプチャは、プライマリ レプリカ データベースで有効にすることができ、セカンダリ レプリカ データベースで関数を使用して CDC テーブルから変更を読み取ることができます。

• 読み取り操作はスナップショット分離トランザクション レベルにマップされるため、プライマリ レプリカでのゴースト レコードのクリーンアップが 1 つ以上のセカンダリ レプリカのトランザクションによってブロックされることがあります。 ゴースト レコードのクリーンアップ タスクでは、プライマリ レプリカのディスク ベース テーブルに含まれるゴースト レコードが、セカンダリ レプリカで不要になったときに自動的にクリーンアップされます。 これは、プライマリ レプリカでトランザクションを実行する場合の処理と似ています。 セカンダリ データベースでの極端なケースでは、ゴーストのクリーンアップをブロックしているセカンダリ上の実行時間の長い読み取りクエリを終了する必要があります。 セカンダリ レプリカの接続が切断された場合、またはセカンダリ データベースでデータの移動が中断された場合、ゴーストのクリーンアップがブロックされる可能性があります。 ゴースト レコードはデータ ファイル内の物理領域を使用します。これにより、領域の再利用に関する問題が発生する可能性があります。詳細については、ゴースト クリーンアップに関する記事を参照してください。 この状態ではログの切り捨ても実行できなくなるので、この状態が続く場合は、セカンダリ データベースを可用性グループから削除することをお勧めします。 メモリ最適化テーブルでは、行バージョンがメモリに保持され、プライマリ レプリカの行バージョンに依存しないため、ゴースト レコードのクリーンアップに関する問題は発生しません。

• セカンダリ レプリカで引き続き必要なゴースト レコードがファイルに含まれている場合、プライマリ レプリカでのディスク ベース テーブルが含まれているファイルに対する DBCC SHRINKFILE 操作は失敗する可能性があります。

• SQL Server 2014 (12.x) 以降では、ユーザー操作やエラーが原因でプライマリ レプリカがオフラインになっても、読み取り可能なセカンダリ レプリカをオンラインのままにすることができます。たとえば、ユーザー コマンドやエラーが原因で同期が中断された場合や、WSFC がオフラインになっているためにレプリカで状態を解決している場合などです。 ただし、この場合、可用性グループ リスナーもオフラインになるため、読み取り専用ルーティング機能しません。 読み取り専用ワークロードにアクセスするには、クライアントは読み取り専用セカンダリ レプリカに直接接続する必要があります。

Note

読み取り可能なセカンダリ レプリカをホストしているサーバー インスタンスで、 sys.dm_db_index_physical_stats 動的管理ビューに対してクエリを実行すると、再実行のブロックという問題が発生する可能性があります。 これは、この動的管理ビューが、指定したユーザー テーブルまたはビューで IS ロックを獲得することが原因です。IS ロックは、そのユーザー テーブルまたはビューの X ロックに関して REDO スレッドの要求をブロックする可能性があります。

パフォーマンスに関する考慮事項

このセクションでは、読み取り可能なセカンダリ データベースのパフォーマンスに関するいくつかの考慮事項について説明します。

このセクションの内容

• データ待機時間

• 読み取り専用ワークロードの影響

• インデックス作成

• 読み取り専用アクセス データベースの統計

データ待機時間

セカンダリ レプリカへの読み取り専用アクセスの実装が役立つのは、読み取り専用ワークロードである程度のデータ待機時間を許容できる場合です。 データ待機時間が許容できない場合は、読み取り専用ワークロードをプライマリ レプリカに対して実行することを検討してください。

プライマリ レプリカは、プライマリ データベースでの変更のログ レコードをセカンダリ レプリカに送信します。 それぞれのセカンダリ データベースで、専用の再実行スレッドがログ レコードに適用されます。 読み取りアクセス セカンダリ データベースでは、その変更を含むログ レコードがセカンダリ データベースに適用され、トランザクションがプライマリ データベース上でコミットされない限り、特定のデータの変更はクエリ結果に反映されません。

つまり、プライマリ レプリカとセカンダリ レプリカの間には待機時間 (通常は数秒程度) が発生します。 ただし、ネットワークの問題のためにスループットが低下するなどの特殊なケースでは、待機時間が長くなることがあります。 I/O ボトルネックが生じた場合やデータの移動が中断された場合は、待機時間が増加します。 データ移動の中断を監視するには、 Always On ダッシュボード または sys.dm_hadr_database_replica_states 動的管理ビューを使用できます。

メモリ最適化テーブルが含まれるデータベースでのデータ待機時間

SQL Server 2014 (12.x) では、アクティブなセカンダリでのデータ待機時間に関して、特別な考慮事項がありました。「SQL Server 2014 (12.x)アクティブなセカンダリ: 読み取り可能なセカンダリ レプリカ」を参照してください。 SQL Server 2016 (13.x) 以降では、メモリ最適化テーブルのデータ待機時間に関する特別な考慮事項はありません。 メモリ最適化テーブルの予想されるデータ待機時間は、ディスク ベース テーブルの待機時間に相当します。

読み取り専用ワークロードの影響

セカンダリ レプリカを読み取り専用アクセス用に構成した場合、セカンダリ データベースに対する読み取り専用ワークロードによって (ディスク ベース テーブルに対する読み取り専用ワークロードで大量の I/O が発生する場合には特に)、再実行スレッドの CPU や (ディスク ベース テーブルに対する) I/O などのシステム リソースが消費されます。 すべての行がメモリ内にあるため、メモリ最適化テーブルにアクセスしても IO の影響はありません。

また、セカンダリ レプリカの読み取り専用ワークロードによって、ログ レコードを介して適用されるデータ定義言語 (DDL) による変更がブロックされることもあります。

• 行のバージョン管理のために、読み取り操作は共有ロックを保持しませんが、スキーマ安定度 (Sch-S) ロックを保持します。これにより、DDL の変更を適用する再実行操作がブロックされることがあります。 DDL 操作にはテーブルおよびビューの ALTER/DROP が含まれますが、ストアド プロシージャの DROP や ALTER は含まれません。 たとえば、プライマリのディスク ベースまたはメモリ最適化テーブルを削除する場合などです。 再実行スレッドは、テーブルを削除するためにログ レコードを処理する場合、テーブルに対する SCH_M ロックを取得する必要があります。また、テーブルにアクセスする実行中のクエリによってブロックされる可能性があります。 この動作は、プライマリ レプリカでも同じです。ただし、テーブルの削除は再実行スレッドではなくユーザー セッションの一部として実行されます。

• メモリ最適化テーブルに対しては、他にもブロック要素があります。 セカンダリ レプリカで同時実行されているネイティブ ストアド プロシージャがある場合に、ネイティブ ストアド プロシージャを削除すると、再実行スレッドのブロックが発生する可能性があります。 この動作は、プライマリ レプリカでも同じです。ただし、ストアド プロシージャの削除は再実行スレッドではなくユーザー セッションの一部として実行されます。

クエリの作成に関するベスト プラクティスを調べて、セカンダリ データベースでベスト プラクティスを実施してください。 たとえば、データ集計などの実行時間の長いクエリは使用率が低い時間帯にスケジュールするようにします。

Note

セカンダリ レプリカに対するクエリによって再実行スレッドがブロックされると、 sqlserver.lock_redo_blocked XEvent が発生します。

インデックス作成

読み取り可能なセカンダリ レプリカでの読み取り専用ワークロードを最適化するには、セカンダリ データベースのテーブルにインデックスを作成できます。 セカンダリ データベースではスキーマやデータの変更を行えないため、プライマリ データベースでインデックスを作成し、再実行プロセスによってセカンダリ データベースに変更を転送できるようにします。

セカンダリ レプリカでのインデックス使用状況を監視するには、 sys.dm_db_index_usage_stats動的管理ビューの user_seeks列、 user_scans 列、および user_lookups 列を照会します。

読み取り専用アクセス データベースの統計

テーブルとインデックス付きビューの列の統計情報を使用して、クエリ プランを最適化します。 可用性グループの場合、プライマリ データベースで作成および管理される統計は、トランザクション ログ レコードの適用の一部としてセカンダリ データベースに自動的に保存されます。 ただし、セカンダリ データベースの読み取り専用ワークロードについて、プライマリ データベースで作成される統計とは別の統計が必要になることがあります。 しかし、セカンダリ データベースは読み取り専用アクセスに制限されているため、セカンダリ データベースで統計を作成することはできません。

この問題に対処するために、セカンダリ レプリカでは、セカンダリ データベースの一時的な統計を、 tempdbに作成して管理します。 一時的な統計と、(プライマリ データベースで作成されてセカンダリ データベースに保存される) 永続的な統計とを区別するために、サフィックス _readonly_database_statistic が一時的な統計の名前に付加されます。

一時的な統計を作成して更新できるのは、 SQL Server のみです。 ただし、永続的な統計の場合と同じツールを使用すると、一時的な統計を削除したり、そのプロパティを監視したりできます。

• DROP STATISTICS Transact-SQL ステートメントを使用して一時的な統計を削除します。

• sys.stats カタログ ビューと sys.stats_columns カタログ ビューを使用して統計を監視します。 sys_stats には、どの統計が一時的または永続的なものかを示すための is_temporary列が含まれています。

プライマリ レプリカまたはセカンダリ レプリカ上のメモリ最適化テーブルでは、自動統計更新がサポートされません。 セカンダリ レプリカに対するクエリ パフォーマンスとプランを監視し、必要であれば、プライマリ レプリカの統計を手動で更新する必要があります。 ただし、不足している統計は、プライマリ レプリカとセカンダリ レプリカについて自動的に作成されます。

SQL Server の統計の詳細については、「 統計」を参照してください。

このセクションの内容

• セカンダリ データベースの古い永続的な統計

• 制限事項と制約事項

セカンダリ データベースの古い永続的な統計

SQL Server によって検出されます。 しかし、プライマリ データベースで変更する以外は、永続的な統計に変更を加えることはできません。 クエリの最適化のために、 SQL Server はセカンダリ データベースのディスク ベース テーブルに一時的な統計を作成し、古い永続的な統計の代わりにこの統計を使用します。

永続的な統計がプライマリ データベースで更新されると、統計は自動的にセカンダリ データベースに永続化されます。 次に、 SQL Server は、一時的な統計よりも新しい、更新された永続的な統計を使用します。

可用性グループのフェールオーバーが発生すると、すべてのセカンダリ レプリカで一時的な統計が削除されます。

制限事項と制約事項

• 一時的な統計は tempdbに格納されるので、 SQL Server サービスを再起動すると、一時的な統計はすべてなくなります。

• サフィックス _readonly_database_statistic は、 SQL Serverによって生成される統計用に予約されています。 このサフィックスは、プライマリ データベースで統計を作成するときには使用できません。 詳細については、統計に関する記事を参照してください。

セカンダリ レプリカ上でのメモリ最適化テーブルへのアクセス

セカンダリ レプリカ上でメモリ最適化テーブルとともに使用できるトランザクション分離レベルは、プライマリ レプリカでのレベルと同じです。 セッション レベルの分離レベルを READ COMMITTED に、またデータベース レベル オプション MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT を ON に設定することをお勧めします。 次に例を示します。

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

キャパシティ プランニングの注意点

• ディスク ベース テーブルの場合、読み取り可能なセカンダリ レプリカでは、2 つの理由から tempdb に領域が必要になることがあります。

  • スナップショット分離レベルは行バージョンを tempdbにコピーします。

  • セカンダリ データベースの一時的な統計が tempdbに作成されて管理されます。 一時的な統計により、 tempdbのサイズが多少増加することがあります。 詳細については、このセクションの後の「 読み取り専用アクセス データベースの統計」をご覧ください。

• 1 つまたは複数のセカンダリ レプリカに対して読み取りアクセスを構成した場合、ディスク ベース テーブルについてセカンダリ データベースに行バージョンのポインターを格納するために、プライマリ データベースでデータ行の削除、変更、または挿入ごとに 14 バイトのオーバーヘッドが生成されます。 この 14 バイトのオーバーヘッドはセカンダリ データベースに適用されます。 14 バイトのオーバーヘッドがデータ行に追加されるため、ページ分割が発生することがあります。

  行バージョンのデータはプライマリ データベースでは生成されません。 代わりに、セカンダリ データベースで行バージョンが生成されます。 ただし、行のバージョン管理により、プライマリ データベースとセカンダリ データベースの両方でデータ ストレージが増加します。

  行バージョン データの追加の有無は、プライマリ データベースのスナップショット分離または READ COMMITTED スナップショット分離 (RCSI) レベル設定によって決まります。 次の表では、ディスク ベース テーブルについて、読み取り可能なセカンダリ データベースでのバージョン管理の動作を設定別に説明します。

  セカンダリ レプリカは読み取り可能かどうか	スナップショット分離または RCSI レベルは有効かどうか	プライマリ データベース	セカンダリ データベース
  いいえ	いいえ	行のバージョンまたは 14 バイトのオーバーヘッドなし	行のバージョンまたは 14 バイトのオーバーヘッドなし
  いいえ	はい	行のバージョンおよび 14 バイトのオーバーヘッド	行のバージョンはないが、14 バイトのオーバーヘッドあり
  はい	いいえ	行のバージョンはないが、14 バイトのオーバーヘッドあり	行のバージョンおよび 14 バイトのオーバーヘッド
  はい	はい	行のバージョンおよび 14 バイトのオーバーヘッド	行のバージョンおよび 14 バイトのオーバーヘッド

Related Tasks

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

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

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

• 可用性グループの監視 (Transact-SQL)

• 可用性レプリカのプロパティの表示 (SQL Server)

• [新しい可用性グループ] ダイアログ ボックスの使用 (SQL Server Management Studio)

関連コンテンツ

• SQL Server Always On チーム ブログ:SQL Server Always On チームのオフィシャル ブログ

参照

Always On 可用性グループの概要 (SQL Server)
可用性レプリカに対するクライアント接続アクセスについて (SQL Server)
可用性グループ リスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)
統計