1 つのピアを可用性グループの一部として構成する

[アーティクル]
09/25/2024

SQL Server 2019 (15.x) CU 13 以降、SQL Server Always On 可用性グループに属するデータベースは、ピアツーピアトランザクションレプリケーショントポロジにピアとして参加できます。この記事では、このシナリオを構成する方法について説明します。

この例のスクリプトでは、T-SQL ストアドプロシージャを使用します。

ロールと名前

このセクションでは、この記事のレプリケーショントポロジに参加するさまざまな要素のロールと名前について説明します。

Peer1

Node1: MyAG 可用性グループのレプリカ 1。
Node2: MyAG 可用性グループのレプリカ 2。
MyAG: Node1 と Node2 で作成する可用性グループの名前。
MyAGListenerName: 可用性グループリスナーの名前。
Dist1: リモートディストリビューターインスタンスの名前。
MyDBName: データベースの名前。
P2P_MyDBName: パブリケーション名。

Peer2

Node3: SQL Server の既定のインスタンスをホストするスタンドアロンサーバー。
Dist2: リモートディストリビューターインスタンスの名前。
MyDBName: データベースの名前。
P2P_MyDBName: パブリケーション名。

前提条件

別々の物理サーバーまたは仮想サーバー上で、可用性グループをホストする SQL Server の 2 つのインスタンス。この可用性グループにピアデータベースを含めます。
もう 1 つのピアデータベースをホストする SQL Server の 1 つのインスタンス。
ディストリビューターデータベースをホストする 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)

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)

セカンダリレプリカの各ホストで、ディストリビューションを構成します。 @password には、ディストリビューターでディストリビューションを設定するために sp_adddistributor を実行したときと同じ値を指定します。

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

データベースを可用性グループの一部とし、リスナーを作成する (Peer1)

目的のプライマリレプリカで、データベースをメンバーデータベースとして使用して可用性グループを作成します。
可用性グループの DNS リスナーを作成します。このリスナーを使用して、レプリケーションエージェントが、現在のプライマリレプリカに接続されます。次の例では、MyAGListername という名前のリスナーを作成します。
```
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> は省略可能です。これは、既定以外のポートを使用する場合にのみ必要です。山かっこ <> は含めないでください。

上記の手順を完了すると、この可用性グループはピアツーピアトポロジに参加する準備が整います。次の手順で、参加する SQL Server のスタンドアロンインスタンス (Peer2) を構成します。

ディストリビューターとリモートパブリッシャーを構成する (Peer2)

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

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;

ディストリビューター Dist2 で Node3 をリモートパブリッシャーとしてを構成する

USE master;   
GO   
EXEC sys.sp_adddistpublisher   
 @publisher = 'Node3',   
 @distribution_db = 'distribution',   
 @working_directory = '\\MyReplShare\WorkingDir2',   
 @security_mode = 1

パブリッシャーを構成する (Peer2)

Node3 で、リモートディストリビューションを構成します。

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

Node3 で、レプリケーション用のデータベースを有効にします。

USE master;  
GO  
EXEC sys.sp_replicationdboption  
    @dbname = 'MyDBName',  
    @optname = 'publish',  
    @value = 'true';

ピアツーピアパブリケーションを作成する (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

Peer1 から Peer2 へのプッシュサブスクリプションを作成する

この手順では、可用性グループから SQL Server のスタンドアロンインスタンスへのプッシュサブスクリプションを作成します。

Node1 で次のスクリプトを実行します。これは、Node1 がプライマリレプリカを実行していることを前提としています。

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @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'Node3'
 , @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 から可用性グループリスナーへのプッシュサブスクリプションを作成する

Peer2 から可用性グループリスナーへのプッシュサブスクリプションを作成するには、Node3 で次のコマンドを実行します。

重要

次のスクリプトでは、サブスクライバーの可用性グループリスナー名を指定します。

@subscriber = N'MyAGListenerName,<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';

次のステップ

ピアツーピアパブリケーションデータベースを可用性グループに含めるように構成する

次の方法で共有

1 つのピアを可用性グループの一部として構成する

ロールと名前

Peer1

Peer2

前提条件

ディストリビューターとリモートパブリッシャーを構成する (Peer1)

元のパブリッシャーでパブリッシャーを構成する (Node1)

セカンダリレプリカのホストをレプリケーションのパブリッシャーとして構成する (Node2)

データベースを可用性グループの一部とし、リスナーを作成する (Peer1)

元のパブリッシャーを AG リスナー名にリダイレクトする (Peer1)

元のパブリッシャーでピアツーピアパブリケーション (Peer1) を作成する - Node1

ピアツーピアパブリケーションが可用性グループと互換性を持つようにする (Peer1)

ディストリビューターとリモートパブリッシャーを構成する (Peer2)

パブリッシャーを構成する (Peer2)

ピアツーピアパブリケーションを作成する (Peer2)

Peer1 から Peer2 へのプッシュサブスクリプションを作成する

Peer2 から可用性グループリスナーへのプッシュサブスクリプションを作成する

リンクサーバーを構成する

次のステップ

フィードバック

その他のリソース

次の方法で共有

1 つのピアを可用性グループの一部として構成する

ロールと名前

Peer1

Peer2

前提条件

ディストリビューターとリモート パブリッシャーを構成する (Peer1)

元のパブリッシャーでパブリッシャーを構成する (Node1)

セカンダリ レプリカのホストをレプリケーションのパブリッシャーとして構成する (Node2)

データベースを可用性グループの一部とし、リスナーを作成する (Peer1)

元のパブリッシャーを AG リスナー名にリダイレクトする (Peer1)

元のパブリッシャーでピアツーピア パブリケーション (Peer1) を作成する - Node1

ピアツーピア パブリケーションが可用性グループと互換性を持つようにする (Peer1)

ディストリビューターとリモート パブリッシャーを構成する (Peer2)

パブリッシャーを構成する (Peer2)

ピアツーピア パブリケーションを作成する (Peer2)

Peer1 から Peer2 へのプッシュ サブスクリプションを作成する

Peer2 から可用性グループ リスナーへのプッシュ サブスクリプションを作成する

リンク サーバーを構成する

次のステップ

フィードバック

その他のリソース

ディストリビューターとリモートパブリッシャーを構成する (Peer1)

セカンダリレプリカのホストをレプリケーションのパブリッシャーとして構成する (Node2)

元のパブリッシャーでピアツーピアパブリケーション (Peer1) を作成する - Node1

ピアツーピアパブリケーションが可用性グループと互換性を持つようにする (Peer1)

ディストリビューターとリモートパブリッシャーを構成する (Peer2)

ピアツーピアパブリケーションを作成する (Peer2)

Peer1 から Peer2 へのプッシュサブスクリプションを作成する

Peer2 から可用性グループリスナーへのプッシュサブスクリプションを作成する

リンクサーバーを構成する