トランザクションレプリケーションの基本的な仕組みを調べたことがなかったのでメモとして。
今回はプッシュサブスクリプションを利用しています。
レプリケーションについては、兄貴 の SQL Server replication overview や SQL Server のレプリケーション から情報を追ってみると良いかと。
最初に注意しておきたい点としては、SQL Server のインストール時に [_SC] (補助文字) の照合順序を使用している場合、レプリケーションの設定でエラーが発生します。
(ディストリビューターの追加でエラーになるようですが。)
Collation error when adding distributer
各役割の照合順序については事前に確認しておいた方がよさそうですね。
SQL Server のトランザクションレプリケーションは
- パブリッシャー (出版社)
- ディストリビューター (流通業者)
- サブスクライバー (購読者)
から構成されます。
(詳細は レプリケーションのパブリッシング モデルの概要 から確認するとよいかと思います。)
各役割のデータ連携については SQL Server エージェントジョブで実施されているため、レプリケーションは SQL Server エージェントが必須となります。
SQL Server エージェントに登録されるジョブの役割については、レプリケーション エージェントの概要 から確認できます。
■パブリッシャーからサブスクライバーに転送するデータの読み取り
データ配信の中心となるのはディストリビューターであり、ディストリビューターがパブリッシャーのトランザクションログからレプリケーション対象 (アーティクルに設定した内容) テーブルのトランザクションログを読み取り、ディストリビューターのテーブルに格納をします。
ログの読み取りについてはディストリビューターの SQL Server エージェントジョブの [ログ リーダー] で行われます。
ログ リーダー からパブリッシャーのログを読み取り、ディストリビューターに格納されます。
それでは、この動作の内容を確認してみたいと思います。
今回は
- Table_1 : レプリケーション対象
- Table_2 : レプリケーション対象外
のテーブルを作成しています。
このテーブルに対して、以下のクエリでデータを挿入してみます。
SET NOCOUNT ON GO DECLARE @Key1 int = (SELECT COUNT(*) + 1 FROM Table_1) DECLARE @Key2 int = (SELECT COUNT(*) + 1 FROM Table_2) DECLARE @cnt int = 1 WHILE (@cnt <= 3) BEGIN INSERT INTO Table_1 VALUES(@Key1, NEWID()) INSERT INTO Table_2 VALUES(@Key2, NEWID()) SELECT @Key1 += 1, @Key2 += 1, @cnt += 1 END
データの挿入が終わったら以下のクエリでログレコードの内容を確認してみます。
SELECT Operation, Context, AllocUnitName, Description FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Replicated Records] = 1)
クエリでは Table_1 / Table_2 に対して INSERT を実行していますが、レプリケーション対象は Table_1 となります。
トランザクションレプリケーションではトランザクション単位でレプリケーションを実施するため、レプリケーション対象については、[Replicated Records] が 1 となっているようです。
実際のログとしてどの操作が連携されるかはここから確認できそうですね。
ログリーダーが読み込んだログについては、[distribution].[dbo].[MSrepl_transactions] から、どのタイミングでどのトランザクション ID が読み込まれたかを確認することができます。。
読み込まれたトランザクションは、[distribution].[dbo].[MSrepl_commands] から確認することができます。
このテーブルの [command] にはバイナリでサブスクライバーで実行する必要のあるコマンドが格納されています。
対象のコマンドのバイナリデータを確認するために以下のクエリでページ番号を取得してみます。
SELECT [xact_seqno],plc.* FROM [distribution].[dbo].[MSrepl_commands] CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS plc
DBCC PAGE を使用して、ページの情報を確認してみます。
DBCC TRACEON(3604) DBCC PAGE(N'distribution', 1, 784, 3)
command には以下のようなバイナリデータが格納されています。
今回は Table_1 をレプリケーションするための、アーティクルのステートメントとして以下の設定がされています。
(?sp_helparticle ‘Table_1’ のようなストアドプロシージャでも確認可能です。)
INSERT 時にはストアドプロシージャ [sp_MSins_dbo_Table_1] が実行されます。
このストアドプロシージャーはサブスクライバーのデータベースに作成されており、パブリッシャーの変更内容がサブスクライバーのこのストアドプロシージャを実行することで適用されます。
先ほどのバイナリデータには、サブスクライバーで実行する必要のあるコマンドが格納されていたことが確認できますね。
バイナリデータを確認しなくても、ストアドプロシージャ [distribution].[sys].[sp_browsereplcmds] を実行することで、コマンドを確認することもできます。
EXEC [distribution].[sys].[sp_browsereplcmds]
■サブスクライバーへのデータの適用
ログリーダーによりディストリビューターに格納されたトランザクションが、SQL Server エージェントジョブの [ディストリビューション] によりサブスクライバーに適用されます。
サブスクライバーにどこまでトランザクションが適用されているかについては、サブスクライバーのデータベースの MSreplication_subscriptions から確認することができます。
サブスクリプション データベースの MSreplication_subscriptions の transaction_timestamp と ディストリビューション データベースの MSrepl_transactions を比較することで、取り込まれたトランザクションのうち、どこまでがサブスクライバーに連携されているかを確認できます。
MSrepl_transactions の xact_seqno と MSreplication_subscriptions の transaction_timestamp を比較することでどこまでサブスクライバーに反映されているかを確認することができます。
■ディストリビューションのクリーンアップ
ディストリビューションデータベースに格納されたトランザクションは永続的に保存されているのではなく、SQL Server エージェントジョブのディストリビューションのクリーンアップジョブで定期的に削除が行われます。
スナップショットを使用しないトランザクション サブスクリプションの初期化
レプリケーション メンテナンス ジョブの実行 (SQL Server Management Studio)
トランザクション レプリケーションに関する注意点
ディストリビューションのクリーンアップでは、dbo.sp_MSdistribution_cleanup を実行し、72 時間前 (@max_distretention の指定値) のトランザクションが削除されるように SQL Server エージェントジョブのステップで指定がされています。
このストアドプロシージャでは、dbo.sp_MSdistribution_delete → sp_MSdelete_publisherdb_trans → sp_MSdelete_dodelete がコールされ、MSrepl_commands (sp_MSdelete_publisherdb_trans) / MSrepl_transactions? (sp_MSdelete_dodelete) からレコードが削除されます。
このレコードの削除は、サブスクライバーへのデータ転送が行われていなくても削除が行われます。
同期が行われていない状態でディストリビューションがクリーンアップされた場合は、サブスクリプションが非アクティブに設定され、再初期化が必要となるようです。
非アクティブかどうかについては dbo.MSsubscriptions から確認することができます。
( Status が 0 の場合は非アクティブとなっています。)
SELECT publisher_db , subscriber_db , subscription_type , status FROM [dbo].[MSsubscriptions]
トランザクションレプリケーションで競合が発生した場合は、サブスクライバーで競合するデータを解消するか、スナップショットまたは、以下のような方法で初期化をする必要がありますので、運用時にはこの辺も合わせて確認しておくとよさそうですね。
スナップショットを使用しないトランザクション サブスクリプションの初期化
トランザクション サブスクリプションのバックアップからの初期化 (レプリケーション Transact-SQL プログラミング)
あとは レプリケーション モニター の使用方法も確認しておくとよいかと。
レプリケーション用のストアドプロシージャもいろいろとありますので、トラブル対応用に sp_repldone をはじめとした強制的なログへのレプリケート済みのフラグ設定等は覚えておくとよさそうですね。
最近は、AlwaysOn 可用性グループのようなレプリケーション方式のものしか触っていなかったので、レプリケーションの仕組みについては結構忘れていることがありますね…
某エンジニアの日記 でリブログしてコメントを追加:
レプリケーションの機能を理解しておくと、仮想トランザクション ログ ファイル (VLF) のサイズやトランザクション レプリケーション環境におけるトランザクション ログの切捨てタイミングが理解できると思います。
ぜひ、熟読してみてください。
tinaba
14 10月 14 at 07:33