SQL Server のトランザクションログの内容を確認する際のアプローチとして「DBCC LOG 」や「sys.fn_dblog」を利用して内容を確認するという方法があります。
これらの DBCC や関数はアンドキュメントとなっており、詳細な情報は公開されていません。
次のようなクエリでテーブルを作成してみます。
CREATE TABLE [dbo].[T1]( [C1] [int] NULL, [C2] [int] NULL, [C3] [char](19) NULL, [C4] [varchar](20) NULL, [C5] [nvarchar](20) NULL ) ON [PRIMARY] GO
このテーブルに対して「8GB=1かずあき」を基準とした、テストデータをかずあきって見たいと思います。
CHECKPOINT INSERT INTO T1 VALUES(1, 2, '16GB=2かずあき', '16GB=2かずあき', N'32GB=∞かずあき')
かずあきった後にトランザクションログを確認してみます。
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE Operation = 'LOP_INSERT_ROWS' AND AllocUnitName = 'dbo.T1'
「LOP_INSERT_ROWS」という操作が「dbo.T1」に対して実行されたという情報がトランザクションログに書き込まれていることが確認できます。
このような方法でトランザクションログを確認することで「どのような操作が行われたか?」の大枠を把握することについては、広く知られている方法ではないでしょうか。
トランザクションログの内容を活用した機能としては「レプリケーション」や「変更データキャプチャ (CDC)」がメジャーかと思います。
次の画像は 変更データキャプチャについて (SQL Server) に記載されている画像です。
レプリケーションや変更データキャプチャは「トランザクションログから必要となる操作のログレコードをキャプチャし、操作を記録するテーブルに格納をする」というようなアプローチがとられています。
また、サードパーティー製品としてはトランザクションログの内容を分析して、更新内容に実データを表示するツールも存在しています。
トランザクションログには、変更の内容が記録されており、正しく分析を行うことで「どのような変更があったかの実際のデータを復元することができる」ということです。
冒頭で紹介した、「sys.fn_dblog」からはトランザクションログの実際のデータを参照することができます。
「Log Record」がログレコード全体の内容となっており、この関数では、この情報解析してユーザーが理解しやすい形に加工して結果を出力しています。
ただし、すべてのデータが加工されているわけではありません。
変更後のレコードについては「RowLog ontents x」に格納されています。
先ほど INSERTした際の該当の情報を見てみましょう。
「RowLog Contents x」のフィールドについてはバイナリデータのままの表示となっており、そのままではどのような変更があったかまでは表示されていません。
しかし、このバイナリデータには内部的にきちんとフォーマットがあり、このバイナリデータをフォーマットに則って解析することで、先ほどの INSERT によって、どのようにデータが格納されたのかをきちんと確認することができます。
実際に解析をしたものがこちらになります。
INSERT を実行した場合は「RowLog Contents 0」に INSERT したデータのバイナリ化された情報が格納されています。
先ほどの INSERT の RowLog Contents 0 を解析すると次のようなデータとして復元することができます。
(以下の画像の内容は、実際に RowLog Contents 0」を解析して、更新内容を復元したものです)
今回の例でいえば、「RowLog Contents 0」には次のようなバイナリデータが格納されています。
0x30001F000100000002000000313647423D3282A982B882A082AB2020202020050000020036004A00313647423D3282A982B882A082AB33003200470042003D001E224B305A3042304D30
これをバイナリデータのフォーマットを意識しながらパースすると次のような情報を得ることができます。
Operation については、sys.fn_dblog のデータをそのまま使っていますが、StatusBit 以降のデータは RowLog Contents 0 をパースして取得しています。
冒頭で記載した INSERT でかずあきった内容が取得できていますね。
これは DELETE や UPDATE でも同様のアプローチが使用することができ、トランザクションログの内容を復元することができます。
(UPDATE の場合は、更新前後の情報が格納されますので、RowLog Contents 0 だけでなく、RowLog Contents 1 の情報も解析する必要がありますが考え方は同じです)
レプリケーションのログリーダーやサードパーティ製の製品が、「どのようなデータ変更が行われたかをトランザクションログから取得できる」のは、「RowLog Contents」に変更された実データが格納されており、正しいフォーマットに則りバイナリデータの解析を行っているからです。
元のテーブルの構造がわからないと、上記のような分析はできないはずなのですが、頑張ると「トランザクションログがどのようなレコードを生成しているのか」を可視化することも可能です。
このようなトランザクションログの分析についての情報ですが、日本語の情報はほとんど見たことがない気がします。
しかし、海外の情報を視野に入れると「SQL Server Forensic Analysis」(SQL Server の法医学分析) という考え方の中で情報を入手することができます。
SQL Server Forensic Analysis はセキュリティ観点で、攻撃があった場合に「SQL Server にどのようなことが行われたのかの真実を分析する」ための手法となります。
その中には「トランザクションログの分析」も含まれており、ログに含まれる内容からデータを復元するために、トランザクションログのバイナリデータを解析する際に必要となるフォーマットについても触れられています。
興味のある方は、「SQL Server Forensic Analysis」で検索してみて下さい。
次のようなドキュメントを確認することができます。
これらのドキュメントは SQL Server 2005 のものですが、SQL Server 2019 でも基本的な考え方は同じであり、上記のかずあきったデータについては、SQL Server 2019 のログをパースしたものとなります。
Forensic Analysis で開設されている情報を元にすると、作成したスクリプトの抜粋ですが、次のようなバイナリデータの解析で、トランザクションログの中に含まれているデータ変更の実体を確認することができます。
(データのパースは本来はテーブル定義から列のデータ型を取得してどのようにパースするかを求めるのが望ましいのですが、今回はモックなので直接適切なデータ型にマッピングしています)
$variableRowoffset = $rowLog0.RowOffset $rowLog0 = New-Object LogRecord $rowLog0.Operation = $dt.Rows[0].Operation $rowLog0.StatusBit = $log[0] $rowLog0.notUsed = $log[1] $rowLog0.RowOffset = [BitConverter]::ToInt16($log[2..3], 0) $rowLog0.c1 = [BitConverter]::ToInt32($log[4..7], 0) $rowLog0.c2 = [BitConverter]::ToInt32($log[8..11], 0) $rowLog0.C3 = [System.Text.Encoding]::GetEncoding(932).GetString($log[12..30]) $rowLog0.numColumns = [BitConverter]::ToInt16($log[$rowoffset..($variableRowoffset + 1)], 0) $rowLog0.nullBitmap = [Convert]::ToString($log[($variableRowoffset + 2)], 2) $rowLog0.variableColumns = [BitConverter]::ToInt16($log[($variableRowoffset + 3)..($variableRowoffset + 4)], 0) $rowLog0.endOffsetC4 = [BitConverter]::ToInt16($log[($variableRowoffset + 5)..($variableRowoffset + 6)], 0) $rowLog0.endOffsetC5 = [BitConverter]::ToInt16($log[($variableRowoffset + 7)..($variableRowoffset + 8)], 0) $rowLog0.C4 = [System.Text.Encoding]::GetEncoding(932).GetString($log[41..($rowLog0.endOffsetC4 - 1)]) $rowLog0.C5 = [System.Text.Encoding]::GetEncoding(1200).GetString($log[($rowLog0.endOffsetC4)..($rowLog0.endOffsetC5 - 1)])
トランザクションログの厳密な解析はあまり得意ではないので、SQL Server の勉強は果て無く続きますねぇ。