データベースのスキーマ変更を検出してみる(SQLServer)

はじめに

こんにちは、デジタルトランスフォーメーション事業部の吉倉です。

先日、プロジェクトメンバーより、スキーマの変更を検出してログに出すことはできないか?という質問を頂きました。

SQLServerを使っているパッケージがあり、データ定義をする管理者画面を操作すると、裏でデータベースのスキーマ変更が行われ、テーブルを作ったり、カラム追加・削除がされたりします。
前提として、裏で実行されているため、スキーマ変更の内容は隠蔽されています。
また、スキーマ変更の実行履歴を確認するための機能はこのパッケージにはありません。
でも、見たいのはこのスキーマ変更の実行履歴なのです。

開発環境からステージング環境に移行する時に、前回移行時点からの差分を取ったりするのですが、実行履歴が見れないがゆえに、「あれ、変更内容ってこれで全部なんだっけ?」ということに迷ったりするわけです。

そこで、スキーマの変更がなされたタイミングとその内容(実行されたDDL)のログを取れれば、前回移行時点からの差分が過不足なく問題ないものであるか、の判断に使えるのではないかと。

やってみよう

ざっくり書くと、やってみたことは以下2点です。

  • データベースSampleOriginal 内のテーブルのスキーマ変更を捉える
  • スキーマ変更内容は別データベースに登録する

スキーマ変更内容を登録するテーブルを作成

データベースSampleDDLLogを用意し、テーブル MigrationHistory を作成します。

テーブルには以下のカラムを作成します。

  • id
  • modified_on(実行時刻)
  • ddl_text (実行されたDDL)
  • event_name (どの種類のDDLが実行されたか)

データベースSampleOriginalにシノニム作成

データベースSampleOriginal から、データベースSampleDDLLogに登録するために、シノニムを作成します。
シノニムは他のデータベースオブジェクトを別名で参照できる機能です。
これを作っておくことで、データベースSampleOriginal で発行したSQLで、データベースSampleDDLLogのテーブルに書き込みができます。

スキーマ変更内容を捉える

DDLトリガーを作成します。
詳細は、MicrosoftのSQLドキュメント、DDLトリガーを参照。

これにより、データベース内のイベントを捉えられるようになります。
捉えるイベントは、FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE  で指定したテーブルのスキーマ変更、テーブル削除、テーブル作成のイベントです。

指定できるイベントは、MicrosoftのSQLドキュメント、DDLイベントを参照。
ストアドプロシージャの変更なども捉えられます。

イベントが発生したら、EVENTDATA()を参照してシノニムに対してINSERT文を発行します。
EVENTDATA()に持っている項目は、MicrosoftのSQLドキュメント、EVANTDATAを参照。
ALTER TABLEだと、EVENT_INSTANCE_ALTER_TABLEのスキーマで返ってくるので、以下の項目が取れます。


この中から、TSQLCommand と、EventTypeを使っています。
これで設定完了です。

実行してみます

テーブルを作成してみます。
以下のスクリプトを実行してみます。

実行後、MigrationHistoryテーブルをSELECTすると、以下の通り登録されました。

カラムの追加をしてみます。

  • col1を削除
  • col2の型を変更
  • col3を追加

テーブルを削除してみます。

やりたいことは満たせたようです。

おわりに

スクラッチで作っているシステムなら、スキーマ変更はMigrationファイルや、DDLのスクリプトを管理するところですが、パッケージの場合はそうも行かないところがあります。

そういう時に、DDLトリガーで検出できるということを頭の片隅に置いておいていただければ幸いです。

最近の記事

  • 関連記事
  • おすすめ記事
  • 特集記事

コメント

この記事へのコメントはありません。

アーカイブ

カテゴリー

PAGE TOP