「2拠点のSQL Serverデータベースを同時に読み書きしながら、リアルタイムにデータを同期したい」とお考えの方も多いのではないでしょうか。本記事では、SQL Server双方向トランザクションレプリケーションの計画・設定・監視・運用方法を解説します。また、運用規模が大きくなった際に管理負荷を下げる手段として、CData Syncを使った代替アプローチもあわせて紹介します。

SQL Server 双方向レプリケーションとは？基本の仕組みを解説

双方向トランザクションレプリケーションでは、2台のサーバーがパブリッシャーとサブスクライバーを相互に兼ねることで変更を交換します。各SQL Serverインスタンスはパブリッシャーとサブスクライバーの両方として機能し、どちらのサーバーで加えた変更も相手方へ伝播します。

このトポロジーは、どちらのサーバーにも読み書きができるアクティブ-アクティブ環境をサポートします。負荷分散、地域分散、および両方のデータベースが稼働し続けることが求められる障害復旧（DR）シナリオでよく採用される構成です。

双方向トランザクションレプリケーションとピアツーピアレプリケーションは混同しがちですが、別物です。ピアツーピアレプリケーションはマルチノードのアクティブ-アクティブ動作を組み込みでサポートしますが、SQL Server Enterprise Editionが必要です。双方向トランザクションレプリケーションはStandard Edition以上で動作しますが、ループ防止と競合管理について慎重な設計が求められます。

レプリケーショントポロジーの比較

双方向レプリケーション設定前に確認すべき前提条件

双方向トランザクションレプリケーションの成否を分けるのは、事前準備の質です。前提条件は必須ステップとして位置づけ、データベース変更の標準的な運用手順に組み込んでおきましょう。

データベーススキーマと主キーの統一

両方のデータベースは、同一のスキーマと主キーで開始する必要があります。テーブル、カラム、インデックス、制約、データ型がすべて一致していることを確認してください。

SQL Serverの主キーは、テーブル内の各行を一意に識別します。トランザクションレプリケーションは、主キーと一意インデックスを使って変更を正確に追跡・適用します。

IDENTITYカラムは競合の原因になりやすい点に注意が必要です。IDENTITYの範囲を慎重に計画し、必要に応じてNOT FOR REPLICATIONを設定してください。この設定により、レプリケーション中にSQL ServerがIDENTITY値を自動インクリメントするのを防ぎ、重複キーエラーを減らせます。

サーバー名とネットワーク接続の確認

各SQL Serverインスタンスは、一貫した正しいサーバー名を返す必要があります。各インスタンスで@@SERVERNAMEを実行し、完全修飾ドメイン名（FQDN）が返ることを確認してください。

サーバー間のネットワーク接続が安定していることも重要です。TCP 1433などの必要なポートを開放し、ファイアウォールルールが双方向のトラフィックを許可していることを確認します。

エディションとセキュリティ要件の確認

双方向トランザクションレプリケーションはSQL Server Standard Edition以上で動作しますが、ピアツーピアレプリケーションにはEnterprise Editionが必要です。

SQL Server エージェントアカウントは、スナップショットエージェント、ログリーダーエージェント、ディストリビューションエージェントなどのレプリケーションエージェントを実行します。このアカウントには、適切なSQL権限、ファイルシステムアクセス、ネットワーク可視性が必要です。

エージェントアカウント、repldataフォルダのアクセス権限、データベースロールを文書化したセキュリティマトリクスを整備し、社内のセキュリティポリシーに合わせておくと、監査やトラブルシューティングが格段に楽になります。

SQL Serverネイティブで双方向レプリケーションを設定する手順

SQL Serverのネイティブトランザクションレプリケーションは、きめ細かな制御とSQL Server Management Studio（SSMS）との緊密な統合を提供します。ここでは設定の大まかな流れを説明します。

ディストリビューターとパブリッシャーの設定

双方向レプリケーションでは、各サーバーがパブリッシャーとディストリビューターの両方を担います。ディストリビューターはレプリケーションのメタデータを管理し、パブリッシャーとサブスクライバー間のデータ移動を調整します。

各サーバーがローカルの変更をパブリッシュし、相手サーバーの変更をサブスクライブするようにトポロジーを設計します。シンプルな図やチェックリストを作成しておくと、クロスパブリッシュの関係を視覚化しやすくなります。

ディストリビューターロールの構成

SSMSを使ってディストリビューターロールを設定します。ローカルまたはリモートのディストリビューターを選択できますが、双方向構成では各サーバーにローカルディストリビューターを配置するのが一般的です。

トランザクションレプリケーションには、スナップショットエージェント、ログリーダーエージェント、ディストリビューションエージェントが必要です。スナップショットフォルダとディストリビューションフォルダは両サーバーからアクセスできる場所を慎重に選定してください。

両サーバーでのパブリケーション作成

レプリケーション対象のテーブルやオブジェクトに対して、各サーバーにパブリケーションを作成します。SSMSを使ってトランザクションパブリケーションを定義し、アーティクルを選択します。

レプリケーションが必要なテーブルの明確なリストを用意し、データガバナンス基準と照らし合わせて検証しましょう。必要がない参照専用テーブルはパブリッシュを避けてください。

アーティクルの定義と管理

アーティクルとは、パブリケーションに含める特定のテーブルやオブジェクトのことです。対称性と予測可能な動作を確保するため、両サーバーは同じアーティクルセットをパブリッシュする必要があります。

すべてのアーティクルに主キーまたは適切な一意インデックスが設定されていることを確認してください。レプリケーションエージェントは、挿入・更新・削除の際にこれらのキーを使って行を識別します。

サーバー間でアーティクル定義が完全に一致しているか確認するため、社内のテンプレートやチェックリストを活用しているチームも多くあります。

双方向パブリケーションの確立

ネイティブの双方向レプリケーションはピアツーピアレプリケーションとは異なります。SQL ServerはStandard Editionシナリオでマルチノードの競合解決を自動管理しません。

手動による双方向トランザクションレプリケーションでは、ループ防止と競合計画を慎重に設計する必要があります。Enterprise Edition専用のピアツーピアレプリケーションは、組み込みのアクティブ-アクティブ動作でこの複雑さを解消します。

ループバック検出を有効にしたサブスクリプションの追加

サブスクリプションを追加する際は、ループバック検出を有効にしてください。@loopback_detection = TRUEを設定すると、サブスクライバーに届いた変更が元のパブリッシャーに再パブリッシュされるのを防止できます。

ループバック検出は、同じ変更がサーバー間を無限に行き来するレプリケーションループを防ぎます。レプリケーションパートナーから発信された変更を無視することで機能します。

SQL Server エージェントアカウントへの権限付与

各SQL Server エージェントアカウントに、相手サーバーのrepldataフォルダへのアクセス権を付与し、レプリケーションエージェントがスナップショットファイルとディストリビューションファイルを読み書きできるようにします。

SSMSのパブリケーションアクセスリスト（PAL）に関連するアカウントを追加してください。権限の設定ミスは、レプリケーションの無音障害やエージェントの停止につながることがあります。

レプリケーションの初期化と検証

トランザクションレプリケーションは、既存データのスナップショットから始まり、その後の変更をレプリケーションエージェントを通じてミラーリングします。

ゼロダウンタイムが必須の場合は、サブスクリプションを初期化する前に両サーバーへ同一のデータをあらかじめ投入しておきましょう。この方法でスナップショット時間と本番環境への影響を最小化できます。

Replication MonitorまたはSSMSのView Synchronization Statusオプションを使って、初期化の成功と継続的な健全性を確認してください。

双方向レプリケーションの監視と維持

双方向レプリケーションは、データの整合性とパフォーマンスを維持するために継続的な管理が必要です。

レプリケーションの遅延、同期ステータス、データドリフトなどのメトリクスを追跡しましょう。ネイティブの可視性にはSQL Server Replication Monitorを活用し、集中監視には外部のダッシュボードツールの導入も検討してください。行数、チェックサム、照合クエリを使ってデータの整合性を定期的に検証することも重要です。

本番環境以外でも競合シナリオをテストし、再初期化手順を含む復旧手順を文書化しておきましょう。

CData Syncを使ったSQL Server双方向レプリケーション

SQL Serverのネイティブレプリケーションは強力ですが、大規模になると管理が複雑になります。CData Syncは、独立した変更ベースのジョブと集中ガバナンスを使った、双方向レプリケーションを簡素化する代替アプローチを提供します。変更データキャプチャ（CDC）の仕組みと各種手法についても、あわせてご確認ください。

接続の設定

CData Syncで各SQL Serverインスタンスの接続を追加します。設定時に認証情報 (クレデンシャル)、ネットワーク接続性、スキーマの可視性を検証してください。

CData Syncは増分変更の追跡に主キーを使用するため、レプリケーション対象テーブルに主キーが設定されていることを確認してください。

双方向レプリケーションジョブの設定

CData Syncは2つの独立したジョブで双方向レプリケーションを実現します。

1. サーバーAからサーバーBへのデータレプリケーションジョブを作成します。

2. サーバーBからサーバーAへのレプリケーションジョブを別途作成します。

3. 各ジョブでテーブルを選択し、変更検出に使用するキーを確認します。

4. レイテンシとワークロードの要件に応じて、スケジュール実行または継続実行を設定します。

双方向の同期を達成するには両方のジョブの実行が必要です。各ジョブが単一のレプリケーション方向を担うため、トラブルシューティングとスケジューリングが容易になります。

変更キャプチャ動作の設定

CData Syncは、利用可能な場合はSQL Serverの変更追跡（Change Tracking）または変更データキャプチャ（CDC）を使用して増分変更を識別します。

負荷を軽減してフル更新を避けるため、増分レプリケーションを有効にしてください。競合リスクを最小化するために、両レプリケーション方向で変更キャプチャ設定を一致させておくことが重要です。

オプションの変換とコントロールの設定

CData Syncはガバナンスを強化し競合を減らすフィルター、マッピング、変換をサポートしています。

テーブルまたはカラムフィルターを適用してレプリケーションデータを絞り込めます。スキーマが異なる場合はカラムマッピングや変換を活用してください。業務上不要な参照データは除外するのが得策です。

CData Syncコンソールでジョブの実行状況、エラー、同期ステータスを一元管理できるため、分散チームの運用が大幅に効率化されます。

双方向レプリケーションの一般的な問題とトラブルシューティング

双方向レプリケーションは単方向構成より複雑であるため、固有の問題が発生しやすくなります。

よくある問題としては、サブスクリプションが表示されない、エージェントのタイムアウト、権限エラー、主キーやFQDNの不一致などが挙げられます。トラブルシューティングの際は、サーバー名、セキュリティ設定、エージェントジョブの履歴を確認してください。

マスター-マスターレプリケーションには適切なオーケストレーションが必要です。設計が不十分だと競合が発生し、データの整合性に影響します。SQL Serverレプリケーション設定完全ガイドも参考にしてください。

よくある質問（SQL Server 双方向レプリケーション）

SQL Server 双方向トランザクションレプリケーションの主な前提条件は何ですか？

双方向トランザクションレプリケーションを設定するには、両サーバーが互換性のあるSQL Serverエディションを使用していること、スキーマと主キーが同一であること、必要なポートが開放されセキュリティ権限が適切に設定されたネットワーク環境が整っていることが必要です。

双方向レプリケーションでIDENTITYカラムの競合を防ぐには？

IDENTITYカラムにNOT FOR REPLICATIONを設定し、IDENTITYの範囲を分割するかサロゲートキーを使用することで、双方向同期中の競合やデータ重複のリスクを最小化できます。

レプリケーションの遅延とエージェントエラーはどう対処しますか？

Replication Monitorでレプリケーション遅延を監視し、必要に応じてエージェントジョブのタイムアウトを延長してください。エージェントエラーはネットワーク接続性、権限、エージェントのステータスを確認することで解決できます。

クラウドマネージドSQL Serverインスタンスで双方向トランザクションレプリケーションは使えますか？

Azure SQL Managed Instanceでは、ネットワークと必要なポートが適切に設定されていれば双方向トランザクションレプリケーションをサポートしています。

双方向レプリケーションを効果的にテストするには？

両方のサーバーで挿入・更新・削除操作を実行し、変更が相手のサーバーに正しく反映されることを確認してください。

CData Syncでセキュアかつスケーラブルな双方向SQL Serverレプリケーションを構築する

双方向トランザクションレプリケーションは、高可用性と運用スケールを支えるアクティブ-アクティブのSQL Serverアーキテクチャを実現します。しかし、ガバナンスなき複雑さはリスクをもたらします。CData Syncのコネクション数の考え方を把握しておくと、ライセンス計画にも役立ちます。

CData Syncは、予測可能な双方向SQL Serverレプリケーションのためのセキュアでスケーラブルな基盤を提供し、チーム全体がリアルタイムデータを同期しながらコントロール、コンプライアンス、運用のシンプルさを維持するのを支援します。今すぐ14日間無償トライアルをお試しください。