可用性グループ内に両方のピアを構成する

  • [アーティクル]

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) を設定する方法について説明します。

  1. sp_adddistributor を実行して、Dist1 でディストリビューションを構成します。 @password = を使用して、リモート パブリッシャーでディストリビューターへの接続に使用されるパスワードを指定します。 このパスワードは、リモート ディストリビューターを構成するときに各リモート パブリッシャーで使用します。

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';

  2. ディストリビューター側のディストリビューション データベースを作成します。

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. 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)

  1. リモート ディストリビューションの元のパブリッシャーを構成します (Node1)。 @password には、ディストリビューターでディストリビューションを設定するために sp_adddistributor を実行したときと同じ値を指定します。

    EXEC sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. データベースでレプリケーションを有効にします。

    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)

  1. 目的のプライマリ レプリカで、データベースをメンバー データベースとして使用して可用性グループを作成します。

  2. 可用性グループの 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) を設定する方法について説明します。

  1. sp_adddistributor を実行して、Dist2 でディストリビューションを構成します。 @password = を使用して、リモート パブリッシャーでディストリビューターへの接続に使用されるパスワードを指定します。 このパスワードは、リモート ディストリビューターを構成するときに各リモート パブリッシャーで使用します。

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist2',  
 @password = '<Strong password for distributor>';

  2. ディストリビューター側のディストリビューション データベースを作成します。

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. 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)

  1. (Node3) で、リモート ディストリビューションを構成します。 @password には、ディストリビューターでディストリビューションを設定するために sp_adddistributor を実行したときと同じ値を指定します。

    EXEC sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. データベースでレプリケーションを有効にします。

    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)

  1. 目的のプライマリ レプリカで、データベースをメンバー データベースとして使用して可用性グループを作成します。

  2. 可用性グループの 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';

次のステップ