Always On 可用性グループのレプリケーション ディストリビューション データベースを設定する
適用対象: SQL Server
この記事では、Always On 可用性グループ (AG) で SQL Server レプリケーションディストリビューション データベースを設定する方法について説明します。
SQL Server 2017 CU 6 と SQL Server 2016 SP2-CU3 では、次のメカニズムを使用して AG のレプリケーション ディストリビューション データベースをサポートするようになりました。
- ディストリビューション データベース AG にはリスナーが必要です。 パブリッシャーでは、ディストリビューターを追加する場合、リスナー名をディストリビューター名として使用します。
- レプリケーション ジョブの作成では、リスナー名をディストリビューター名として使用します。 ディストリビューション サーバー上で作成されたレプリケーション スナップショット ジョブ、ログ リーダー ジョブ、ディストリビューション エージェント (プッシュ サブスクリプション) ジョブは、ディストリビューション DB 用 AG のすべてのセカンダリ レプリカ上で作成されます。
Note
プル サブスクリプション用のディストリビューション エージェント ジョブは、サブスクライバー サーバー上には作成されますが、ディストリビューション サーバー上には作成されません。
- 新しいジョブでは、ディストリビューション データベースの状態 (AG 内でプライマリかセカンダリか) を監視し、ディストリビューション データベースの状態に基づいてレプリケーション ジョブを有効または無効にします。
AG のディストリビューション データベースを下記の手順に基づいて構成したら、ディストリビューション データベース AG のフェールオーバーの前後にレプリケーション構成ジョブおよびランタイム ジョブが正常に実行されるようになります。
サポートされるシナリオ
- AG に含めるディストリビューション データベースを構成します。
- AG フェールオーバーの前後にパブリケーションやサブスクリプションなどのレプリケーションを構成します。
- フェールオーバーの前後に機能するレプリケーション ジョブ。
- ディストリビューション データベースが AG に含まれている場合にディストリビューター側およびパブリッシャー側でレプリケーションを削除します。
- 既存のディストリビューション データベース AG にノードを追加、またはその AG からノードを削除します。
- ディストリビューターには複数のディストリビューション データベースが存在する場合があります。 各ディストリビューション データベースは独自の AG に属することはできますが、任意の AG に属することはできません。 複数のディストリビューション データベースで AG を共有することができます。
- パブリッシャーとディストリビューターは別々の SQL Server インスタンス上に置く必要があります。
- ディストリビューション データベースをホストする可用性グループのリスナーが既定以外のポートを使用するように構成されている場合は、リスナーと既定以外のポートのエイリアスを設定する必要があります。
制限事項または適用除外事項
ローカル ディストリビューター (パブリッシャー サーバーもディストリビューター) はサポートされていません。 パブリッシャーとディストリビューターは別々の SQL Server インスタンス上に置く必要があります。 これらのインスタンスは、同じノード セットでホストできます。 次の理由により、ローカル ディストリビューターはサポートされていません。
- ディストリビューターがローカルで構成されている場合、可用性グループ リスナーを使用してディストリビューターにトラフィックを経路選択すると、フェールオーバー後に、レプリケーション エージェントが失敗する原因となります。
- ローカル ディストリビューターが構成されていて、ディストリビューター可用性グループが元のセカンダリにフェールオーバーすると、ディストリビューターへのパブリッシャー接続がローカルからリモートに変わり、レプリケーション ストアド プロシージャとエージェントが失敗します。
Oracle パブリッシャーはサポートされていません。
マージ レプリケーションはサポートされていません。
即時更新サブスクライバーまたはキュー更新サブスクライバーを含むトランザクション レプリケーションはサポートされていません。
SQL Server 2019 (15.x) CU 17 より前のピア ツー ピア レプリケーションはサポートされていません
ディストリビューション データベースのレプリカをホストする SQL Server 2017 インスタンスはすべて、SQL Server 2017 CU 6 以降とする必要があります。
ディストリビューション データベースのレプリカをホストする SQL Server 2016 インスタンスはすべて、SQL Server 2016 SP2-CU3 以降とする必要があります。
ディストリビューション データベースのレプリカをホストする SQL Server インスタンスはすべて、同じバージョンである必要があります。ただし、アップグレードが実行される狭いタイムフレームの期間中は例外です。
ディストリビューション データベースは、完全復旧モデルである必要があります。
復旧し、トランザクション ログの切り捨てを許可するには、完全バックアップとトランザクション ログ バックアップを構成します。
ディストリビューション データベース AG では、リスナーが構成されている必要があります。
ディストリビューション データベース AG 内のセカンダリ レプリカは、同期または非同期のいずれにも指定できます。 同期モードが推奨され、優先されます。
双方向トランザクション レプリケーションはサポートされていません。
ディストリビューション データベースが可用性グループに追加されたときに、SSMS でディストリビューション データベースが同期中/同期済みとして表示されることはありません。
Note
任意のレプリケーション ストアド プロシージャ (たとえば、
sp_dropdistpublisher
、sp_dropdistributiondb
、sp_dropdistributor
、sp_adddistributiondb
、sp_adddistpublisher
) をセカンダリ レプリカ上で実行するには、レプリカが完全に同期されていることを事前に確認しておきます。ディストリビューション データベース AG 内のセカンダリ レプリカは、すべて読み取り可能である必要があります。 セカンダリ レプリカが読み取り可能でない場合、特定のセカンダリ レプリカ上の SQL Server Management Studio のディストリビューター プロパティにアクセスすることはできませんが、レプリケーションは引き続き正常に動作します。
ディストリビューション データベース AG 内のノードはすべて同じドメイン アカウントを使用して SQL Server エージェントを実行する必要があります。このドメイン アカウントの権限は各ノードで同じである必要があります。
任意のレプリケーション エージェントをプロキシ アカウントで実行する場合、このプロキシ アカウントがディストリビューション データベース AG 内のすべてのノードに割り当てられており、その権限は各ノードで同じである必要があります。
ディストリビューション データベース AG に参加しているすべてのレプリカで、ディストリビューターまたはディストリビューション データベースのプロパティを変更します。
ディストリビューション データベース AG に参加しているすべてのレプリカで、msdb ストアド プロシージャまたは SQL Server Management Studio を介してレプリケーション ジョブに変更を加えます。
エージェントにカスタム プロファイルを使用する場合は、
sp_add_agent_profile
手順を使用して、すべてのセカンダリ レプリカに手動で作成する必要があります。 プロファイルは、すべてのレプリカに同じ ID を持っている必要があります。 セカンダリ レプリカにプロファイルが存在しない場合は、フェールオーバー後に主キー違反エラーが発生する可能性があります。 多くの場合、エラーを解決するには、パブリケーションのサブスクリプションを再初期化する必要があります。パブリッシャー上でディストリビューターを構成する場合は、スクリプトを使用する必要があります。 レプリケーション ウィザードは使用できません。 レプリケーション ウィザードおよびプロパティ シートは他の目的ではサポートされています。
ディストリビューション データベースに対して AG を構成するには、スクリプトしか使用できません。
AG のディストリビューション データベースの設定は、新しいレプリケーション構成として行う必要があります。 既存のディストリビューション データベースを AG に切り替えることはサポートされていません。 また、ディストリビューション データベースは AG から外されると、有効なディストリビューション データベースとして機能しなくなります。そのようなディストリビューション データベースは削除する必要があります。
構成アーキテクチャ
この記事の例では、次のサーバー名と設定を使用します。
- DIST1、DIST2、DIST3 はディストリビューター サーバーです。
- PUB はパブリッシャー サーバーです。
- ディストリビューション データベースの AG が形成された後、リスナー名は DISTLISTENER となります。
- DIST1 はディストリビューション データベース AG の初期のプライマリ レプリカとなることを目的としています。
ディストリビューター、ディストリビューション データベース、パブリッシャーを構成する
この例では、ディストリビューターとパブリッシャーを新規に構成し、ディストリビューション データベースを AG に含めます。
ディストリビューターのワークフロー
sp_adddistributor @@servername
を使用して DIST1、DIST2、DIST3 をディストリビューターとして構成します。@password
によってdistributor_admin
のパスワードを指定します。@password
は、DIST1、DIST2、DIST3 の間で同じにする必要があります。sp_adddistributiondb
を使用して DIST1 上にディストリビューション データベースを作成します。 ディストリビューション データベースの名前はdistribution
です。distribution
データベースの復旧モデルを単純から完全に変更します。DIST1、DIST2、および DIST3 上でレプリカを使用して
distribution
データベース用の AG を作成します。 すべてのレプリカを同期させることをお勧めします。 セカンダリ レプリカを読み取り可能に構成するか、読み取りを許可するように構成します。 この時点では、ディストリビューション データベースは AG であり、DIST1 がプライマリ レプリカ、DIST2 と DIST3 がセカンダリ レプリカです。AG 用に
DISTLISTENER
という名前のリスナーを構成します。復旧し、トランザクション ログの切り捨てを許可するには、完全バックアップとトランザクション ログ バックアップを構成します。
DIST2 および DIST3 上で次を実行します。
EXEC sys.sp_adddistributiondb @database = 'distribution';
PUB
をパブリッシャーとして DIST1 に追加するには、次を実行します。EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
@working_directory
の値は、DIST1、DIST2、および DIST3 とは無関係のネットワーク パスとする必要があります。DIST2 と DIST3 でレプリカがセカンダリとして読み取り可能である場合は、次を実行します。
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
レプリカがセカンダリとして読み取り可能でない場合は、レプリカがプライマリになるようにフェールオーバーを実行し、
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
@working_directory
の値は、前の手順と同じにする必要があります。
パブリッシャーのワークフロー
distribution
データベース AG のリスナーをディストリビューターとして PUB に追加するには、次を実行します。
EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;
@password の値は、ディストリビューターのワークフローでディストリビューターを構成したときに指定した値とする必要があります。
ディストリビューターとパブリッシャーを削除する
この例では、ディストリビューション データベースが AG 内にある場合に、パブリッシャーとディストリビューターを削除します。
パブリッシャーのワークフロー
PUB 上で、このパブリッシャー用のサブスクリプションとパブリケーションをすべて削除してから、sp_dropdistributor
を呼び出します。
ディストリビューターのワークフロー
この例では、DIST1 が distribution
データベース AG の現在のプライマリ レプリカです。 DIST2 と DIST3 は、セカンダリ レプリカです。
DIST2 および DIST3 上で次を実行します。
EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;
DIST1 上で次を実行します。
EXEC sys.sp_dropdistpublisher @publisher = 'PUB';
AG を削除します。
DIST2 および DIST3 上で、回復によってデータベースを復元することで、
distribution
データベースを read_write モードに変更します。RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
distribution
データベースを削除し、スナップショット ディレクトリを保持するには、次を実行します。EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;
この手順では、このレプリカに対するすべての未解決ジョブを削除します。
DIST1 上で
distribution
データベースを削除するには、次を実行します。EXEC sys.sp_dropdistributiondb @database = 'distribution';
AG 内にディストリビューション データベースが他に存在しない場合は、DIST1、DIST2、および DIST3 上で
sp_dropdistributor
を実行します。
ディストリビューション データベース AG にレプリカを追加する
この例では、AG のディストリビューション データベースを対象にした既存のレプリケーション構成に新しいディストリビューターを追加します。 この例では、既存のディストリビューション データベースは AG に含まれています。 DIST1 および DIST2 はディストリビューターです。distribution
は AG に含まれているディストリビューション データベースです。PUB はパブリッシャーです。 AG にレプリカとして DIST3 を追加する
ディストリビューターのワークフロー
sp_adddistributor @@servername
を使用して DIST3 をディストリビューターとしてする必要があります。 @password パラメーターを使用して、distributor_admin
のパスワードを指定する必要があります。 パスワードは、DIST1 および DIST2 に対して指定したものと同じにする必要があります。現在のディストリビューション データベース用の AG に DIST3 を追加します。
DIST3 上で次を実行します。
EXEC sys.sp_adddistributiondb @database = 'distribution';
DIST3 でレプリカがセカンダリとして読み取り可能である場合は、次を実行します。
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
レプリカがセカンダリとして読み取り可能でない場合は、レプリカがプライマリになるようにフェールオーバーを実行し、次を実行します。
EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
@working_directory
の値は、DIST1 および DIST2 に対して指定したものと同じにする必要があります。DIST3 では、サブスクライバ―に対してリンク サーバーを再作成する必要があります。
ディストリビューション データベース AG からレプリカを削除する
この例では、現在のディストリビューション データベース AG からディストリビューターを削除しますが、ディストリビューション データベース AG 内の残りのレプリカには影響ありません。 この例では、ディストリビューション データベースは AG に含まれています。 DIST1、DIST2、および DIST3 はディストリビューターです。distribution
は AG に含まれているディストリビューション データベースです。PUB はパブリッシャーです。 AG から DIST3 を削除します。
ディストリビューターのワークフロー
DIST3 が
distribution
データベース AG のセカンダリ レプリカであることを確認します。distribution
データベース AG から DIST3 を削除します。DIST3 上で、回復によってデータベースを復元することで、
distribution
データベースを read_write モードに変更します。 たとえば、次のコマンドを実行します。RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
DIST3 に対する孤立したジョブをすべて削除するには、次を実行します。
EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;
DIST3 上で次を実行します。
EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;
DIST3 上で次を実行します。
EXEC sys.sp_dropdistributor;
ディストリビューション データベース AG からパブリッシャーを削除する
この例では、ディストリビューターの現在のディストリビューション データベース AG からパブリッシャーを削除しますが、このディストリビューション データベース AG によって提供されている残りのパブリッシャーには影響ありません。 この例では、既存の構成のディストリビューション データベースは AG に含まれています。 DIST1、DIST2、および DIST3 はディストリビューターです。distribution
は AG に含まれているディストリビューション データベースです。PUB1 と PUB2 は distribution
データベースによって提供されているパブリッシャーです。 例では、これらのディストリビューターから PUB1 を削除します。
パブリッシャーのワークフロー
PUB1 上で、このパブリッシャー用のサブスクリプションとパブリケーションをすべて削除してから、sp_dropdistributor
を呼び出します。
ディストリビューターのワークフロー
DIST1 は distribution
データベース AG の現在のプライマリ レプリカです。
DIST2 および DIST3 上で次を実行します。
EXEC sys.sp_dropdistpublisher @publisher = 'PUB1', @no_checks = 1;
DIST1 上で次を実行します。
EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';
この時点で、DIST2 または DIST3 には PUB1 に関連する孤立したジョブが存在する可能性があります。 DIST2 および DIST3 へのフェールオーバーが発生するたびに、PUB1 のすべてのパブリケーションに関連する孤立したジョブは
Monitor and sync replication agent jobs
ジョブによって削除されます。
サブスクリプションの追加
この例は、ディストリビューター間でサブスクライバー情報を正しく構成する方法について示します。 この例ではサブスクライバーを追加します。 DIST1 は AG 内のディストリビューション データベースの現在のプライマリ レプリカであり、DIST2 と DIST3 は AG 内のディストリビューション データベースの現在のセカンダリ レプリカです。 サブスクライバーの名前は SUB です。
パブリッシャーのワークフロー
PUB 上で、サブスクライバー SUB
に対して通常行うのと同様にサブスクリプションを追加します。
ディストリビューターのワークフロー
DIST2 と DIST3 で、以前に DIST2 または DIST3 に登録されていない場合は、'SUB' のリンク サーバーを追加します。 次のサンプルは、リンク サーバーの作成用の TSQL です。
EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;
プル サブスクリプションを追加する
サブスクライバーのワークフロー
AG 内のディストリビューション データベースでのパブリケーションのためにプル サブスクリプションを追加するには、@distributor
の sp_addpullsubscription_agent
パラメーターに AG リスナー名を使用します。
サンプル T-SQL: AG でのディストリビューション DB の作成
次のスクリプトは、可用性グループ内でディストリビューション データベースを有効にします。
--- WorkFlow to Enable Distribution Database In AG.
-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS
-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
@distributor = @@SERVERNAME,
@password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
@distributor = @@SERVERNAME,
@password = 'Pass@word1';
GO
-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
@database = 'DistributionDB',
@security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES
);
GO
:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES
);
GO
:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
SEEDING_MODE = AUTOMATIC);
GO
:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO
--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1
USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO
-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO
--STEP 6 - On all Distributor Nodes Configure the Publisher Details
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB',
@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB',
@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.
@password = 'Pass@word1';
GO
-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;