Info2softは、ウェブサイトでより快適で適切な閲覧体験を提供するためにCookieを使用しています。 プライバシーポリシー
Loading...
著者:
Microsoft SQL Server 常時ON可用性グループ（AG）は基幹業務データベースに対し、高可用性と災害復旧機能を提供します。同時にSQL Serverレプリケーションは、データ配信、レポート処理の負荷分散、差分同期、複数サーバー間のデータ統合に対応します。
多くの企業環境ではこの2つの技術を組み合わせて利用し、レプリカのフェイルオーバーが発生した場合でも下流のサブスクライバーに影響を与えず稼働を継続する、耐障害性の高いデータパイプラインを構築しています。
しかし、可用性グループとレプリケーションの仕組みには標準で制限事項が存在するため、データベース管理者は多くの課題に直面します。既定の設定ではAG上のデータベースをレプリケーションのパブリッシャーとして直接構成することはできません。適切な設定を行わない場合、自動または手動フェイルオーバー後にレプリケーションが完全に停止する恐れがあります。
本ガイドでは常時ON可用性グループと連携したレプリケーションの構成手順を解説します。事前要件、詳細な設定手順、運用時に発生する一般的なトラブルを記載しています。
作業を開始する前に、常時ON AGとSQL Serverレプリケーションの対応シナリオおよび制限事項を確認してください。
対応しているレプリケーション種別：
非対応・互換性のないレプリケーション構成：
MicrosoftはAG上でホストされたデータベースでは以下のレプリケーショントポロジを明示的に制限しています。
アーキテクチャ上の重要ルール：
常時ON AGで要件を満たせる場合、本記事ではより簡単で高性能な代替レプリケーションソリューションも紹介します。
設定作業を開始する前に、インフラ、エディション、設定に関するすべての事前要件を満たしていることを確認してください。
1. 可用性グループのすべてのレプリカノードにSQL Server Enterpriseエディションが必要です。 AGとレプリケーションの基本的な連携機能はSQL Server 2016以降で完全サポートされています。可用性グループによるディストリビューションデータベースの高可用性は、SQL Server 2016 SP2 CU3、SQL Server 2017 CU6、およびそれ以降のすべてのバージョンで対応しています。
2. AGが完全に構成済みで、プライマリ・セカンダリレプリカの状態が正常、ユーザーデータベースの同期が完了し、未解消のAG健全性警告が存在しないこと。
3. 可用性グループクラスターのすべてのノードにSQL Serverレプリケーション機能をインストールする必要があります。プライマリレプリカだけでは不十分です。フェイルオーバーでパブリッシャーとなる可能性のあるすべてのノードで、レプリケーションエージェントとメタデータの実行ファイルを統一する必要があります。
4. 本番環境ではスタンドアロン型のリモートSQL Serverディストリビューターを強く推奨します。ローカルディストリビューターも利用可能ですが、高可用性の分離設計ができず、運用の複雑度が上昇します。ディストリビューターはすべてのレプリケーションメタデータ、エージェントジョブ、トランザクションログ配信を管理します。
5. 本連携機能にはDNS名の可用性グループリスナーが必須です。リスナーはAGフェイルオーバー時に変更されない固定のネットワークエンドポイントを提供し、透過的なパブリッシャーリダイレクトを実現します。
6. セキュリティとネットワーク権限を正しく設定する必要があります。
7. AG配下のユーザーデータベースは完全復旧モデル(FULL)に設定し、トランザクションレプリケーションのログ読み取りに対応するためトランザクションログバックアップを構成する必要があります。
ご利用のSQL Server環境で以下の手順を実施してください。
ディストリビューターはレプリケーションの基盤となるコンポーネントで、メタデータとトランザクションコマンドを保管するディストリビューションデータベースを保持します。以下にディストリビューターとディストリビューションDBの設定方法を記載します。
►オプションA：スタンドアロンディストリビューター（全対応バージョン）
1. SQL Server管理ツール(SSMS)からディストリビューターインスタンスに接続します。
2. レプリケーションを右クリックし、「ディストリビューションの構成」を選択します。
3. ウィザードに従い、当該インスタンスを自身のディストリビューターとして設定、スナップショットフォルダを指定し、ディストリビューションデータベースを作成します。
4. T-SQLを使用する場合は以下のコードを実行します。
USE master;
GO
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = N'StrongPassword!';
GO
EXEC sp_adddistributiondb @database = N'distribution';
GO
►オプションB：可用性グループ上のディストリビューションDB（SQL Server 2016 SP2-CU3以降 / 2017 CU6以降）
ディストリビューター側に高可用性が必要な場合は、ディストリビューションデータベースを専用のAGに配置できます。本構成は慎重な設定が必要ですが、最重要要件としてパブリッシャーとディストリビューターを別々のSQL Serverインスタンスに分ける必要があります。また、ディストリビューションDB用AGに専用のリスナーを作成する必要があります。
ディストリビューターの準備が完了したら、現在のプライマリレプリカ上でデータベースをパブリケーション対象として設定します。
1. プライマリレプリカインスタンスに接続します。
2. データベースをトランザクションレプリケーション有効化します。
USE [YourDatabaseName];
GO
EXEC sp_replicationdboption
@dbname = N'YourDatabaseName',
@optname = N'publish',
@value = N'true';
GO
3. パブリケーションを作成します。重要：この段階ではAGリスナーではなく、プライマリレプリカの物理サーバー名を使用します。リスナーへのリダイレクトは後の手順で実施します。
USE [YourDatabaseName];
GO
EXEC sp_addpublication
@publication = N'YourPublicationName',
@description = N'Transactional publication of database.',
@sync_method = N'concurrent',
@retention = 336,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'true',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false';
GO
4. パブリケーションにアーティクル（テーブル、ビューなど）を追加します。
USE [YourDatabaseName];
GO
EXEC sp_addarticle
@publication = N'YourPublicationName',
@article = N'YourTableName',
@source_owner = N'dbo',
@source_object = N'YourTableName',
@type = N'logbased',
@description = NULL,
@creation_script = NULL,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'YourTableName',
@destination_owner = N'dbo',
@status = 24;
GO
— レプリケーション対象のテーブルごとに上記コマンドを実行してください
フェイルオーバー後にプライマリとなる可能性のあるすべてのセカンダリレプリカは、パブリケーションをホストできるよう適切に設定する必要があります。
各セカンダリレプリカホストで以下の作業を実施します。
1. ディストリビューターを追加（未登録の場合）：
USE master;
GO
EXEC sp_adddistributor
@distributor = N'YourDistributorServerName',
@password = N'StrongPassword!';
GO
2. 各セカンダリレプリカをディストリビューターに登録します。ディストリビューターインスタンス上で実行します。
USE distribution;
GO
EXEC sp_adddistpublisher
@publisher = N'SecondaryReplicaHostName', -- セカンダリレプリカの物理サーバー名
@distribution_db = N'distribution',
@working_directory = N'\\NetworkShare\ReplData',
@security_mode = 1, -- 1 = Windows認証
@trusted = N'false',
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER';
GO
— パブリッシャー候補となるすべてのセカンダリレプリカごとに実行
3. プッシュサブスクリプションに必要なため、各セカンダリレプリカ上でサブスクライバーをリンクサーバーとして登録します。
— 各セカンダリレプリカで実行
EXEC sp_addlinkedserver
@server = N'SubscriberServerName',
@srvproduct = N'SQL Server';
GO
本手順でセカンダリレプリカ側の設定を完了させ、フェイルオーバー時に完全にパブリッシャーとして動作できる状態にします。
各セカンダリレプリカホストで以下を実行します。
-- 本手順でセカンダリ側にパブリケーション情報を登録します
USE [YourDatabaseName];
GO
EXEC sp_replicationdboption
@dbname = N'YourDatabaseName',
@optname = N'publish',
@value = N'true';
GO
注記：セカンダリ側でパブリケーションを再作成しないでください。パブリケーションはデータベース内に既に存在しており、当該DBがプライマリに昇格した際に自動的に認識されます。
シームレスなフェイルオーバーを実現するための最重要手順です。ディストリビューターに対し、元のパブリッシャーに接続できない場合はAGリスナーに接続するよう通知します。
ディストリビューターインスタンスのdistributionデータベース上で実行します。
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = N'PhysicalPrimaryServerName', -- 元のプライマリホスト名
@publisher_db = N'YourDatabaseName',
@redirected_publisher = N'YourAGListenerName'; -- 既定以外のポートを使用する場合は「リスナー名,2233」の形式で記載可
GO
-- リダイレクト設定の検証
USE distribution;
GO
SELECT * FROM MSredirected_publishers;
GO
サブスクリプションを作成する前に、Microsoftはすべてのレプリカがパブリッシャーとして正常に設定されているか検証することを推奨しています。
ディストリビューター上で以下の検証コマンドを実行します。
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = N'PhysicalPrimaryServerName',
@publisher_db = N'YourDatabaseName',
@redirected_publisher = @redirected_publisher OUTPUT;
GO
検証でエラーが返される場合は、前工程を再確認し、すべてのセカンダリレプリカがディストリビューターに正しく登録され、パブリッシュ対象DBとして設定されているか確認してください。
ここでサブスクリプションを作成します。AGリスナーの構成にSSMSのウィザードは正しく対応できないため、T-SQLスクリプトを使用する必要があります。
►オプションA：プッシュサブスクリプション（AG配下サブスクライバーに推奨）
パブリッシャー側でプッシュサブスクリプションを作成します。サブスクライバーDBがAG配下の場合はサブスクライバーのAGリスナー名、そうでない場合は物理サーバー名を指定します。
-- パブリッシャー（プライマリレプリカ）上で実行
GO
-- サブスクリプションの追加
EXEC sp_addsubscription
@publication = N'YourPublicationName',
@subscriber = N'SubscriberAGListenerName', -- サブスクライバーがAG配下の場合はリスナー名を使用
@destination_db = N'SubscriberDatabaseName',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0;
GO
-- ディストリビューションエージェントジョブの追加
EXEC sp_addpushsubscription_agent
@publication = N'YourPublicationName',
@subscriber = N'SubscriberAGListenerName',
@subscriber_db = N'SubscriberDatabaseName',
@job_login = N'DOMAIN\ReplAgentAccount',
@publication = N'YourPublicationName',
@job_password = N'StrongPassword!',
@subscriber_security_mode = 1, -- 1 = Windows認証
@frequency_type = 64, -- 64 = 常時実行
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20240101,
@active_end_date = 99991231,
@dts_package_location = N'Distributor';
GO
►オプションB：プルサブスクリプション
プルサブスクリプションはサブスクライバー側で作成します。パブリッシャー側にはAGリスナー名を指定します。
-- サブスクライバー上で実行
USE [SubscriberDatabaseName];
GO
-- プルサブスクリプションの追加
EXEC sp_addpullsubscription
@publisher = N'YourAGListenerName', -- パブリッシャー側AGリスナー
@publication = N'YourPublicationName',
@publisher_db = N'YourDatabaseName',
@independent_agent = N'True';
GO
-- サブスクライバー側にディストリビューションエージェントジョブを追加
EXEC sp_addpullsubscription_agent
@publisher = N'YourAGListenerName',
@publisher_db = N'YourDatabaseName',
@publication = N'YourPublicationName',
@distributor = N'YourDistributorServerName',
@distributor_security_mode = 1,
@distributor_login = N'',
@distributor_password = N'',
@enabled_for_syncmgr = N'False',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20240101,
@active_end_date = 99991231,
@alt_snapshot_folder = N'',
@working_directory = N'';
GO
サブスクライバーDBがAG配下の状態でプルサブスクリプションを作成すると、ディストリビューションエージェントジョブはサブスクライバーAGの現在のプライマリレプリカにのみ作成されます。元のサーバーが稼働し続けている場合、サブスクライバーAGのフェイルオーバー後もレプリケーションは自動的に継続されます（ジョブは旧プライマリ上で実行され続けます）。
ただし長期的な安定運用のため、旧プライマリのジョブを無効化し、新しいプライマリにジョブを作成・有効化する必要があります。詳細は「フェイルオーバー後の運用管理」の項目を参照してください。
サブスクライバーの初期同期用スナップショットを生成します。
-- パブリッシャー（プライマリレプリカ）上で実行
USE [YourDatabaseName];
GO
EXEC sp_startpublication_snapshot @publication = N'YourPublicationName';
GO
スナップショットの生成・配信状況はレプリケーションモニター、またはdistributionデータベースのMSsnapshot_historyテーブルを照会して監視できます。
監視を容易にするため、レプリケーションモニターに元のパブリッシャーを追加します。
1. SSMSからディストリビューターインスタンスに接続します。
2. レプリケーションを展開し、レプリケーションモニターを右クリック、「パブリッシャーの追加」を選択します。
3. パブリッシャーの元の物理サーバー名を追加します。レプリケーションモニターは自動的にリダイレクト先のパブリッシャーを検知し、正しい情報を表示します。
注記：フェイルオーバー後もレプリケーションモニターには元のプライマリ名が表示され続ける場合がありますが、レプリケーション機能に影響はありません。
常時ON可用性グループは高可用性を実現できますが、可用性グループとレプリケーションを連携させる設定作業は複雑です。読み書き分離、可視化レポート、データ配信による異種DB統合などの要件がある場合は、専用のデータベースレプリケーションソリューションをご検討ください。
i2Streamは企業向けの代替ソリューションで、純粋な高可用性以外の用途に常時ON AGを拡張する際の制限事項を解消します。SQL Server Enterpriseエディションは不要で、Windowsクラスターにも依存しません。
i2Streamの主な特長・メリット：
下部の無料トライアルボタンをクリックしてください。弊社より速やかにご連絡し、お客様の環境への導入手順と使用方法をご案内いたします。
症状：AGフェイルオーバー後、ログリーダーエージェントまたはディストリビューションエージェントが接続エラーで異常終了する。
主な原因：
解決策：
症状：トランザクションのレプリケーションが失敗し、未コミットトランザクションに関するエラーが出力される。
主な原因：ディストリビューションDBの同期状態が異常、またはAG同期時に競合が発生している。
解決策：
症状：DBがAG配下の状態でスナップショットエージェントが「エージェントの状態を取得できません」などのエラーを出力する。
主な原因：レプリケーション設定完了前にDBをAGに追加した、またはSQL Server 2022のコンテインド可用性グループを使用している。
解決策：
症状：フェイルオーバー後、ログリーダーエージェントが旧プライマリインスタンスへの接続を試行し続ける。
主な原因：sp_redirect_publisher を実行するDBが間違っている、またはパラメータが不正。
解決策：
1. ディストリビューターのdistributionDB上でリダイレクト設定を確認：
SELECT * FROM MSredirected_publishers;
2. 設定が不正な場合はリダイレクトを再作成：
EXEC sys.sp_redirect_publisher
@original_publisher = N'PhysicalServerName',
@publisher_db = N'YourDatabase',
@redirected_publisher = N'AGListenerName';
3. ログリーダーエージェントジョブを再起動
現象：フェイルオーバー後、レプリケーションモニターに元のプライマリインスタンス名でレプリケーション情報が表示され続けます。
解説：この挙動は既知の仕様です。フェイルオーバー後、レプリケーションモニターはパブリッシュインスタンス名を更新できず、元のプライマリ名のまま表示します。
対応策：画面表示上の不具合のみで、レプリケーション機能に影響はありません。T-SQLにより新しいパブリッシャーに登録したトレーサートークンはレプリケーションモニター上で確認可能です。
Always On可用性グループとレプリケーションを組み合わせて構成することで、SQL Server基盤を単なる高可用性ソリューションから、内蔵レジリエンスを備えた総合的なデータ配信プラットフォームへと変換できます。
より簡単でエンタープライズ水準のデータベースレプリケーションソリューションをご希望の場合はi2Streamが適切です。非侵入型CDCとエージェントレスアーキテクチャにより、異なるデータベース基盤間でデータを継続的に複製できます。
