SQL Server Profilerを使ってSQL Serverテーブルの変更をチェックする

2024-07-27

SQL Serverテーブルの変更をチェックする方法

Change Trackingを使う

Change Trackingは、テーブルレベルで変更されたデータを追跡する機能です。有効にすると、どの行が挿入、更新、削除されたかを追跡できます。

メリット

  • 比較的軽量な機能
  • 設定が簡単
  • クエリで変更内容を取得できる
  • 変更されたデータの内容は追跡できない
  • 古い情報は自動的に削除される
  1. データベースとテーブルで変更の追跡を有効化する
  2. CHANGETABLE() 関数を使って変更を取得する

トリガーを使う

トリガーは、特定のイベントが発生したときに実行されるプログラムです。テーブルの変更を検知したい場合は、INSERT、UPDATE、DELETEトリガーを作成することで、変更内容を記録することができます。

  • 複雑な処理を記述できる
  • Change Trackingより負荷が高い
  • 設定が複雑になる

トリガーを使う方法

  1. トリガーを作成する
  2. トリガー内で変更内容を記録する

変更データキャプチャを使う

変更データキャプチャは、データベースの変更をリアルタイムでキャプチャする機能です。変更されたデータの内容だけでなく、変更日時やユーザー情報なども取得できます。

  • リアルタイムで変更を取得できる
  • 高度なフィルタリング機能
  • 設定が複雑
  • 負荷が高い

変更データキャプチャを使う方法

  1. 変更データキャプチャを有効化する
  2. キャプチャされたデータを取得する

上記以外にも、以下の方法で変更をチェックすることができます。

  • SQL Server Profilerを使う
  • 監査ログを使う
  • テーブルのハッシュ値を比較する

これらの方法は、上記3つの方法よりも複雑であったり、機能が限定されていたりします。



USE AdventureWorks2019;

-- テーブルの変更の追跡を有効にする
ALTER TABLE Person
ENABLE CHANGE TRACKING;

-- 変更を取得する
SELECT *
FROM CHANGETABLE(Person, ALL);

USE AdventureWorks2019;

-- トリガーを作成する
CREATE TRIGGER trg_Person_Change
ON Person
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 変更内容を記録する
    INSERT INTO dbo.ChangeLog (TableName, ChangeType, DateTime, Data)
    VALUES ('Person', TRIGGER_EVENT, GETDATE(), (SELECT * FROM INSERTED));
END;

-- トリガーを有効にする
ALTER TRIGGER trg_Person_Change
ON Person
ENABLE;

-- 変更をテストする
INSERT INTO Person (FirstName, LastName) VALUES ('John', 'Doe');

-- 変更ログを確認する
SELECT * FROM dbo.ChangeLog;

USE AdventureWorks2019;

-- 変更データキャプチャを有効にする
EXEC sys.sp_cdc_enable_db;

-- キャプチャされたデータを取得する
SELECT * FROM cdc.lsn_time_mapping;
SELECT * FROM cdc.captured_columns;
SELECT * FROM cdc.change_tables;


  • 非常に詳細な情報が取得できる
  • 特定のユーザーやアプリケーションによる変更のみをトレースできる

SQL Serverは、データベースに対する操作を監査ログに記録することができます。監査ログを解析することで、変更内容を取得することができます。

  • すべての操作が記録される
  • 高いセキュリティ
  • ログの量が膨大になる
  • 解析が複雑

テーブルのハッシュ値を定期的に計算し、比較することで変更をチェックすることができます。ハッシュ値が一致すれば変更されていない、一致しなければ変更されていることが分かります。

  • 非常に軽量な方法
  • ハッシュ値の衝突が発生する可能性がある
  • 変更されたデータの内容までは分からない

sql sql-server datatable

SQL SQL SQL SQL Amazon で見る