はじめに
こんにちは、デジタルトランスフォーメーション事業部の吉倉です。
先日、プロジェクトメンバーより、スキーマの変更を検出してログに出すことはできないか？という質問を頂きました。
SQLServerを使っているパッケージがあり、データ定義をする管理者画面を操作すると、裏でデータベースのスキーマ変更が行われ、テーブルを作ったり、カラム追加・削除がされたりします。
前提として、裏で実行されているため、スキーマ変更の内容は隠蔽されています。
また、スキーマ変更の実行履歴を確認するための機能はこのパッケージにはありません。
でも、見たいのはこのスキーマ変更の実行履歴なのです。
開発環境からステージング環境に移行する時に、前回移行時点からの差分を取ったりするのですが、実行履歴が見れないがゆえに、「あれ、変更内容ってこれで全部なんだっけ？」ということに迷ったりするわけです。
そこで、スキーマの変更がなされたタイミングとその内容(実行されたDDL)のログを取れれば、前回移行時点からの差分が過不足なく問題ないものであるか、の判断に使えるのではないかと。
やってみよう
ざっくり書くと、やってみたことは以下2点です。
- データベースSampleOriginal 内のテーブルのスキーマ変更を捉える
- スキーマ変更内容は別データベースに登録する
スキーマ変更内容を登録するテーブルを作成
データベースSampleDDLLogを用意し、テーブル MigrationHistory を作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [SampleDDLLog] GO CREATE TABLE dbo.MigrationHistory ( id bigint NOT NULL IDENTITY (1, 1), modified_on datetimeoffset(7) NULL, ddl_text nvarchar(MAX) NULL, event_name nvarchar(50) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE dbo.MigrationHistory ADD CONSTRAINT PK_MigrationHistory PRIMARY KEY CLUSTERED ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.MigrationHistory SET (LOCK_ESCALATION = TABLE) GO |
テーブルには以下のカラムを作成します。
- id
- modified_on(実行時刻)
- ddl_text (実行されたDDL)
- event_name (どの種類のDDLが実行されたか)
データベースSampleOriginalにシノニム作成
データベースSampleOriginal から、データベースSampleDDLLogに登録するために、シノニムを作成します。
シノニムは他のデータベースオブジェクトを別名で参照できる機能です。
これを作っておくことで、データベースSampleOriginal で発行したSQLで、データベースSampleDDLLogのテーブルに書き込みができます。
|
1 2 3 4 |
USE [SampleOriginal] GO CREATE SYNONYM [dbo].[MigrationHistorySynonym] FOR [SampleDDLLog].[dbo].[MigrationHistory] GO |
スキーマ変更内容を捉える
DDLトリガーを作成します。
詳細は、MicrosoftのSQLドキュメント、DDLトリガーを参照。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [SampleOriginal] GO CREATE TRIGGER [ModifySchemaTriger] ON DATABASE FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE AS DECLARE @data XML SET @data = EVENTDATA() INSERT INTO dbo.MigrationHistorySynonym SELECT SYSDATETIMEOFFSET() AS modified_on, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') AS ddl_text, @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(MAX)') AS event_name ; |
これにより、データベース内のイベントを捉えられるようになります。
捉えるイベントは、FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE で指定したテーブルのスキーマ変更、テーブル削除、テーブル作成のイベントです。
指定できるイベントは、MicrosoftのSQLドキュメント、DDLイベントを参照。
ストアドプロシージャの変更なども捉えられます。
イベントが発生したら、EVENTDATA()を参照してシノニムに対してINSERT文を発行します。
EVENTDATA()に持っている項目は、MicrosoftのSQLドキュメント、EVANTDATAを参照。
ALTER TABLEだと、EVENT_INSTANCE_ALTER_TABLEのスキーマで返ってくるので、以下の項目が取れます。
この中から、TSQLCommand と、EventTypeを使っています。
これで設定完了です。
実行してみます
テーブルを作成してみます。
以下のスクリプトを実行してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [SampleOriginal] GO CREATE TABLE [dbo].[Sample1]( [id] [bigint] NOT NULL, [col1] [nchar](10) NULL, [col2] [int] NULL, CONSTRAINT [PK_Sample1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO |
実行後、MigrationHistoryテーブルをSELECTすると、以下の通り登録されました。
カラムの追加をしてみます。
- col1を削除
- col2の型を変更
- col3を追加
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
USE [SampleOriginal] GO CREATE TABLE dbo.Tmp_Sample1 ( id bigint NOT NULL, col2 bigint NULL, col3 datetime NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Sample1 SET (LOCK_ESCALATION = TABLE) GO IF EXISTS(SELECT * FROM dbo.Sample1) EXEC('INSERT INTO dbo.Tmp_Sample1 (id, col2) SELECT id, CONVERT(bigint, col2) FROM dbo.Sample1 WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.Sample1 GO EXECUTE sp_rename N'dbo.Tmp_Sample1', N'Sample1', 'OBJECT' GO ALTER TABLE dbo.Sample1 ADD CONSTRAINT PK_Sample1 PRIMARY KEY CLUSTERED ( id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO |
テーブルを削除してみます。
|
1 2 3 4 5 |
USE [SampleOriginal] GO DROP TABLE [dbo].[Sample1] GO |
やりたいことは満たせたようです。
おわりに
スクラッチで作っているシステムなら、スキーマ変更はMigrationファイルや、DDLのスクリプトを管理するところですが、パッケージの場合はそうも行かないところがあります。
そういう時に、DDLトリガーで検出できるということを頭の片隅に置いておいていただければ幸いです。