• 戻る（冗長化アーキテクチャ、SQL Server）（分散・冗長化）
  • SQL Server のレプリケーション
  • SQL Server のクラスタリング / SQL Server リンクサーバ機能 / Elastic Scale, Elastic Database Pool

• SQL Server には以下のレプリケーション機構がある。
• 古くからある機能だが、新規機能追加も頻繁に行われており、全体の再編も起こっている。

• スナップショット レプリケーション
  データの変更は追跡されず、
  スナップショットが適用されるたびに、レプリケートされる。

• トランザクション レプリケーション

  • トランザクション ログを使用して変更が追跡される。
  • ログ配布とは仕組みが異なり、よりリアルタイム。

• マージ レプリケーション
  トリガーとメタデータ テーブルを使用して変更が追跡される。

• ログ配布

  • トランザクション ログの転送をベースに利用している。
  • トランザクション レプリケーションとは仕組みが異なる。

• AlwaysOn

  • 最近出てきた機能で、色々なパターンに対してワンストップ。
  • 同期/非同期モード、自動/手動フェールオーバーがある。
  • トランザクション レプリケーションともログ配布とも仕組みが異なる。

• ログ配布
  トランザクション ログの転送をベースに利用している。

• フェールオーバー クラスタリング (WSFC)

• データベース ミラーリング

  • トランザクション ログの転送をベースに利用している。
  • ミラー サーバーは読み取り専用ではなく、
  • フェールオーバー時にのみアクティブになる。
  • SQL Server の将来のバージョンで削除される予定。

• AlwaysOn

  • 同期モード、非同期モードがある。
  • 自動フェールオーバー、手動フェールオーバーがある。
  • 最近出てきた機能で、色々なパターンに対してワンストップ。

補足（全体の見取り図）: 名前が似ていて混乱しやすいので、
単位・方向・目的で整理しておく。

機構	複製の単位	方向	主目的	転送の仕組み
スナップショット レプリケーション	アーティクル（表・ビュー等）	単方向	参照系の配布	全件コピー
トランザクション レプリケーション	アーティクル	単方向（更新可も可）	参照系の配布・分析用	ログから抽出して SQL 再適用
マージ レプリケーション	アーティクル	双方向	断続接続のクライアント同期	トリガ + メタデータ表
ログ配布	データベース	単方向	災害対策・ウォーム スタンバイ	ログ バックアップの転送・復元
データベース ミラーリング	データベース	単方向	耐障害	ログ レコードの転送
可用性グループ (AG)	データベース（複数まとめて）	単方向	耐障害 + 参照分散	ログ ブロックの転送
FCI	インスタンス	-	耐障害	複製しない（共有ストレージ）

「レプリケーション」（パブリッシャ／サブスクライバのモデル）は
オブジェクト単位でデータを配る仕組みであり、
ログ配布・ミラーリング・AG のような可用性の仕組みとは目的が別である。
本ページはこの 2 系統をまとめて扱っている点に注意して読む。

補足（最新化：現在の推奨）:

• データベース ミラーリングは非推奨（SQL Server 2012 で非推奨化）。
  予告どおり AlwaysOn 可用性グループに再編された。新規構築では使わない。

• AlwaysOn は現在 2 つの機能の総称として整理されている。

  • Always On 可用性グループ（AG）
  • Always On フェールオーバー クラスター インスタンス（FCI）

• 耐障害を目的とするなら AG が第一候補。
  詳細はSQL Server のクラスタリングを参照。

• 参照系のオフロードも、レプリケーションを組むより
  AG の読み取り可能セカンダリ（Enterprise Edition）のほうが
  構成・運用ともに簡単である。

• レプリケーション（パブリッシャ／サブスクライバ）が今も有効なのは、

  • 一部のテーブル・列・行だけを配りたい
  • 異機種・異バージョンへ配りたい
  • 双方向同期が要る（マージ レプリケーション）

  といったケースである。

レプリケーション トポロジ内のコンポーネントを出版業界に例えて表す。

レプリケーションのパブリッシング モデルの概要
https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/publish/replication-publishing-model-overview

• レプリケーションを介して他の場所でデータを使用できるようにする
  データベース インスタンス。
• パブリッシャーは、
  • 1 つ以上のパブリケーションを持つことができ、
  • 各パブリケーションには、論理的に関連する
    • レプリケート対象のオブジェクトと
    • データのセットが定義される。

• 1 つ以上のパブリッシャーに関連付けられた
  レプリケーション固有のデータの保存場所として機能するデータベース インスタンス。

• 各パブリッシャーは、ディストリビューターの単一の
  ディストリビューション データベースと関連付けられる。

• ディストリビューション データベース

  • レプリケーション状態データ、パブリケーションに関するメタデータが保存される。
  • 場合によっては、パブリッシャーからサブスクライバーへ移動するデータの
    キューとしても機能する。

• モデル

  • ローカル ディストリビューター
    単一のデータベース サーバー インスタンスが、
    パブリッシャーとディストリビューター両方の役割を果たす場合。
  • リモート ディストリビューター
    パブリッシャーとディストリビューターが
    別のデータベース サーバー インスタンス上で構成される場合。

補足（ディストリビューション DB の監視が要る）: サブスクライバーへの
配信が滞ると、ディストリビューション データベースに
未配信のコマンドが溜まり続けて肥大化する。
保有期間（既定 72 時間）を超えるとサブスクリプションが
**期限切れ（expired）**になり、再初期化（スナップショットからやり直し）が必要になる。
レプリケーション監視ツール（Replication Monitor）と
distribution.dbo.MSdistribution_status で滞留を監視する。

• レプリケートされたデータを受信するデータベース インスタンス。

• サブスクライバーは、複数のパブリッシャーおよびパブリケーションから
  データを受信できる。

• 選択したレプリケーションの種類に応じて、サブスクライバーは

  • パブリッシャーにデータの変更を戻したり、
  • データを他のサブスクライバーに再パブリッシュしたりできる。

データベースからの 1 つないし複数のアーティクルの集合。

• テーブルやレプリケーション用に指定されたデータベース・オブジェクト。

• パブリケーションには、さまざまな種類のアーティクルを含めることができる。

  • テーブル
  • ビュー
  • ストアド プロシージャ
  • その他のオブジェクト

• テーブルがアーティクルとしてパブリッシュされている場合は、
  フィルターを使用してサブスクライバーに送信するデータの
  列と行を制限することができる。

パブリケーションのコピーを要求すること。

• パブリケーションをいつ、どこで受信するのかが定義される。
• サブスクリプションには、プッシュとプルの 2 つの種類がある。

補足（プッシュとプルの選び分け）:

	プッシュ	プル
エージェントの実行場所	ディストリビューター	サブスクライバー
負荷	ディストリビューターに集中	サブスクライバーに分散
向くケース	サブスクライバーが少数・常時接続	サブスクライバーが多数、または断続接続

モバイル端末など多数のサブスクライバーを抱えるマージ レプリケーションでは
プルが定石。

主にスケールアウトで利用されるレプリケーションの種類。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/types-of-replication

データの変更は追跡されず、
スナップショットが適用されるたびに、レプリケートされる。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/snapshot-replication

スナップショット レプリケーションでは、

• 特定の時間のデータを配信する。
• データに対する更新は監視されない。
• 同期が発生すると
  • データ全体のスナップショットが作成され、
  • サブスクライバーに送信される。

VSS のスナップショットでファイルの変更点を配布している。

移行メモ（正誤）: スナップショット レプリケーションは
VSS（ボリューム シャドウ コピー サービス）とは無関係である。
スナップショット エージェントが
BCP 形式のデータ ファイルとスキーマ スクリプトを生成し、
スナップショット フォルダ経由でサブスクライバーに適用する仕組みである。
また、「ファイルの変更点を配布」ではなく
毎回データ全体を配布する（差分ではない）点も本文の説明どおり。

SQL Server トランザクション ログを使用して変更が追跡される。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/transactional/transactional-replication

• 開始
  パブリケーションのデータベース オブジェクトとデータのスナップショットで開始される。

• 配信

  • 最初のスナップショットが取得されるとすぐに配信される。
  • それ以後パブリッシャーでデータやスキーマが変更されると、
    通常はその都度 (ほぼリアルタイムで) サブスクライバーに配信される。

• データの変更

  • パブリッシャーで発生したのと同じ順序で、
    同じトランザクションの中で、サブスクライバーに適用される。
  • したがって、パブリケーション内では、トランザクションの一貫性が保証される。

補足（主キーが必須）: トランザクション レプリケーションでパブリッシュする
テーブルには主キーが必須である（行を一意に特定して更新を適用するため）。
主キーのないテーブルはスナップショット／マージでしか配れない。

トリガーとメタデータ テーブルを使用して変更が追跡される。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/merge/merge-replication

• 開始
  トランザクション レプリケーションと同様に、
  パブリケーションのデータベース オブジェクトとデータのスナップショットで開始される。

• 追跡
  その後にパブリッシャーとサブスクライバーで行われた
  データおよびスキーマの変更は、トリガーを使って追跡される。

• 同期
  サブスクライバーは、ネットワークに接続されたときにパブリッシャーと同期して、
  前回の同期以降にパブリッシャーとサブスクライバーの間で
  変更されたすべての行を交換する。

補足（双方向ゆえの競合解決）: マージ レプリケーションは
双方向のため、同じ行が両側で更新される競合が起きる。
既定は「パブリッシャー優先」だが、

• 優先順位ベース（サブスクライバーごとに優先度を設定）
• 列レベルの追跡（別々の列を更新した場合は競合としない）
• カスタム競合回避モジュール

といった選択肢がある。
また、テーブルに rowguid 列（uniqueidentifier + ROWGUIDCOL）が
自動的に追加される（既存になければ）点にも注意。

レプリケーションでは、
エージェントと呼ばれる多数のスタンドアロン プログラムを使用して、
変更の監視やデータの配信に関連するタスクを実行する。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/agents/replication-snapshot-agent

• 一般的にすべての種類のレプリケーションで使用される。
• ディストリビューター側で実行される。
• スナップショット エージェントは、
  • パブリッシュされたテーブルやその他のオブジェクトの
    • スキーマと初期データ ファイルを作成、
    • スナップショット ファイルを格納して、
  • 同期に関する情報をディストリビューション データベースに記録する。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/agents/replication-log-reader-agent

• ログ リーダー エージェントは、トランザクション レプリケーションで使用される。

• レプリケーションとしてマークが付けられたトランザクションを、

  • パブリッシャーのトランザクション ログから、
  • ディストリビューション データベースに移動する。

• トランザクション レプリケーションを使用してパブリッシュされた各データベースには、
  ディストリビューター上で実行され、パブリッシャーに接続する
  独自のログ リーダー エージェントがある
  (ディストリビューターはパブリッシャーと同じコンピューター上に存在していてもよい)。

補足（ログが切り捨てられなくなる）: 未読のレプリケート対象トランザクションが
ログに残っている限り、単純復旧モデルであってもログは切り捨てられない。
ログ リーダー エージェントが止まっているとログが際限なく肥大化する。
sys.databases の log_reuse_wait_desc が REPLICATION になっていたら
これを疑う（SQL Server の障害復旧参照）。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/agents/replication-distribution-agent

• ディストリビューション エージェントは、

  • スナップショット レプリケーション、
    およびトランザクション レプリケーションで使用される。

  • 初期スナップショットをサブスクライバーに適用し、
    ディストリビューション データベースに保持されている
    トランザクションをサブスクライバーに移動する。

  • プッシュ or プル

    • プッシュ サブスクリプションの場合はディストリビューターで実行され、
    • プル サブスクリプションの場合はサブスクライバーで実行される。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/agents/replication-merge-agent

• マージ エージェントは、マージ レプリケーションで使用される。

• マージ サブスクリプションごとにマージ エージェントがあり、
  パブリッシャーとサブスクライバーの両方に接続し、両方を更新する。

• マージ エージェントは、

  • 初期スナップショットをサブスクライバーに適用し、

  • データの増分変更を移動および調整する。

  • 既定では、

    • サブスクライバーからパブリッシャーに変更をアップロードし、
    • パブリッシャーからサブスクライバーに変更をダウンロードする。

  • プッシュ or プル

    • プッシュ サブスクリプションの場合はディストリビューターで実行され、
    • プル サブスクリプションの場合はサブスクライバーで実行される。

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/agents/replication-queue-reader-agent

• キュー リーダー エージェントは、
  • キュー更新オプションによるトランザクション レプリケーションで使用される。
  • ディストリビューターで実行され、サブスクライバーで行われた変更を
    パブリッシャーに戻す。
  • ディストリビューション エージェントやマージ エージェントとは異なり、
    キュー リーダー エージェントは 1 つのインスタンスだけで、
    ディストリビューション データベースのすべてのパブリッシャーおよび
    パブリケーションに対応する。

レプリケーションで配布されるデータの集合をパブリッシャ（元データの持ち主）で設定。

パブリケーション内で配布対象となる個々のオブジェクトを定義。

パブリケーションを受け取る側（サブスクライバー）で設定。

• レプリケーションには、

  • 定期的なメンテナンスや
  • 要求時メンテナンスを

  実行する多数のメンテナンス ジョブがある。

• スナップショット エージェント
  スナップショットを作成し、配布データベースに格納
• ログ リーダー エージェント
  トランザクション ログを監視し、変更を配布データベースに送信
• 配布エージェント
  配布データベースからサブスクライバーにデータを送信
• マージ エージェント
  マージ レプリケーションで変更を同期
• クリーンアップ ジョブ
  古いスナップショットや履歴データを削除してパフォーマンスを維持

データとデータベース オブジェクトのパブリッシュ
https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/publish/publish-data-and-database-objects

• パブリケーション データベースでのスキーマの変更

• パブリケーションとアーティクルの作成、変更、および削除 (レプリケーション)

  • アーティクルの定義
    • アーティクルの種類の指定 (レプリケーション Transact-SQL プログラミング)

• レプリケーション ストアド プロシージャ (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/replication-stored-procedures-transact-sql

  • sp_addarticle (Transact-SQL)
  • sp_changearticle (Transact-SQL)

• アーティクルの種類の指定 (レプリケーション Transact-SQL プログラミング)
  https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/publish/specify-article-types-replication-transact-sql-programming
  • レプリケーションにおける既定の種類のアーティクルはテーブル アーティクル。
  • ビュー、ストアド プロシージャ、ユーザー定義関数、ストアド プロシージャ実行など、
    他のデータベース オブジェクトもパブリッシュできる。

パブリケーションのサブスクライブ
https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/subscribe-to-publications

• プッシュ サブスクリプションの作成
• プル サブスクリプションのプロパティの表示または変更
• プッシュ サブスクリプションのプロパティの表示または変更
• プル サブスクリプションの削除
• プッシュ サブスクリプションの削除

• SQL Server 2005 シリーズ
  ※「管理編」にレプリケーションの自習書があります。

ログ配布は、トランザクション ログのバックアップ＆リストアの
テクノロジを使用してデータベースのレプリケーションを実現しているもよう。

「トランザクション ログ バックアップを、
"プライマリ サーバー" インスタンスの "プライマリ データベース" から、
別の "セカンダリ サーバー" インスタンスの 1 つ以上の "セカンダリ データベース" に
自動的に送信」

• 参考
  • ログ配布について (SQL Server) | Microsoft Learn
    https://learn.microsoft.com/ja-jp/sql/database-engine/log-shipping/about-log-shipping-sql-server
  • DO's&DONT's #16 やってはいけないこと\
    • ログ配布プライマリ データベースのログ バックアップ\
    • Microsoft SQL Server Japan Support Team Blog
      https://learn.microsoft.com/ja-jp/archive/blogs/jpsql/

補足（ログ配布の要点）: 3 つのジョブ（バックアップ / コピー / 復元）で
構成される、単純だが堅牢な仕組み。

• 完全復旧モデルが前提（ログ バックアップを使うため）
• 参照リンクの「やってはいけないこと」のとおり、
  ログ配布とは別にログ バックアップを取ると鎖が壊れて配布が止まる
  （SQL Server の障害復旧のログの鎖を参照）
• STANDBY モードで復元すればセカンダリを参照用に使えるが、
  復元中は接続が切断される
• フェールオーバーは手動。RPO はログ バックアップの間隔に等しい

AG より遅延は大きいが、構成が単純でネットワーク要件も緩いため、
遠隔地の災害対策としては現在も現役の選択肢である。

データベース ミラーリングは、可用性向上のための冗長化の仕組み。

• ミラーリングもトランザクション ログの転送によって実現されている。
• 読み取り専用の機能はなく、障害時フェールオーバーする。
• フェールオーバー クラスタリング (WSFC)と違って共有ディスク不要。
• SQL Server の将来のバージョンで AlwaysOn に再編され削除される予定。

※ RAID1 的、ちなみに RAID0 はシャーディング
（Elastic Scale, Elastic Database Pool）的。

（可用性グループ用のレプリケーションの構成）

こちらは、スケールアウトにも可用性向上にも対応した仕組み。
ログ転送の仕組みはトランザクション レプリケーションともログ配布とも異なる。

• 柔軟で確実な高可用性を実現する SQL Server 2012 の「AlwaysOn」機能：CodeZine
  http://codezine.jp/article/detail/6480

  • AlwaysOn 可用性グループ
    旧バージョンでは、ミラーリング機能に相当する

    • データ同期モード
      オリジナル データベースと複製するデータベースとの同期方法の選択
    • フェイル・オーバー モード
      オリジナル データベースで障害発生時に、
      複製先データベースへフェイル・オーバーする方法の選択
    • アクティブ セカンダリ
      複製したデータベースを参照専用データベースとして使用できる機能
    • セカンダリ バックアップ
      複製したデータベース上でバックアップを取得できる機能

  • AlwaysOn フェイル・オーバー クラスタ インスタンス
    旧バージョンでは、フェイル・オーバー機能に相当する。

    フェイル・オーバー クラスタリング (WSFC)の共有ディスクに代わって、
    データベース ミラーリングされたデータを使用することで、共有ディスクを使用せず
    地理的に離れた 2 つのノードのフェイル・オーバー クラスタリングを実現する。

移行メモ（正誤）: 最後の段落は AG と FCI の説明が混ざっている。
FCI は現在も共有ストレージを前提とする機能であり、
共有ストレージ無しで地理的に離れたノードを冗長化するのは
**可用性グループ（AG）**のほうである。
詳細はSQL Server のクラスタリングの補足を参照。

• トランザクション レプリケーションの基本的な仕組み at SE の雑記
  http://blog.engineer-memo.com/2014/10/13/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E3%83%AC%E3%83%97%E3%83%AA%E3%82%B1%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%81%AE%E5%9F%BA%E6%9C%AC%E7%9A%84%E3%81%AA%E4%BB%95/

• オンプレの SQL Server から AWS へレプリケーションをする – 1.基礎知識 – ｜ Developers.IO
  http://dev.classmethod.jp/cloud/aws/sqlserver-replication/

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/sql-server-replication

• レプリケーションの旧バージョンとの互換性

  • SQL Server レプリケーションの非推奨機能
  • SQL Server レプリケーションにおける重大な変更

• レプリケーション機能とタスク

  • データとデータベース オブジェクトのパブリッシュ
    • レプリケーションのパブリッシング モデルの概要

https://learn.microsoft.com/ja-jp/sql/relational-databases/replication/types-of-replication

• スナップショット レプリケーション
• トランザクション レプリケーション
• マージ レプリケーション

• Always On 可用性グループ用のレプリケーションの構成 (SQL Server)
  https://learn.microsoft.com/ja-jp/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server
• レプリケーション、変更の追跡、変更データ キャプチャ、および Always On 可用性グループ
• ログ配布とレプリケーション (SQL Server)
• データベース ミラーリングとレプリケーション (SQL Server)

• ログ配布
  https://learn.microsoft.com/ja-jp/sql/database-engine/log-shipping/about-log-shipping-sql-server

• データベース ミラーリング
  https://learn.microsoft.com/ja-jp/sql/database-engine/database-mirroring/database-mirroring-sql-server

• Always On
  https://learn.microsoft.com/ja-jp/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

---

Tags: 移行, データアクセス, SQL Server