More than 3 years have passed since last update.

【SQL Server】Update-DatabaseではなくScript-Migrationをつかう

Posted at 2021-09-07

はじめに

Docker の SQL Server を試している過程で
Update-Databaseでデータベースを更新することは本番には適していないと知った

理由は以下の2点

・SQL コマンドはツールによって直接適用されます。開発者はこのコマンドを検査または変更することはできません。これは、運用環境では危険である可能性があります。
・.NET SDK と EF ツールは、実稼働サーバーにインストールする必要があります。

1点目については、SQL コマンドを精査できるほど知識がないので何とも言えない
2点目については、身に覚えがある
- 本番のマシンだろうが構わずSDK（ランタイム）やら解析ツールをインストールするようなダラけた精神は叩き直すべき（自身のこと）

運用データベースへの移行は、SQL スクリプトがおススメです

先述の公式ドキュメントでは、移行をデータベースに反映させる方法が4つ紹介されている

方法	コマンド	運用データベースへの適正
SQL スクリプト	`Script-Migration`	〇適している
べき等 SQL スクリプト	`Script-Migration -Idempotent`	〇適している
コマンドラインツール	`Update-Database`	×適していない
実行時に移行を適用する	`context.Database.Migrate()`	×適していない

今回は、べき等 SQL スクリプトを試す

べき等 SQL スクリプトの特徴を引用する（引用元）

既に適用されている移行を内部で確認し、不足しているもののみを適用しますこれは、データベースに最後に適用された移行の内容を正確に把握していない場合、またはそれぞれが異なる移行に配置されている複数のデータベースに配置する場合に便利です。

データベースがどこまで移行されているかを関知せずに実行できるらしく、扱いやすそう

表1行目と2行目の違いは？

1番目も2番目も同じScript-Migrationコマンドを使うから、動作は同じはず
ただ、1番目のセクションでは**「-From・-Toオプションを使うと移行の始点と終点を指定することができるよ」と書いてあり、やや上級者向け（細かく指定したい人向け？）にみえた
なので、今回はべき等に動作する2番目をチョイス**した

SQL スクリプトをつかって、`Blogging`データベースを移行してみる

この記事に至るまで、Bloggingデータベースをつくって初期データを与えるまでの2つの移行（マイグレーション）を行っている
移行はUpdate-Databaseをつかってデータベースに反映させてきた
（※ 繰り返しになるが、Update-Databaseは運用データベースに向いていない）

今回は、Bloggingデータベースをつくって初期データを与えるまでの2つの移行（マイグレーション）をSQL スクリプトにより行う

SQL スクリプトを生成する`Script-Migration`

まずは、SQL スクリプトを生成する

SQL スクリプトを生成するときは、-Vオプションを付けておくとよい
-Vオプションがないと、生成された SQL スクリプトの場所がわからない

パッケージマネージャーコンソール（最終行にsqlファイルの出力先が書いてある）

Script-Migration -Idempotent -Context BloggingContext -Project Intro -StartupProject WPF_EFCore -V

# Using project 'Intro'.
# Using startup project 'WPF_EFCore'.
# Build started...
# Build succeeded.
# ---中略---
# Writing '{.slnファイルがあるディレクトリ}\Intro\obj\Debug\netcoreapp3.1\jzy0rczc.sql'...

objディレクトリ以下、おそらくビルド環境に合わせたディレクトリに降りていけば、そこにsqlファイルが生成されているはず

生成されたsqlファイルの全容は付録に載せる

移行先のデータベースをつくる

この記事と同じ操作で、Docker の SQL サーバにログインして作業する

いろいろデータベースの中で作業してきたので、いったん壊して作り直す
SQL スクリプトはデータベースの作成までは関与していない（付録のコード参照）

-- データベース削除
1> DROP DATABASE Blogging;
2> GO
-- データベース作成
1> CREATE DATABASE Blogging;
2> GO

SQL スクリプトを実行する

スクリプトもコマンドラインで打ちたかったけど、Docker for Windows のマウントで詰まったので、さくっと
SSMSの力を借りる
（sqlcmdで同じ操作をする方法は別記事にする予定）

さきほど作ったBloggingデータベースを選択し、SQL スクリプトを実行する

実行すると成功のメッセージが表示される

なお、続けざまに実行してもコマンドは正常に完了しました。と表示されるのみで
べき等 SQL スクリプトである（同じ処理をすれば、結果は一意に決まる）ことがわかる

SQLスクリプトを複数回実行したあとのテーブル

-- Blogsテーブル
1> SELECT * FROM Blogs;
2> GO
BlogId Url Rating
------ --- ------
11 http://one.com 0
22 http://two.com 0

(2 rows affected)

-- Postsテーブル
1> SELECT * FROM Posts;
2> GO
PostId Title Content BlogId
------ ----- ------- ------
11 title11 content11 11
22 title12 content12 11
33 title23 content23 22
44 title24 content24 22

(4 rows affected)

余談

マイクロソフトの公式ドキュメントを日本語で表示すると、オプション引数も日本語表記になる

文字通りにコマンドを打ったら怒られた

パッケージマネージャーコンソール

Script-Migration -べき等
Script-Migration : パラメーター名 'べき等' に一致するパラメーターが見つかりません。
発生場所 行:1 文字:18

おわりに

これまではUpdate-Databaseしか使ってこなかった

今回はべき等 SQL スクリプトを試してみた

生のSQL文が見えるのは、たしかに安心感があり、手を加えてみたくなる

参考にさせていただいた記事

付録

Script-Migrationにより生成された.sqlファイル

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE TABLE [Blogs] (
        [BlogId] int NOT NULL IDENTITY,
        [Url] nvarchar(max) NULL,
        [Rating] int NOT NULL,
        CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
    );
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE TABLE [Posts] (
        [PostId] int NOT NULL IDENTITY,
        [Title] nvarchar(max) NULL,
        [Content] nvarchar(max) NULL,
        [BlogId] int NOT NULL,
        CONSTRAINT [PK_Posts] PRIMARY KEY ([PostId]),
        CONSTRAINT [FK_Posts_Blogs_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blogs] ([BlogId]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE INDEX [IX_Posts_BlogId] ON [Posts] ([BlogId]);
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'2021**********_Init', N'5.0.9');
END;
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    EXEC(N'INSERT INTO [Blogs] ([BlogId], [Rating], [Url])
    VALUES (11, 0, N''http://one.com'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    EXEC(N'INSERT INTO [Blogs] ([BlogId], [Rating], [Url])
    VALUES (22, 0, N''http://two.com'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'PostId', N'BlogId', N'Content', N'Title') AND [object_id] = OBJECT_ID(N'[Posts]'))
        SET IDENTITY_INSERT [Posts] ON;
    EXEC(N'INSERT INTO [Posts] ([PostId], [BlogId], [Content], [Title])
    VALUES (11, 11, N''content11'', N''title11''),
    (22, 11, N''content12'', N''title12''),
    (33, 22, N''content23'', N''title23''),
    (44, 22, N''content24'', N''title24'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'PostId', N'BlogId', N'Content', N'Title') AND [object_id] = OBJECT_ID(N'[Posts]'))
        SET IDENTITY_INSERT [Posts] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'2021**********_Seed', N'5.0.9');
END;
GO

COMMIT;
GO

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up