SQL Serverのデータ同期方法を比較してみた (レプリ・CDC・変更の追跡・マイクロバッチ）

背景

SQL Serverにおいて、データを別サーバーと同期したいときに使える手段について調べてみました。
今回は変更の追跡、CDC、レプリケーション、マイクロバッチ方式について検討してみました。

※BCPとAlways ONなどが他の手段として挙げられると思いますが、今回は比較対象外としています。
※変更の追跡とCDCの比較が主となっており、網羅的な内容ではありません。

変更の追跡

変更の追跡機能を使うと、変更があったレコードの主キーが専用のテーブルに格納されます。

--1
ALTER DATABASE TEST 
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
CREATE TABLE [dbo].[bcp_in](
    [C1] [nvarchar](30) NOT NULL,
    [C2] [int] NULL,
    [C3] [int] NULL,
    [C4] [nvarchar](max) NULL,
    [C5] [datetime] NULL,
 CONSTRAINT [PK_bcp_in] PRIMARY KEY CLUSTERED 
(
    [C1] ASC
))

--2
ALTER TABLE bcp_in  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

--3
truncate table bcp_in
CHECKPOINT

--4
INSERT INTO bcp_in VALUES(SUBSTRING(CAST(NEWID() AS varchar(36)),1,20), 1,1,1,GETDATE())
select * from sys.fn_dblog(NULL, NULL)
declare @last_synchronization_version bigint;

トランザクションログの中身。
テーブルへのINSERTと変更の追跡用テーブルへのINSERTが同一のトランザクションIDで行われていることがわかる。
したがって、同期的な処理となるため、1トランザクションあたりの実行時間が伸びる。

変更の追跡についての確認クエリ

--変更の履歴のバージョン番号の最新の値を取得する＝次回の同期処理実行時に、どのバージョン番号以降の変更の履歴を追えばいいかが判断できる
SELECT CHANGE_TRACKING_CURRENT_VERSION()

-- 3なら3より大きいバージョン番号の全ての変更の履歴を取得する
SELECT  
*
FROM  
    CHANGETABLE(CHANGES bcp_in, 3) AS CT

挙動の検証

→バージョン番号の指定の違いで、「指定したバージョン番号から現在の状態までもっていくために必要な操作」として見せてくれる。

バージョン番号が1の場合はまだレコードが無いのでINSERT、18の場合はレコードはあるが、そのあとにUPDATEが走っているのでU（UPDATE）となっている。

異なるバージョン番号で２回UPDATEされていたとしても、最新の１レコードだけがとれてくる

初回同期をする際は、バージョン番号1を指定すると全レコード取得できる。

変更の追跡のまとめ

変更の追跡だと、書き込みの時間的オーバーヘッドの増加が最大の懸念。

変更データキャプチャ(CDC)

変更データキャプチャを使うと、変更があったレコードと、どのカラムに変更があったのかまで一度に分かる。
アーキテクチャはこちら

「変更は、非同期プロセスを使用してトランザクション ログを読み取ることによってキャプチャされます。これは、システムへの影響が少ない方法です。」とあり、トリガのような同期的なものではないので、データ更新時の時間的なオーバーヘッドの増加は無さそう。

オーバーヘッド

トランザクションログから読み取る処理と、変更履歴用テーブルに対しての書き込みが発生する。
そのため、トランザクションログの読み取り負荷が多少上昇する見込み。

検証

参考URL
https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2012/cc627369(v=sql.110)
https://qiita.com/tsurumiii/items/91f95add623b8c24f8d5

CDCの有効化と、対象テーブルの追加設定を行う。

EXEC sys.sp_cdc_enable_db
GO


EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name = N'bcp_in', 
@role_name = N'cdc_role', 
@filegroup_name = N'primary',
@supports_net_changes = 1
GO

↑テーブルを更新後にCDC用のテーブルをSELECTすると、レコードがはいっている。

↑トランザクションIDもCDCだと別のIDとなっており、非同期的であることが分かる。

挙動の検証

18:30ごろに以下のクエリを投げた

update bcp_in set c2 = 3 where c1 = '00B475C2-E2FF-4699-8'

そのあと、以下のクエリを実行

DECLARE @from_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2017-01-01');
DECLARE @to_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '2020-05-14 18:21');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_bcp_in ( @from_lsn , @to_lsn , 'all') order by __$start_lsn, __$operation 
go

DECLARE @from_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2017-01-01');
DECLARE @to_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '2020-05-14 18:31');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_bcp_in ( @from_lsn , @to_lsn , 'all') order by __$start_lsn, __$operation

→C1= '00B475C2-E2FF-4699-8'について、C2の値が一個目と二個目のクエリとで違っている。
cdc.fn_cdc_get_net_changes_dbo_bcp_in()に指定する、特定の日時のLSNの間で起きた変更だけを取得できる。

CDC vs 変更の追跡

write heavyなワークロードでなければ、変更の追跡でもありかもしれない。

レプリ vs CDC vs 変更の追跡

レプリケーションが遅延するような状況下で、同一のサーバーでCDCや変更の追跡に切り替えたとして遅延は防げるのか？

まず、レプリケーションがどういう状況で遅延するのかを理解する必要がある。
１．ログリーダーの読み取りの遅延
　→ CDCも同一の仕組みを使っているので、ログリーダーが遅延すればCDCも遅延する。変更の追跡は影響は受けない。
２．ディストリビューターからサブスクライバに配布する処理の遅延
　→ アプリの組み方次第で高速化できる可能性はあるが、CDCならLSNを意識しながら整合性のある更新反映処理を高速化できるか次第になってくる。
　　　（指定したLSNの範囲で最終的なデータの状態だけが返ってくるため、LSNの範囲を複数に区切って、オペレーション（__$operation）の順番通りに再生すれば整合性のある結果で、かつ並列処理による高速化ができる見込みあり）
　　変更の追跡の場合は、LSNを意識する必要はないので、並列化等による高速化が可能。

vs マイクロバッチ

ModifyDT / RegistDTを見て読み取る方式の場合、「削除されたデータはわからないでOK」という点と、「アプリ側で完ぺきにModiyDTの更新が行われている状態が整っている」という点を前提とするなら、遅延が起きずに

高速に同期できる方式である可能性はある。

ただ、整合性を担保するために一時的にマスタ側にロックをかける必要はある。

select * from Table where RegistDT > dateadd(minutes, -1, getdate() or ModifyDT > dateadd(minutes, -1, getdate()

なので、数十万レコードのSELECTが発生すると、長時間共有ロックを取得してブロッキングにつながる懸念はある。
これやるならRead Committed SnapshotをONにしておきたいところ。

プロダクション環境で設定する際の懸念について

設定のクエリをプロダクション環境で実行するときはオンラインでいける見込み

ALTER DATABASE TEST SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

テーブルの適用についてもオンラインでいける見込み

ALTER TABLE bcp_in ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

変更の追跡の導入時の検証方法について

検証ステップ１

・開発環境で、変更の追跡を導入する前後で、同一ワークロードに負荷面および実行時間面でどういった変化があるかを実験する
　→環境によらない一般的な知見を得ておく

検証ステップ２

・プロダクション環境で、導入予定のテーブルを作成
・コピーしたテーブルに対して、様々なシナリオを用意して変更の追跡のON・OFFにおける実行速度や負荷の違いを評価する
　→ベンチマーク用のバッチの処理時間や、exec_query_statsを評価に用いる

・変更の履歴の管理用テーブルのサイズチェック

検証ステップ３

・同期したいテーブル群から1つピックアップして、変更の追跡の設定をいれる
・実際のワークロードでの負荷や時間についての変化をチェックする
　→理想的には、同一のステートメントで評価できると◎
・数日間運用してみて、変更の履歴の管理用テーブルのサイズチェック