SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

トランザクションレプリケーションの動作を理解する – ログリーダー編 –

leave a comment

SQL Server のテーブル単位でのデータ同期の仕組みとして「トランザクションレプリケーション」があります。
以前 トランザクションレプリケーションの基本的な仕組み という投稿をしたことがあるのですが、もう少し詳細なレベルでの知識が必要となったので、スキルアップデートをしようかと。

SQL Serer のレプリケーションの構成は次のようになります。

上記の図と基本的な概念については、レプリケーションのパブリッシング モデルの概要 に記載されていますので割愛しますが、

  1. パブリッシャーで配信対象として設定したテーブルの変更を検知して、ディストリビューターに格納
  2. ディストリビューターに格納されたデータを、サブスクライバーに反映させる

というような流れとなっており、これらの制御を「レプリケーション エージェント」が実施しています。
このレプリケーションエージェントのうち「1.」の部分を実施しているのが「ログリーダー エージェント」となります。
この流れについては、トランザクション レプリケーション に記載されています。

ログリーダー エージェントがトランザクションログの内容を読み、ディストリビューションデータベース (distributor) に格納するという流れが記載されています。
本投稿ではこの流れをステートメントレベルで理解するということが目的となります。
ログリーダー エージェントは、SQL Server Agent のジョブとして登録がされている「Repl-LogReader」のカテゴリが設定されたジョブで起動して、制御が行われています。
image
このジョブが起動することで、「logread.exe」がパブリッシャー内で起動して、トランザクションログの読み取りとディストリビューターへの配信が行われます。
ログリーダーは SQL Server 内では次のようなセッションの情報として確認ができます。

declare @host_process_id int = (select top 1 host_process_id from sys.dm_exec_sessions
where program_name like '%LogReader%')
select session_id, login_time,host_name,program_name, last_request_start_time,last_request_end_time
from sys.dm_exec_sessions
where host_process_id = @host_process_id
order by last_request_start_time desc

 
image
4 つのセッションが実行され、そのうちの 2 つは、SQL Server エージェントのジョブの出力のような補助的な動作を行っており、残りの二つが、

  1. トランザクションログのログレコードの読み取り
  2. 読み取ったトランザクションログのレコードをディストリビューターに格納
  3. 読み取ったトランザクションログを完了としてマーク

というような処理を実施しています。
実際に、どのようなクエリが実行されているかは、High Level Transactional Replication Process Flow で解説が行われています。

レプリケーション ログ リーダーとディストリビューションのレプリケーション エージェントのパフォーマンスの統計ツールの概要 も参考になります。

キーとなるのは、

    1. sp_replcmds : パブリケーション DB で実行
      • トランザクションログのログレコードの読み取り
    2. sp_MSadd_replcmds : ディストリビューター DB で実行
      • 読み取ったトランザクションログのレコードをディストリビューターに格納
    3. sp_repldone : パブリケーション DB で実行
      • 読み取ったトランザクションログを完了としてマーク

という処理です。
初期のスナップショットの配信が終わったテーブルを例にして、追加したレコードがどのように処理をされるかを見ていきましょう。
本来は、SQL Server エージェントのジョブから実行される「logread.exe」がこの辺りの一連の動作を実施してくれるのですが、今回は処理の流れを把握するために、手動でクエリを実行して確認を行います。
 

1. アーティクルにデータを投入

今回は T1 というテーブルをトランザクションレプリケーションの対象としていますので、データを INSERT します。

INSERT INTO ReplDB..T1(C1, C2)
VALUES(COALESCE((SELECT MAX(C1) + 1 FROM ReplDB..T1),1), 10)

 
T1 はレプリケーションの配信対象となっているため、トランザクションログからも状況を確認することができます。

SELECT * FROM sys.fn_dblog(NULL,NULL)
WHERE [Replicated Records] = 1

 
「Replicated Records」がレプリケート対象としてマークされていた、トランザクションログのレコードとなっています。

レプリケートされたレコードとなっていますが、レプリケートと対象としてマークされている状態については、次のクエリで確認ができます。

EXEC sp_repltrans

 
sp_repltrans はレプリケーション対象としてマークされているトランザクションの中で、ディストリビューターに未配布なトランザクションの結果セットを返すものとなります。
image
テーブルに対して INSERT したタイミングで該当データとして認識されていますので、レプリケーション対象のテーブルについてはデータの変更を実施したタイミングで、対象として認識されているように見えます。
この情報を使用することで「どのトランザクションログをディストリビューターへの配信対象とすればよいか？」の制御が行えることになります。
トランザクションレプリケーションの配信対象ログのマークのクリアとして次のようなクエリを用いるケースがあります。

exec sp_repldone null, null, 0,0,1

このクエリを実行することで sp_repltrans の情報をクリアしているようで、未配信のログが存在していない状態に強制的に変更しているようです。
 

2. トランザクションログのレコードの読み込み

「sp_repltrans」 を実行することで「どのトランザクションがディストリビューターに未配信なのか？」を核にすることができました。
次に実施する必要がある処理が「未配信のトランザクションログのログレコードの取得」となります。
未配信のトランザクションログレコードの取得を行うものが「sp_replcmds」となります。
このストアドプロシージャ (厳密には拡張ストアドプロシージャ) は、データベースに対して一つだけ実行ができるものとなります。
sp_replcmds を実行したセッションが残っている限りは、他のセッションはこのストアドを実行することはできません。
「sp_replflush」 を実行して、アーティクルキャッシュをフラッシュすることや、セッションが切断されることで、sp_replcmds のアクセスを解放することができますが、それらを実施しても後続の 1 セッションのみが sp_replcmds を実行することができます。
このような排他制御が行われているため、「1 DB につき sp_replcmds によりトランザクションログを読み取れるのは 1 セッションのみ」というような挙動となります。
このような制御は「sp_replshowcmds」も同一ですので、この 2 つのストアドを実行してみます。

EXEC sp_replcmds 500,0,-1,0x,0,0,500000
EXEC sp_replshowcmds

 
image
sp_replshowcmds の実行は、トランザクションログから生成したコマンドをユーザーフレンドリーな形で表示したものとなりますが、ログリーダーで実際に使用されているのは「sp_replcmds」となります。
sp_replcmds の引数については、「エージェント プロファイル」の「ログリーダー エージェント」のデフォルトの値を使用しています。
image
 
「sp_repltrans」でディストリビューターに未配信のトランザクションログが「sp_replcmds」によって読み取られることになります。
この読み取られたトランザクションログのレコードがディストリビューターに格納されます。
「sp_replcmds」によるログレコードの読み取りは、配信対象のテーブルがあるパブリケーション DB で実行されていましたが、ディストリビューターへの格納については、distribution DB で実行されます。
 

3. ディストリビューターへの格納

ログレコードの読み取り方法は確認できましたのでディストリビューターへの格納に移ります。
ディストリビューターにはデータを受け取るサブスクライバーに配信するためのデータを格納することになりますが、重要になるのが、次の 2 つのテーブルです。

これらのテーブルについては distribution データベースに存在している情報となります。
これらのテーブルの情報がデータの同期先である「サブスクライバー」でトランザクションを再生する際に使用される情報となります。
これらのテーブルに対してのデータの投入は「sp_MSadd_replcmds」というストアドプロシージャを介して行われることになります。
このストアドプロシージャに、「sp_replcmds」によって読み取られたトランザクションログの内容を渡すことで、「上記の 2 つのテーブルへのデータの投入 = 配信対象データの生成」 が行われることになります。
sp_replcmds の実行結果のパース方法については、sp_MSadd_replcmds の中身を見ることで分析することができるのですが、今回は特定の INSERT パターンに合わせて、sp_MSadd_replcmds に連携可能なバイナリを生成することができる次のクエリを実行しています。

DECLARE @replcmds table (
	[article id]	int,
	[table]	bit,
	command	varbinary(1024),
	xactid	binary(10),
	xact_seqno	varbinary(16),
	publication_id	int,
	command_id	int,
	command_type	int,
	originator_srvname	sysname,
	originator_db	sysname,
	pkHash	int,
	originator_publication_id	int,
	originator_db_version	int,
	originator_lsn	varbinary(16)
)
INSERT INTO @replcmds EXEC sp_replcmds 500,0,-1,0x,0,0,500000
SELECT xactid + xact_seqno + CAST([Article ID] AS varbinary(10))  + CAST(command_id AS varbinary(10))
+ CAST(command_type AS varbinary(10))
+ CAST(LEN(command) +2 AS varbinary(3))
+ 0x000000
+ CAST(pkHash as varbinary(3))
+ 0x000000000000000000000000000000000000
+ command
FROM @replcmds

これでトランザクションログのレコードをディストリビューターに配信することができるバイナリ値を生成することができます。
作成されたバイナリ値を使用して、sp_MSadd_replcmds を実行します。

DECLARE @cmd varbinary(max) = 0x00000～18181
exec sp_MSadd_replcmds 3,0,N'ReplDB',@cmd

 
3 の部分は publication_database_id となるため、「MSpublications」あたりから情報を確認すればよいかと。
sp_MSadd_replcmds が正常に実行されていれば、次のクエリでディストリビューターのデータの格納状況を確認することができます。

select TOP 1 * from MSrepl_transactions order by entry_time desc
select * from MSrepl_commands
exec sp_browsereplcmds

 
image
これでディストリビューターへのデータ格納が終了しました。
最後の処理として、パブリケーションデータベースで「ディストリビューターにデータを格納されたことを把握させる」必要があります。
 

4. ディストリビューターにトランザクションを格納できたことを反映

ここまでで、パブリケーションデータベースのトランザクションログをディストリビューターに格納することはできました。
この状態で次のクエリを実行するとどうなるでしょうか？

EXEC sp_replcmds 500,0,-1,0x,0,0,500000
EXEC sp_replshowcmds

 
ここまでの作業ではトランザクションログのデータは再度読み込めてしまいます。
image
最後のステップとして、ディストリビューターに格納したトランザクションログを配信済みとしてマークする必要があります。
この時に使用するストアドが「sp_repldone」です。
先ほどのクエリで「0x00000283000000500003」という「xact_seqno」(トランザクションシーケンス番号) のログの内容をディストリビューターに連携しましたので、このトランザクションを配信済みとしてマークします。

DECLARE @xact_seqno varbinary(16) = 0x00000283000000500003
EXEC sp_repldone 0x,@xact_seqno,-1,1

 
これにより「sp_repltrans」で取得可能な結果セットから、該当のトランザクションが除外された状態となり、再度「sp_replcmds」を実行してみると、このトランザクションが表示されなくなっていることが確認できます。

EXEC sp_replcmds 500,0,-1,0x,0,0,500000
EXEC sp_replshowcmds
EXEC sp_repltrans

 
image
 
SQL Server Agent のジョブでログリーダを起動させたい場合は、次のクエリを実行して、ログリーダーが本来実行する処理を手動で実行していたものを解放しておきます。

EXEC sp_replflush

 
ログリーダーエージェントの中ではこれ以外にもいくつかのストアドが実行されていますが、ログの読み取りからディストリビューターへの格納はこのような流れを繰り返しながら実現されています。
詳細にこの辺の流れが解説されている公式のドキュメントが見当たらず、手探りで調査してみたのですが、ログリーダーの基本的な処理は、手動で代替できた気がします。

Share

Written by Masayuki.Ozawa

3月 5th, 2020 at 11:19 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply