可用性グループ内に両方のピアを構成する
SQL Server 2019 (15.x) CU 13 以降、SQL Server Always On 可用性グループに属するデータベースは、ピアツーピア トランザクション レプリケーション トポロジにピアとして参加できます。 この記事では、このシナリオを 2 つのピア (それぞれが独自の可用性グループ内にある) を使用して構成する方法について説明します。
この例のスクリプトでは、T-SQL ストアド プロシージャを使用します。
ロールと名前
このセクションでは、この記事のレプリケーション トポロジに参加するさまざまな要素のロールと名前について説明します。
Peer1
- Node1: 1 番目の可用性グループのプライマリ レプリカ
- Node2: 1 番目の可用性グループのセカンダリ レプリカ
- MyAG: 1 番目の可用性グループの可用性グループ名
- MyDBName: Peer1 データベース。 パブリッシュするデータベース
- Dist1: リモート ディストリビューター
- P2P_MyDBName: パブリケーション名
- MyAGListenerName: 可用性グループ リスナー
Peer2
- Node3: 2 番目の可用性グループのプライマリ レプリカ
- Node4: 2 番目の可用性グループのセカンダリ レプリカ
- MyAG2: 2 番目の可用性グループ名の可用性グループ名
- MyDBName: パブリッシュするデータベース
- Dist2: リモート ディストリビューター
- P2P_MyDBName: パブリケーション名
- MyAG2ListenerName: 可用性グループ リスナー
前提条件
別々の物理サーバーまたは仮想サーバー上で、可用性グループをホストする SQL Server の 4 つのインスタンス。 2 つの可用性グループそれぞれにピア データベースを含めます。
ディストリビューター データベースをホストする SQL Server の 2 つのインスタンス。
すべてのサーバー インスタンスで、サポート対象エディション (Enterprise Edition または Developer Edition) が必要です。
すべてのサーバー インスタンスで、サポート対象バージョン SQL Server 2019 (15.x) CU13 以降が必要です。
すべてのインスタンス間で十分なネットワーク接続と帯域幅。
SQL Server のすべてのインスタンスに SQL Server レプリケーションをインストールします。
レプリケーションがインスタンスにインストールされているかどうかを確認するには、次のクエリを実行します。
USE master; GO DECLARE @installed int; EXEC @installed = sys.sp_MS_replication_installed; SELECT @installed;Note
ディストリビューション データベースの単一障害点を回避するには、ピアごとにリモート ディストリビューターを使用します。
デモンストレーションまたはテスト環境の場合は、単一のインスタンスにディストリビューション データベースを構成することができます。
ディストリビューターとリモート パブリッシャーを構成する (Peer1)
このセクションでは、可用性グループの 1 番目のピア (Peer1) を設定する方法について説明します。
sp_adddistributorを実行して、Dist1 でディストリビューションを構成します。@password =を使用して、リモート パブリッシャーでディストリビューターへの接続に使用されるパスワードを指定します。 このパスワードは、リモート ディストリビューターを構成するときに各リモート パブリッシャーで使用します。USE master; GO EXEC sys.sp_adddistributor @distributor = 'Dist1', @password = '<Strong password for distributor>';ディストリビューター側のディストリビューション データベースを作成します。
USE master; GO EXEC sys.sp_adddistributiondb @database = 'distribution', @security_mode = 1;Node1 および Node2 リモート パブリッシャーを構成します。
@security_modeを使用して、レプリケーション エージェントが現在のプライマリに接続する方法を決定します。1= Windows 認証。0= SQL Server 認証。@loginと@passwordが必要です。 このログインとパスワードは、各セカンダリ レプリカで有効である必要があります。
Note
変更されたレプリケーション エージェントをディストリビューター以外のコンピューターで実行する場合、プライマリへの接続に Windows 認証を使用するには、レプリカのホスト コンピューター間の通信に Kerberos 認証が必要です。 現在のプライマリへの接続に SQL Server ログインを使用する場合は、Kerberos 認証は必要ありません。
USE master; GO EXEC sys.sp_adddistpublisher @publisher = 'Node1', @distribution_db = 'distribution', @working_directory = '\\MyReplShare\WorkingDir', @security_mode = 1 USE master; GO EXEC sys.sp_adddistpublisher @publisher = 'Node2', @distribution_db = 'distribution', @working_directory = '\\MyReplShare\WorkingDir', @security_mode = 1
元のパブリッシャーでパブリッシャーを構成する (Node1)
リモート ディストリビューションの元のパブリッシャーを構成します (Node1)。
@passwordには、ディストリビューターでディストリビューションを設定するためにsp_adddistributorを実行したときと同じ値を指定します。EXEC sys.sp_adddistributor @distributor = 'Dist1', @password = '<Password used when running sp_adddistributor on distributor server>'データベースでレプリケーションを有効にします。
USE master; GO EXEC sys.sp_replicationdboption @dbname = 'MyDBName', @optname = 'publish', @value = 'true';
セカンダリ レプリカのホストをレプリケーションのパブリッシャーとして構成する (Node2)
1 番目の可用性グループのセカンダリ レプリカの各ホスト (Node2) で、ディストリビューションを構成します。 @password には、ディストリビューターでディストリビューションを設定するために sp_adddistributor を実行したときと同じ値を指定します。
EXEC sys.sp_adddistributor
@distributor = 'Dist1',
@password = '<Password used when running sp_adddistributor on distributor server>'
データベースを可用性グループの一部とし、リスナーを作成する (Peer1)
目的のプライマリ レプリカで、データベースをメンバー データベースとして使用して可用性グループを作成します。
可用性グループの DNS リスナーを作成します。 このリスナーを使用して、レプリケーション エージェントが、現在のプライマリ レプリカに接続されます。 次の例では、
MyAGListenerNameという名前のリスナーを作成します。ALTER AVAILABILITY GROUP 'MyAG' ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));Note
上記のスクリプトの角かっこ (
[ ... ]) 内の情報は省略可能です。 これは、TCP ポートに既定以外の値を指定する場合に使用します。 かっこは含めないでください。
元のパブリッシャーを AG リスナー名にリダイレクトする (Peer1)
Peer1 のディストリビューターで、元のパブリッシャーを AG リスナー名にリダイレクトします。
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'Node1',
@publisher_db = 'MyDBName',
@redirected_publisher = 'MyAGListenerName,<port>';
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。 山かっこ <> は含めないでください。
元のパブリッシャーでピアツーピア パブリケーション (Peer1) を作成する - Node1
次のスクリプトでは、Peer1 のパブリケーションを作成します。
EXEC master..sp_replicationdboption @dbname= 'MyDBName'
,@optname= 'publish'
,@value= 'true'
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node1'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 100
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO
DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object,
@source_owner = @source_owner, @type = @type
GO
ピアツーピア パブリケーションが可用性グループと互換性を持つようにする (Peer1)
元のパブリッシャー (Node1) で、次のスクリプトを実行して、パブリケーションが可用性グループと互換性を持つようにします。
USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName'
DECLARE @property sysname = N'redirected_publisher'
DECLARE @value sysname = N'MyAGListenerName,<port>'
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value
GO
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。
上記の手順を完了すると、この可用性グループはピアツーピア トポロジに参加する準備が整います。 次の手順では、ピア ツー ピア レプリケーション トポロジの 2 番目のピア (Peer2) として、別の可用性グループを構成します。
ディストリビューターとリモート パブリッシャーを構成する (Peer2)
このセクションでは、別の可用性グループの 2 番目のピア (Peer2) を設定する方法について説明します。
sp_adddistributorを実行して、Dist2 でディストリビューションを構成します。@password =を使用して、リモート パブリッシャーでディストリビューターへの接続に使用されるパスワードを指定します。 このパスワードは、リモート ディストリビューターを構成するときに各リモート パブリッシャーで使用します。USE master; GO EXEC sys.sp_adddistributor @distributor = 'Dist2', @password = '<Strong password for distributor>';ディストリビューター側のディストリビューション データベースを作成します。
USE master; GO EXEC sys.sp_adddistributiondb @database = 'distribution', @security_mode = 1;Node3 および Node4 リモート パブリッシャーを構成します。
@security_modeを使用して、レプリケーション エージェントが現在のプライマリに接続する方法を決定します。1= Windows 認証。0= SQL Server 認証。@loginと@passwordが必要です。 このログインとパスワードは、各セカンダリ レプリカで有効である必要があります。
Note
変更されたレプリケーション エージェントをディストリビューター以外のコンピューターで実行する場合、プライマリへの接続に Windows 認証を使用するには、レプリカのホスト コンピューター間の通信に Kerberos 認証が必要です。 現在のプライマリへの接続に SQL Server ログインを使用する場合は、Kerberos 認証は必要ありません。
USE master; GO EXEC sys.sp_adddistpublisher @publisher = 'Node3', @distribution_db = 'distribution', @working_directory = '\\MyReplShare\WorkingDir2', @security_mode = 1 USE master; GO EXEC sys.sp_adddistpublisher @publisher = 'Node4', @distribution_db = 'distribution', @working_directory = '\\MyReplShare\WorkingDir2', @security_mode = 1
パブリッシャーを構成する (Peer2)
(Node3) で、リモート ディストリビューションを構成します。
@passwordには、ディストリビューターでディストリビューションを設定するためにsp_adddistributorを実行したときと同じ値を指定します。EXEC sys.sp_adddistributor @distributor = 'Dist2', @password = '<Password used when running sp_adddistributor on distributor server>'データベースでレプリケーションを有効にします。
USE master; GO EXEC sys.sp_replicationdboption @dbname = 'MyDBName', @optname = 'publish', @value = 'true';
セカンダリ レプリカのホストをレプリケーションのパブリッシャーとして構成する (Node4)
2 番目の可用性グループのセカンダリ レプリカの各ホスト (Node4) で、ディストリビューションを構成します。 @password には、ディストリビューターでディストリビューションを設定するために sp_adddistributor を実行したときと同じ値を指定します。
EXEC sys.sp_adddistributor
@distributor = 'Dist2',
@password = '<Password used when running sp_adddistributor on distributor server>'
データベースを可用性グループの一部とし、リスナーを作成する (Peer2)
目的のプライマリ レプリカで、データベースをメンバー データベースとして使用して可用性グループを作成します。
可用性グループの DNS リスナーを作成します。 このリスナーを使用して、レプリケーション エージェントが、現在のプライマリ レプリカに接続されます。 次の例では、
MyAG2ListenerNameという名前のリスナーを作成します。ALTER AVAILABILITY GROUP 'MyAG2' ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));Note
上記のスクリプトの角かっこ (
[ ... ]) 内の情報は省略可能です。 これは、TCP ポートに既定以外の値を指定する場合に使用します。 かっこは含めないでください。
元のパブリッシャーを AG リスナー名にリダイレクトする (Peer2)
Peer2 のディストリビューターで、元のパブリッシャーを AG リスナー名にリダイレクトします。
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'Node3',
@publisher_db = 'MyDBName',
@redirected_publisher = 'MyAG2ListenerName,<port>';
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。 山かっこ <> は含めないでください。
ピアツーピア パブリケーションを作成する (Peer2)
次のスクリプトでは、Peer2 のパブリケーションを作成します。
Node3 で、次のコマンドを実行して、ピアツーピア パブリケーションを作成します。
EXEC master..sp_replicationdboption @dbname= 'MyDBName'
,@optname= 'publish'
,@value= 'true'
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO
-- Note – Make sure that the value for @p2p_originator_id is different from Peer1.
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node3'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 1
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO
DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object,
@source_owner = @source_owner, @type = @type
GO
ピアツーピア パブリケーションが可用性グループと互換性を持つようにする (Peer2)
元のパブリッシャー (Node3) で、次のスクリプトを実行して、パブリケーションが可用性グループと互換性を持つようにします。
USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName'
DECLARE @property sysname = N'redirected_publisher'
DECLARE @value sysname = N'MyAG2ListenerName,<port>'
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value
GO
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。
Peer1 から Peer2 の可用性グループ リスナーへのプッシュ サブスクリプションを作成する
Peer1 から可用性グループ リスナー Peer2 へのプッシュ サブスクリプションを作成するには、Node1 で次のコマンドを実行します。
Node1 で次のスクリプトを実行します。 これは、Node1 がプライマリ レプリカを実行していることを前提としています。
重要
次のスクリプトでは、サブスクライバーの可用性グループ リスナー名を指定します。
@subscriber = N'MyAGListenerName,<port>'
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。 山かっこ <> は含めないでください。
EXEC [MyDBName].dbo.sp_addsubscription
@publication = N'P2P_MyDBName'
, @subscriber = N'MyAG2Listener,<port>'
, @destination_db = N'MyDBName'
, @subscription_type = N'push'
, @sync_type = N'replication support only'
GO
EXEC [MyDBName].dbo.sp_addpushsubscription_agent
@publication = N'P2P_MyDBName'
, @subscriber = N'MyAG2Listener,<port>'
, @subscriber_db = N'MyDBName'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 1
, @frequency_type = 64
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 4
, @frequency_subday_interval = 5
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @dts_package_location = N'Distributor'
GO
Peer2 から可用性グループ リスナー (Peer1) へのプッシュ サブスクリプションを作成する
Peer2 から可用性グループ リスナー (Peer1) へのプッシュ サブスクリプションを作成するには、Node3 で次のコマンドを実行します。
重要
次のスクリプトでは、サブスクライバーの可用性グループ リスナー名を指定します。
@subscriber = N'MyAGListenerName,<port>'
Note
上記のスクリプトの ,<port> は省略可能です。 これは、既定以外のポートを使用する場合にのみ必要です。 山かっこ <> は含めないでください。
EXEC [MyDBName].dbo.sp_addsubscription
@publication = N'P2P_MyDBName'
, @subscriber = N'MyAGListenerName,<port>'
, @destination_db = N'MyDBName'
, @subscription_type = N'push'
, @sync_type = N'replication support only'
GO
EXEC [MyDBName].dbo.sp_addpushsubscription_agent
@publication = N'P2P_MyDBName'
, @subscriber = N'MyAGListenerName,<port>'
, @subscriber_db = N'MyDBName'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 1
, @frequency_type = 64
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 4
, @frequency_subday_interval = 5
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @dts_package_location = N'Distributor'
GO
リンク サーバーを構成する
セカンダリ レプリカの各ホストで、データベースのパブリケーションのプッシュ サブスクライバーがリンク サーバーとして表示されることを確認します。
EXEC sys.sp_addlinkedserver
@server = 'MySubscriber';
次のステップ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示