SE の雑記

ぺんぺん師匠が Azure SQL DatabaseのGeoレプリケーションのセカンダリへの反映ロジック という記事を書かれていて、この内容についてディスカッションさせていただいたのですが、結構面白かったので、この辺の内容をまとめてみようかと。

今の SQL Database では、どのパフォーマンスレベルでも、アクティブ Geo レプリケーションを使用することができるようになっています。
詳細については、概要: アクティブ geo レプリケーションと自動フェールオーバー グループ で解説されています。
この仕組みですが、AlwaysOn 可用性グループの非同期コミットモードがベースとなっています。
(おそらく、分散型可用性グループ のテクノロジが使われているのかと)

アクティブ geo レプリケーションは SQL Server の Always On テクノロジーを活用し、スナップショット分離を使用してプライマリ データベース上のコミットされたトランザクションを非同期的にレプリケートします。

AlwaysOn 可用性グループは、SQL Server のインスタンス間で、プライマリからセカンダリにデータベースの同期を行う機能です。

冒頭で紹介した記事では、次のように記載されています。

特定の時点におけるセカンダリ データベースは、プライマリ データベースよりもわずかに古い可能性がありますが、セカンダリ データには部分トランザクションが含まれないことが保証されます。

この動作の基本原理を理解するためには、次の二つのポイントがあります。

1. プライマリからセカンダリには、トランザクションログが連携される
2. セカンダリは、受け取ったトランザクションログを REDO スレッドにより、データに反映させる

トランザクションログの連携については、AlwaysOn 可用性グループの重要な部分となっており、可用性モード (AlwaysOn 可用性グループ) で解説されています。
SQL Database も AlwaysOn 可用性グループをベースとしているので、この考え方は同様です。
プライマリで更新された内容については、トランザクションログ送信されることで、セカンダリに対して連携されます。
「同期コミットモード」の場合は、次のような流れになります。

トランザクションログの書き込みが完了したかどうかの応答をプライマリが受け取ったらコミットを完了させるというような「コミットタイミングでセカンダリのトランザクションに書き込みが完了したこと」を確認して、トランザクションを完了させるという仕組みになります。
「非同期コミットモード」の場合は、ACK の応答は待ちません。

プライマリは、セカンダリの ACK を待つことなくトランザクションを完了させ、セカンダリには非同期的にトランザクションログのレコードが送信されることになります。
そのため、非同期モードの場合は、データ差が発生する可能性があります。
(Box の SQL Server の場合、非同期モードのデータ送信間隔は、ユーザー側で設定はできず、SQL Server 任せで送信されます)
ぺんぺん師匠のブログには、

セカンダリへの反映は一定量の変更をセカンダリにトランザクションログを用いてします。
反映対象は、トランザクションが完了したものになります。

 
と書かれていますが、これをミスリードしないように気を付ける必要があります。
「反映対象は、トランザクションが完了したものになります。」というのは、ログファイルへのデータ転送ではありません。
トランザクションログの送信と、セカンダリのログファイルに対してのログレコードの書き込みについては、トランザクションが完了 (コミット) しなくても実行されます。
プライマリで次のクエリを実行してみます。

SET NOCOUNT ON
GO
DROP TABLE IF EXISTS T1
CREATE TABLE T1 (
	C1 char(900),
	C2 uniqueidentifier,
	CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED(C1)
)
GO
BEGIN TRAN
	INSERT INTO T1 VALUES(NEWID(), NEWID())

 
このクエリは未コミット (トランザクションが完了していない) のクエリとなります。
しかし、このレコードはセカンダリに連携が行われています。
 
セカンダリで次のクエリを実行してみます。

SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = 'dbo.T1.PK_T1'

このクエリはトランザクションログのレコードを検索するものなのですが、プライマリで実行された INSERT のレコードがセカンダリにも連携がされていますね。

トランザクションログについては、未コミットのものもセカンダリに連携されます。
それでは「反映対象は、トランザクションが完了したものになります。」はどのような事象を指すのでしょうか？
これが、「セカンダリは、受け取ったトランザクションログを REDO スレッドにより、データに反映させる」の動作となります。
セカンダリでトランザクションログが受信された後は、「REDO スレッド」により、トランザクションログの内容がデータに反映されます。

この部分が、「反映対象は、トランザクションが完了したものになります。」の記述部分の動作になります。
先ほどのトランザクションを「COMMIT TRAN」で完了させて、次のクエリをプライマリに対して実行してみます。

RAISERROR('Insert data Start ...', 10,1) WITH NOWAIT
DECLARE @cnt int = 1
BEGIN TRAN
WHILE(@cnt <= 10000)
BEGIN
	INSERT INTO T1 VALUES(NEWID(), NEWID())
	SET @cnt += 1
END
COMMIT TRAN
GO
RAISERROR('Index maintenanse Start ...', 10,1) WITH NOWAIT
BEGIN TRAN
	ALTER INDEX PK_T1 ON T1 REBUILD --WITH (ONLINE = ON)
	UPDATE STATISTICS T1
COMMIT TRAN
GO

このクエリを実行している最中に、セカンダリで次のクエリを実行してみます。

SELECT
	tl.request_session_id,
	tl.resource_type,
	tl.request_mode,
	tl.request_type,
	tl.request_status,
	tl.request_owner_type,
	OBJECT_NAME(p.object_id) AS object_name,
	i.name,
	er.command,
	er.wait_type,
	COUNT(*) AS lock_count
FROM
	sys.dm_tran_locks AS tl WITH(NOLOCK)
	INNER JOIN
		sys.dm_exec_requests AS er WITH(NOLOCK)
	ON
		er.session_id = tl.request_session_id
		AND
		er.wait_type LIKE 'REDO%'
	LEFT JOIN
		sys.partitions AS p WITH(NOLOCK)
	ON
		p.hobt_id = tl.resource_associated_entity_id
	LEFT JOIN
		sys.indexes AS i WITH(NOLOCK)
	ON
		i.object_id = p.object_id AND i.index_id = p.index_id
GROUP BY
	tl.request_session_id,
	tl.resource_type,
	tl.request_mode,
	tl.request_type,
	tl.request_status,
	tl.request_owner_type,
	p.object_id,
	i.name,
	er.command,
	er.wait_type
GO

 
このクエリは、REDO スレッドによる確保されているロックの情報を取得するものです。
実行すると次のような結果が取得できます。

通常のトランザクションであれば、ユーザーセッションがテーブルに対して更新を行います。
プライマリについてはそのような動作になるのですが、セカンダリについては異なります。
セカンダリのデータへの更新はユーザーセッションではなく、バックグラウンドで実行されている「REDO スレッド」により、セカンダリのデータに対して反映が行われます。
そのため、データに対してロックを取得しているセッションについは、REDO スレッドのセッションとなります。
これが「反映対象は、トランザクションが完了したものになります。」の対象となるのかと。
REDO スレッドがデータファイルに反映させるのは、トランザクションが完了したものになり、未コミットのものについては、ログレコードとしては反映されるが、REDO スレッドによる反映対象とならないというのが、上記の記載の意味かと。
今回は「Basic」の DTU でアクティブ Geo レプリケーションを構築しているため、REDO スレッドは一つのみ起動していますが、SQL Server 2016/2017: Availability group secondary replica redo model and performance に記載されているように、SQL Server 2016 以降は、REDO スレッドが CPU のコア数に応じて複数起動するようになっています。
REDO スレッドによりデータの反映が行われないと、

• 実データとしての更新が遅延し、読み取りセカンダリから情報を読み取った際に、最新のデータにならない
• サーバーに障害が発生し、起動したタイミングのロールフォワード処理に時間がかかり、DB が開始されるのに時間がかかる

というようなことが考えられます。
大量のデータの更新が行われる環境では、単一の REDO スレッドでは、トランザクションログのデータを反映させるのに時間がかかり、データの最新化が遅延する可能性があります。
これを回避 / 緩和させるために、SQL Server 2016 以降は複数の REDO スレッドが起動できるようになっています。
1 スレッドで発生させられる I/O には限界がありますが、これを複数のスレッドで並列で実行させることで、データへの反映速度が向上し、データの最新化のタイミングも早いものとなります。
最近の、SQL Server の可用性機能は、SQL Database から取り込まれたものが多いような気がしますので、SQL Server の可用性機能で機能拡張が行われた場合、「それがどのようなシナリオを想定した実装されたのか」を考えてみると、SQL Database の仕組みの理解にもつながるのではないでしょうか。