Skip to content

MS_SQLServerReplication

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server のレプリケーション

概要

  • 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 つのインスタンスだけで、
      ディストリビューション データベースのすべてのパブリッシャーおよび
      パブリケーションに対応する。

定義

レプリケーションの定義

パブリケーション作成

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

アーティクル定義

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

サブスクリプション作成

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

レプリケーション メンテナンス ジョブ

メンテナンス ジョブとは？

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

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

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

SQL Serverの主なメンテナンス ジョブ

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

参考

パブリッシュ

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

アーティクル

サブスクライブ

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

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

自習書

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

追加機能

ログ配布

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

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

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

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

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

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

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

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

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

AlwaysOn

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

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

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

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

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

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

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

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

参考

Microsoft Learn

レプリケーション

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

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

レプリケーションおよび高可用性機能の相互運用性

追加機能

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

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally