---

November 2017

Volume 33 Number 11

Devops - Visual Studio と TFS を使用した継続的データ移行

Jebarson Jebamony

完全に新規のプロジェクトでも、アプリケーションの再設計を行う場合でも、アプリケーション開発の取り組みにおいて、データ移行が必要になることは珍しくありません。それにもかかわらず、設計フェーズや開発フェーズ中はデータ移行にほとんど注意が払われず、移行関連の作業は頻繁にプロジェクトの後半に先延ばしになります。こうしたアプローチは一気に集中して移行に取り組むことができそうですが、いくつかリスクがあります。中でも、移行とその周辺のコードを適切にテストできる時間とリソースが足りなくなるリスクは問題です。

継続的なデータ移行という概念は、アプリケーションの開発中に移行スクリプトを開発し、アプリケーション コードのようにバージョン管理するという発想に基づきます。継続的な移行アプローチは、コード開発と平行して移行をテストでき、データとコード資産の整合性を保つことができます。

今回は、Visual Studio と Team Foundation Server を利用して、継続的なデータ移行を実現するソリューションを取り上げます。Red Gate Ready Roll など、ある程度データ移行機能を備えたサード パーティ製のツールもありますが、そのようなツールは膨大なコストがかかるうえ、継続的なデータ移行の機能がありません。

課題とソリューション

Visual Studio では、SqlPackage.exe を利用することでデータベースの差分のみを発行できますが、SqlPackage.exe はさまざまな点で役不足です。たとえば、SqlPackage.exe では、テーブルの列と列の間への新しい列の挿入、シード データの変更、テーブルの正規化と非正規化ができず、その他にも対応できないことがあります。

また、バージョンの変更は、対象を指定した修正や配置が必要なときに非常に重要です。たとえば、v1.2 から v1.3 に移行するときにのみ、列の値を 10 ずつ増やす必要があり、その他のフローではそれが不要だとします。これはバージョン管理を利用しなければ実現できませんが、SQL にはその機能がありません。

ここでは、上記の欠点を克服しつつ、Visual Studio と SqlPackage.exe で得られる機能を駆使したソリューションを設計することで、この課題に取り組みます。

典型的なデータベース プロジェクトでは、コンパイル済みスクリプトと非コンパイル スクリプトの 2 種類のスクリプトが使用されます。スキーマ、テーブル、ビュー、ストアド プロシージャなどのオブジェクトはすべて、基本的にコンパイル済みスクリプトとして作成します。シード スクリプトとあらゆるランタイム クエリは、通常、コンパイルなしの、配置後スクリプトに含められます。

それでは例を見ていきましょう。図 1 は、サンプル データベース プロジェクトの Adventure­Works.Database です (bit.ly/2vPwu4N (英語) から入手できるバックアップからインポート)。ご覧のように、すべてのオブジェクトはコンパイル済みスクリプトとして配置されています。

図 1 AdventureWorks.Database のコンパイル済みスクリプト

シード データ スクリプト (アプリケーションが機能するために必要なデータを含むスクリプト) は、非コンパイル スクリプトとして配置し、配置後スクリプトからこれらを参照します。図 2 は、この状態を示しています。配置後スクリプトの意味をご存知ない場合は、MSDN ライブラリのドキュメント (bit.ly/2w12Iy4) をご覧ください。

図 2 配置後スクリプト

配置後スクリプトが増分配置を処理できるように、すべての INSERT ステートメントの前に NOT EXISTS 句を追加しています。以下に例を示します。

IF NOT EXISTS (SELECT 1 FROM [Person].[AddressType] WHERE [AddressTypeID] = 1)
INSERT [Person].[AddressType] ([AddressTypeID], [Name], [ModifiedDate]) VALUES (1 N’Billing’, CAST (N’2008-04-30T00:00.000’ AS DateTime))

簡潔に、メンテナンスしやすくするため、シード スクリプトはすべて個別のファイルで保持し、配置後スクリプトから参照します。

これで、どの時点でも、最新のスキーマとシード データを配置するプロジェクトが用意できます。プロジェクトが互換性に影響する変更を導入しないのであれば、既存のデータベースに対して増分配置を実行することもできます。ただし、このセクションの初めに触れた制限の影響を受けます。

また、ユーザー定義型 (UDT) が変更されると、増分配置が機能しなくなるバグがあります。残念ながら Visual Studio チームはこのバグを "修正しない" (Won't fix) 方針なので、自力で対処する必要があります。このバグの詳細については、Visual Studio Developer Community のエントリ (bit.ly/2w0zTBU、英語) を参照してください。

バージョン管理

公開するアプリケーションをバージョン管理するように、データベースもバージョン管理することが重要です。バージョン管理はソース コードの追跡にも有用で、ソフトウェアのすべてのリリースの機能、バグ、修正方法を簡単に記録できます。バージョン管理をよくご存じない場合は、semver.org の「Semantic Versioning 2.0.0」(セマンティック バージョニング 2.0.0、英語) を参照してください。一読の価値はあります。

データ移行に取りかかる前に、課題を克服しなくてはなりません。つまり、SQL にはバージョン管理のメカニズムがないため、独自に作成する必要があります。バージョンの詳細情報を格納する [internal].[Database­Version] というテーブルを作成します。この “internal” は、テーブルのスキーマです。内部目的に使用される (つまり、実際の事業には関係ない) すべてのデータベース オブジェクト用に、スキーマを分けて用意することをお勧めします。

図 3 は、このテーブル用に提案するスキーマです。お気に召さない場合は、独自のパターンを採用してください。ビルドとリリースを追跡できるように、バージョンを作成することだけは忘れないでください。

図 3 テーブル スキーマ

CREATE TABLE [internal].[DatabaseVersion]
(
 [DatabaseVersionId] INT IDENTITY(1,1) NOT NULL,
 [Major] INT NOT NULL,
 [Minor] INT NOT NULL,
 [Build] INT NOT NULL,
 [Revision] INT NOT NULL,
 [CreatedBy] NVARCHAR (256) 
CONSTRAINT [DFDatabaseVersionCreatedBy] DEFAULT ('') NOT NULL,
 [CreatedOn] DATETIME 
CONSTRAINT [DFDatabaseVersionCreatedOn] DEFAULT (GETUTCDATE()) NOT NULL,
 [ModifiedBy] NVARCHAR (256) 
CONSTRAINT [DFDatabaseVersionModifiedBy] DEFAULT ('') NOT NULL,
 [ModifiedOn] DATETIME 
CONSTRAINT [DFDatabaseVersionModifiedOn] DEFAULT (GETUTCDATE()) NOT NULL,
 CONSTRAINT [PKDatabaseVersion] PRIMARY KEY CLUSTERED ([DatabaseVersionId] ASC)
);GO

スキーマを変更するか、データ移行スクリプトをチェック インするたびに、このテーブルに新しいバージョン エントリを追加します。それにより、変更があったことを示します。現在のバージョンが 1.0.0.0 だとします。Gender フラグの問題を、値を元に戻すことで修正する移行を導入する場合、この変更を行う適切なスクリプトを追加し、さらに、バージョンを 1.1.0128.212 にして新しいエントリをテーブルに追加します。

移行

前述のとおり、Visual Studio では差分配置を実行できますが、互換性に影響する変更が含まれていてはなりません。したがって移行を設計する際はそのことを念頭に、この制限を回避する必要があります。

最初のステップでは、この移行専用のプロジェクトを作成します。図 3 のサンプルでは、AdventureWorks.Database.Migration という新しいデータベース プロジェクトを作成しています。この移行プロジェクトでは、2 種類のスクリプトを対象にします。1 つは、データ移行スクリプトです。このスクリプトは、データの移動や更新が発生した場合に、実行される必要があります。もう 1 つのスクリプトは、Visual Studio と SqlPackage.exe で処理できない、互換性に影響するスキーマの変更を対処します。どちらのスクリプトも、配置後スクリプトとしてプロジェクトに取り込まれます。このプロジェクトには、コンパイル可能なスクリプトはありません。

このシナリオの理解を深めるため、AdventureWorks サンプルについてすべて説明しましょう。このソース コードは Git リポジトリ (github.com/Jebarson/ContinuousDataMigration、英語) にアップロードしています。マスター分岐は、データベースからインポートして作成した基本プロジェクト (前述) を取り込みます。

シナリオの説明に入る前に、移行がどのように行われるかを説明します。「バージョン管理」で説明しましたが、公開される変更の 1 つ 1 つに、internal.DatabaseVersion に新しい行を追加することで、バージョンを管理しています。AdventureWorks.Database.Mi­gration プロジェクト内に、ターゲットのデータベース バージョンに基づいて適切な移行スクリプトを実行するロジックを作成します。関係するロジックについては、図 4 のフローチャートを参照してください。

図 4 移行ロジック

AdventureWorks.Database.Migration プロジェクトでは最初にデータベースの現バージョンをチェックして、それを基に最新バージョンに達するまで移行スクリプトを実行します。移行パスを決定するために使うコード スニペット (「スクリプト 1」と呼びます) を以下に示します。

DECLARE @currentDBVersion BIGINT = NULL;

-- Get the current version of the database.
SELECT TOP 1 @currentDBVersion = Id FROM [internal].[DatabaseVersion] ORDER BY [DatabaseVersionId] DESC

-- Jump to the incremental migration scripts based on the current version.
IF @currentDBVersion = 1 GOTO Version11
ELSE IF @currentDBVersion = 2 GOTO Version12
ELSE
RETURN

移行スクリプトが実行されるしくみを説明したので、今度は移行について説明しましょう。どのような処理が行われるかを理解しやすいように、いくつか架空のシナリオを使います。以前作成した基本プロジェクトでの、2 つのバージョンの変更について説明します。

バージョン 1.1: これは、基本プロジェクトに対する最初の変更です。この変更は、GitHub の Continuous Data Migration プロジェクトの v11 分岐に含まれます。このバージョンでコミットした変更は、次のとおりです。

• [HumanResources].[Em­ployee] の JobTitle 列の後に新しい IsEmployee 列を挿入。
• [Person].[AddressType] の名前を Main Office から Office に変更。
• ストアド プロシージャを変更 (移行プロジェクトに含める必要はありません)。
• ストアド プロシージャを新規作成 (移行プロジェクトに含める必要はありません)。

すべての変更はレギュラー プロジェクトの AdventureWorks.Database にそのまま適用され、新しいバージョンの行が internal.DatabaseVersion に追加されます。これで、新しい配置を行うときに、最新の変更を組み込みやすくなります。基本バージョンの既存のデータベースを v1.1 にアップグレードするには、同じ変更を移行プロジェクトに実装する必要があります。そのために、スキーマの変更とデータの変更の 2 つのセクションに分けます。IsEmployee という名前の列を挿入するのはスキーマの変更で、AddressType を Main Office から Office に変更するのはデータの変更です。

スキーマの変更は Visual Studio によって処理できます。しかし、Visual Studio では 列の追加しかできず、これはここで目的とする処理ではありません。この制限を克服するために、まず、Employee テーブルのすべての依存関係 (インデックス、制約、外部キーなど) を削除するスクリプトを生成し、その後、それらの依存関係の新しい列を正しい順序で含む一時テーブルを作成する必要があります。次に、Employee テーブルから一時テーブルにデータを移動し、Employee テーブルを削除します。最後に一時テーブルの名前を Employee に変更します。このスクリプトは、GitHub の Continuous Data Migration プロジェクトの v11 分岐にある、SchemaChangeScript.sql というファイルに含まれています。

データの変更はレコードの値を Main Office から Office に変更するだけなので、更新クエリのスクリプトを作成して対応できます。GitHub の Continuous Data Migration プロジェクトの v11 分岐にある DataChangeScript.sql を参照してください。

既存の AdventureWorks.Database に対して移行プロジェクトを実行すると、スクリプト 1 のコードによって、スキーマとデータ変更スクリプトを呼び出すスクリプトに実行が送られます。次のスニペットは、このスクリプト (「スクリプト 2」と呼びます) を示しています。

-- Script to migrate to v1.1
Version11:
:r .\Scripts\Migration\V11\SchemaChangeScript.sql
:r .\Scripts\Migration\V11\DataChangeScript.sql

EXEC [internal].[CreateDatabaseVersion] @id = 2, @major = 1, @minor = 1, 
 @build = 0128, 
 @revision = 212

バージョン 1.2: これは、v1.1 の後にコミットされた最新の変更です。同じ変更は、GitHub のプロジェクトの v12 に含まれています。このバージョンにおける変更は、次のとおりです。

• [HumanResources].[Employee] の IsEmployee を EmployeeType に変更し、新しいテーブルでは [HumanResources].[EmployeeType] を参照。
• ストアド プロシージャを変更 (移行プロジェクトに含める必要はありません)。
• テーブルを新規作成 (移行プロジェクトに含める必要はありません)。

v1.1 と同様に、レギュラー プロジェクトの AdventureWorks.Database にも同じ変更を行い、internal.DatabaseVersion に新しいエントリを追加しました。このように IsEmployee が EmployeeType に変更されて、格納できる従業員の種類が増えました。v1.1 と同じパターンに従って、この変更を行っていますが、新しい列のデータ移行は、以前の列の値を基に作成する必要があります。スキーマ変更スクリプトは、GitHub の Continuous Data Migration プロジェクトの v12 分岐にある、SchemaChangeScript.sql というファイルに含まれています。

v1.2 移行するためのプロジェクトに含めたスクリプト (「スクリプト 3」と呼びます) は、次のとおりです。

-- Script to migrate to v1.2
Version12:
:r .\Scripts\Migration\V12\SchemaChangeScript.sql

EXEC [internal].[CreateDatabaseVersion] @id = 3, @major = 1, @minor = 2, 
 @build = 0414, 
 @revision = 096

前述のとおり、Visual Studio によって増分配置はある程度処理できます。ただし、Visual Studio で対応できない項目に限っては、これまでに作成したスクリプトを使用しています。v1.1 と v1.2 のどちらにも、「移行プロジェクトに含める必要はありません」というただし書きのある項目があります。 これらの項目は、Visual Studio による増分配置が可能なためです。そこで出てくる疑問が、「移行プロジェクトに含める変更と、除外すべき変更はどれか」ということです。

図 5 に、移行のスクリプトを作成すべきかどうかを判断に役立つ、便利な早見表をまとめました。ただし、その他にも一覧に追加できる項目が見つかる可能性があります。

変更	トリアージ
テーブル/ビュー/ストアド プロシージャ/オブジェクトの新規作成	Visual Studio を利用
ビュー/ストアド プロシージャ/関数の変更	Visual Studio を利用
ユーザー定義型の変更	UDT に関連付けられているすべてのストアド プロシージャを削除します。これが前述のバグの回避策に該当します。
テーブルへの新しい列の追加	既存のテーブルから、列の順序が正しい新しいテーブルへの移行のスクリプトを作成します (github.com/Jebarson/ContinuousDataMigration (英語) を参照)。Null 許容列を追加するので、列の順序が問題にならない場合、これは不要です。
テーブルの正規化と非正規化	要件に応じて、分割またはマージを行う移行スクリプトを作成します。これは v1.2 で作成したスクリプトに似ています。
データの変更	データ変更のスクリプトを作成します。

 

図 5 移行プロジェクト早見表

これで、移行スクリプトの生成については十分説明をしたので、配置の説明に移りましょう。

既存のデータベースの最新バージョンの新しいインスタンスを配置する場合は、移行は必要ありません。これまで使用してきた例では、配置が必要なものは AdventureWorks.Database のみです。これは、Visual Studio (公開機能を使用) または SqlPackage.exe を使用して行うことができます。SqlPackage.exe を使用してこのデータベースを配置するコマンドは、次のとおりです。

SqlPackage.exe /Action:Publish /SourceFile:"AdventureWorks.Database.dacpac" /tdn:<<DatabaseName>> /tsn:"<<SQL Instance>>"

既存のデータベースに対して増分配置を実行する場合、最新のスクリプトで移行が必要になる可能性があります。つまり、移行データベースの配置も必要になります。それには、まず AdventureWorks.Database.Migration プロジェクトを配置し、その後、AdventureWorks.Database を配置します。[公開の詳細設定] ダイアログ ボックスの [配置の詳細設定オプション] にある [データベースを常に再作成する] チェック ボックスは必ずオフにしてください (図 6 参照)。

図 6 [公開の詳細設定] ダイアログ ボックス

SqlPackage.exe /Action:Publish /SourceFile:"AdventureWorks.Migration.Database.dacpac" /tdn:<<DatabaseName>> /tsn:"<<SQL Instance>>" /p:CreateNewDatabase = False
SqlPackage.exe /Action:Publish /SourceFile:"AdventureWorks.Database.dacpac" /tdn:<<DatabaseName>> /tsn:"<<SQL Instance>>" /p:CreateNewDatabase = False

3 つのよくある移行の問題と修正方法

継続的データ移行はさまざまなメリットをもたらしますが、課題がないわけではありません。ここでは、このソリューションを実装するときに発生する可能性がある、よくある問題とその解決方法を紹介します。

移行スクリプトの新しいバージョンでオブジェクトが削除されていて、そのオブジェクトが以前のバージョンのスクリプトで参照されている場合に、移行プロジェクトで以下のエラーが発生する可能性があります。解決方法は、sp_executesql ‘<<ここに移行スクリプトを挿入>>’ というクエリを書くことです。 以下に例を示します。

EXEC sp_executesql 'ALTER TABLE Employee ADD NewCol INT'

制御対象外の移行スクリプトとバージョンのオーバーロード:

常に移行対象には最低限のバージョンを設定することをお勧めします。そうすることで、移行スクリプトの範囲が制限され、メンテナンスできないほどの状態になることを防ぎます。

運用データベースでの実装:

既に運用中のデータベースにこのソリューションを実装する場合は、internal.Database­Version の定義とバージョン エントリを含めます。internal.DatabaseVersion が存在しているかを確認し、存在していない場合は、新しいバージョン ラベルを実行の対象にするよう “スクリプト 1” を変更します。それにより、移行が実行され、テーブルも作成されます。以下に例を示します。

DECLARE @currentDBVersion BIGINT = NULL;

-- Get the current version of the database.
IF NOT EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLES].
[TABLE_NAME]='DatabaseVersion' AND [TABLES].[TABLE_SCHEMA]='internal')
SELECT @currentDBVersion = 1
ELSE
SELECT TOP 1 @currentDBVersion = Id FROM [internal].[DatabaseVersion]
ORDER BY [DatabaseVersionId] DESC
-- Jump to the incremental migration scripts based on the current version.
IF @currentDBVersion = 1 GOTO Version11
ELSE IF @currentDBVersion = 2 GOTO Version12
ELSE
RETURN

継続的移行を配置するよう TFS ビルドを構成する

継続的インテグレーションのように、ビルドがトリガーされるとビルド サーバーがデータ移行を行い、開発者やテスターにビルドを提供するような、移行の自動化は目標ではありません。次のステップでは、ビルドの公開タスクを構成します。

ビルドのタスクを作成するには、まず、継続的インテグレーション ビルドの作成方法を知っている必要があります。ご存じでない場合は、Microsoft Visual Studio サイトで公開されているチュートリアル (bit.ly/2xWqtUx、英語) をお読みください。

ビルド タスクを作成したら、データベースの配置タスクを作成する必要があります。この例では、AdventureWorks.Database.Migration プロジェクト用と、AdventureWorks.Database プロジェクト用の 2 つの配置タスクを追加する必要があります。この配置タスクは、図 7 のようになります。

図 7 配置タスク

要件を基に、詳細を入力し、トリガーを設定します。ビルドが運用できる状態になったら、アプリケーションの継続的データ移行の設定は完了です。

まとめ

今回は、複数のリリース フェーズを伴うプロジェクトでの、継続的データ移行の重要性と、Visual Studio および TFS を使用してそれを実現する方法について説明しました。継続的データ移行は、開発作業と移行のバグを削減するうえで有効です。筆者の経験では、開発に関しては移行作業の 40% に相当するメリットが得られました。また、プロジェクトの移行フェーズをなくすこともできました。

移行スクリプト自体と同じように重要なのが、移行スクリプトを TFS に統合することです。継続的データ移行プロセスは、毎日のビルドの一環として配置を行わない場合は意味がありません。ソフトウェア開発において覚えておくべき呪文は「Fail early, fail fast」 (失敗は早い段階で速やかに経験する) で、継続的データ移行はまさにそれを可能にします。

---

Jebarson Jebamony* は Microsoft サービスのシニア コンサルタントで、Microsoft や Microsoft のパートナーとお客様のためのソリューションを設計および構築しています。技術業界での経験は 14 年以上に及び、その間、さまざまな Microsoft テクノロジを扱ってきました。*

この記事のレビューに協力してくれたマイクロソフト技術スタッフの Sagar Dheram および Shrenik Jhaveri に心より感謝いたします。

---

この記事について MSDN マガジン フォーラムで議論する