BT

InfoQ ホームページ アーティクル データベースのバージョニングとアップグレードスクリプトの利用

データベースのバージョニングとアップグレードスクリプトの利用

ブックマーク

原文(投稿日:2013/08/23)へのリンク

1.はじめに

データベースのアップグレードはいつでもデリバリ作業の"最後の一歩(last mile)"です。プロジェクトが終わるまで,あるいはリリース直前のスプリントまで残ってしまうことも少なくありません。完璧と呼ぶには程遠いものです。なぜなら:

  • テスト環境にソフトウェアをデプロイする度に,データベースが再構築されるような場合もしばしばです。テスタにとってこれは,毎回テストデータを失うことに他なりません。
  • プロジェクトが長期間に及ぶ場合には,最初のデータベース更新が実施されてから何ヶ月も後にアップグレードスクリプトが書かれることも珍しくありません。その頃にはデータ移行に関する情報が失われてしまったり,損なわれてしまっているかも知れないのです。
  • 製品版のリリースに対して,事前にアップグレードスクリプトを徹底的かつ反復的にテストすることができません。これは失敗のリスクを極めて大きなものにします。
  • アップグレードスクリプトの作成に必要な時間を見積もることは難しいため,提供日の遅延や予算超過のリスクがより一層大きくなります。

このような問題を回避するために経験から学んだのは,データベースの優れたバージョン管理とデリバリ戦略は大部分のエンタープライズプロジェクトで必須である,ということです。私たちがObjectivityでそれをどのように扱っているのか,ここでご紹介しましょう。

2. アジャイルの観点

私たちのプロジェクトはアジャイル手法で進行されています。すなわちアプリケーションは増加的,かつ反復的に開発されます。データベースはこのソフトウェア開発プロセスの一部です。ここではすべてが "完了の定義(DoD/Definition of Done)" から始まります。あらゆる高機能チームにとってこれは,非常に重要なことです。また,ユーザストーリのレベルでのDoDには,"デプロイ可能"という条件を含める必要があります。つまり,スクリプトを介して自動的にデプロイ可能な場合にのみ,ストーリが完了したと解釈するのです。もちろんDoDの条件はこれだけではありません。この話題だけでひとつの記事になってしまう位,他にもたくさんの条件があります(データベースのアップグレードスクリプト作成もそのひとつです)。

こうして作り上げられたDoDは,スプリントの計画や見積にも影響を与えます。主要なタスクをすべて考慮したかどうかを評価する,チェックリストとして使用するのです。データベースに関して言えば,アップグレードスクリプトを書く上でプロジェクトに適用されたルールについて,各チームメンバに周知しておく必要があります – どのようなフォーマットか? テンプレートは使用するのか? ファイルをどに置くのか? ファイルで遵守すべき名称規約はあるのか? 等々。

開発期間中のコードやデータベースは,開発者たちによって並行的に変更されます。そこでデータベースプロジェクトを修正したときには,同時にアップグレードスクリプトも記述するようにします。作成した成果は,他のコードとともにチェックインして,専用の環境でユーザストーリ (User Story/US)をテストするために使用されるのです。

スプリントが完了して,ソフトウェアの実運用移行の決定が下されたならば,スクリプトは他の成果物と合わせてインストレーションの実行に使用されます。

3.バージョニングのアプローチ

データベースのバージョニングの詳細な実施方法はプロジェクトによってまちまちですが,キーとなる要素は常に存在します。それは次のようなものです。

  • データベースがバージョン管理されていること – 自明な出発点です。データベースの変更を特定できなければ,どうやってアップグレードスクリプトを書くことができるのでしょう? 私たちはVisual Studio 2010データベースプロジェクト,またはRedGate SQL Source Controlを使用しています。いずれもTFSリポジトリを対象としてデータベースの構造を管理するものです。この分野では,ツールがかなりの部分をサポートしてくれます。
  • データベースのバージョンがデータベース自体に格納されていること – ある環境にインストールされているDBスキーマのバージョンを識別可能にするためには,データベースそれ自体に記録しておくことが必要です。実現方法はいろいろあります(ユーザ定義関数や拡張プロパティ,特定の名前のオブジェクトなど)が,ObjectivityではDbVersionという特別なテーブルを使用しています。この方法のメリットとして,テーブルは標準的なデータベースオブジェクトなので,開発者や管理者も扱い方をよく知っていますし,コードからのアクセスも容易な点が挙げられます。データベースには現在のバージョンを記録しても,あるいはバージョン歴をすべて格納しておいてもよいでしょう。テーブル定義の例をリスト1に挙げておきます。
  • データベースのバージョンをアプリケーション起動時に検証すること – アプリケーションにデータベースのバージョンを確認するコードを埋め込んでおいて,起動時に検証を行います。所定の条件が満たされていなければ,適切なエラーを表示して停止するようにします。このベストプラクティスは,多くのデプロイエラーから開発者チームを開放すると同時に,テスト作業が無駄になるリスクを最小化してくれます。
  • アップグレードスクリプトを開発と並行して作成すること – 開発者がデータベーススキーマを変更する時には,データベースを更新するSQLスクリプトも同時に用意するようにします。私たちはそのために,テンプレートを用意するようにしています (リスト2のサンプル参照)。テンプレートの最初の部分では,データベースバージョンが期待したものであるかを確認しています。適切なバージョンであれば,トランザクションを開始します。データベースに所定の変更(この部分は開発者が作成します)を行った後,バージョンテーブルを更新した上でトランザクションをコミットし,正常終了のメッセージを表示します。このプラクティスは,「はじめに」の章で述べたリストの中の,第2と第4の問題に対処するためのものです。

4.ハイブリッドなソリューション

データベースが(データサイズではなく)オブジェクト数の面で大規模な場合,特にストアドプロシージャやユーザ定義関数を多数使っているような場合には,アップグレードスクリプトが非常に大きなものになることがあります。対応策のひとつとして,アップグレードスクリプトの対象を特定のオブジェクトタイプに制限する方法があります。一般的には実際にデータをストアする部分(例えばテーブル)を対象として,他のオブジェクトはアップグレードプロセスの最後に再インストールするようにしておきます。このようなハイブリッドソリューションが特に推奨されるのは,チームのデータベースアップグレードに関する経験が浅い場合や,広範なビジネスロジックがデータベースに保存されているような場合です。

5.データについては?

データは一般的に,2つのグループに区別することができます。

  • 初期データ – 参照データやディクショナリなど,アプリケーションの起動時および/または実行時に必要なデータ。
  • ビジネスデータ – アプリケーションUIから生成されたデータ,外部ソースからインポートされたデータ,あるいは開発者やテスタが何らかの作業目的で事前に作成しておいたデータ。

新たな"最後の一歩"問題を回避するためにも,これらのグループはプロジェクトを開始する時から区別しておいた方がよいでしょう。

私たちは,グループ毎のフォルダに置いたスクリプト/CSVを使ってデータベースのデータを初期化するか,あるいは(小規模システムでデプロイを容易にするため)アップグレードスクリプトに初期化データを埋め込んでいます。データを別々のフォルダに置く場合には,複数回の実行が可能なようにスクリプトを記述しておくのがベストです。もうひとつ対処しなければならない問題が,テーブルにデータを挿入する順番です。複雑なデータベーススキーマ(循環参照のあるような)では,テーブルに順序付けすることができません。この場合は事前に外部キーを無効にしておいて,データ格納後に再度有効にするのがベストプラクティスです。

6.バージョニングのベストプラクティス

以降のプラクティスは必須ではありませんが,非常に有効なものです。新たにプロジェクトを始めるのであれば,少なくとも検討はするべきでしょう。

3項目のバージョン文字列を使用すること

私たちの経験では,次のようなデータベースバージョン文字列がもっとも柔軟性があります。

<メジャーバージョン>.<マイナーバージョン>.<リビジョン>

最初の項目はメジャーリリースや,2ヶ月に1回といったシステムフェーズで更新します。残る2つの項目は開発者が指定します。マイナーバージョンは,互換性のない修正(必須フィールドの追加など)がデータベースに導入されて,"旧"アプリケーションが新しいスキーマに適合しなくなった場合に更新します。データベースに互換性のある修正(インデックスやテーブル,省略可能なフィールドの追加など)が加えられた場合は,リビジョンを増やします。

環境に依存しないスクリプトを記述すること

アップグレードスクリプトは理論上,環境の変更を一切必要とせずに動作するような方法で記述するべきです。これはすなわち,パスやデータベースインスタンス名,SQLユーザ名やリンクしているサーバ設定といった情報を含んではならない,という意味です。Microsoft SQL Serverを使っているのであれば,SQLCMD変数をこの目的で使用することができます。より詳しい情報はこちらを参照してください。

複数のチームがひとつのデータベース上で作業している場合は,データベース全体をいくつかのスキーマに分割すること

複数のチームが並行して大規模なデータベース開発を行う場合,データベースを複数のスキーマに分割しておくと,より効率的になります。すべてのスキーマにはそれぞれ,独自のバージョンとアップグレードスクリプトを用意します。こうすることによって,コード統合時のコンフリクトが最小かつ単純になるのです。当然ですが,DbVersionテーブルはスキーマバージョン(新たな列)を格納できるように修正しなければなりません。この場合のスキーマには,2つのタイプ - 共有(shared)と専用(dedicated) - があります。あるチームが共有スキーマを変更したいと考えている場合は,他のチームの意見を聞いた上で,もっともよい方法でオブジェクト構造を修正する必要があります。一方の専用スキーマについては,チームの完全なコントロール下にあります。

あるいはもし,データベースがレガシであって新たなスキーマを導入できなければ,データベースをいくつかの仮想領域に分割して,それぞれ別々にバージョンを付けることも可能です。

チェックインしたアップグレードスクリプトは決して変更しないこと

データベースにバージョンが明記されていれば,ユーザは単にそれを使用するでしょう。オリジナルと比較して変更点を確認するとは限りません。そのため,データベースに適用されたアップグレードスクリプトのバージョンが違っていると,その状況を見つけ出すのが困難になります。もしアップグレードスクリプトの誤りによってデータベースを更新してしまったならば,元に戻す別のスクリプトを書いてください – オリジナルのスクリプトを変更してはいけません。すでにどこかの環境で適用された後かも知れないからです。

複数のリリースを開発する場合は,一定範囲のバージョンを確保してマージを単純化すること

複数のチームが同一システム/データベースの連続的なリリースの開発を並行して行っているような場合,各チームが使用するバージョンの範囲について前もって合意しておけば,マージ上の問題を回避することができます。

例えば,リリース1を開発しているチームAが共有スキーマの2.x.xと注文スキーマの1.x.xを,リリース2に向けて開発を行うチームBが共有スキーマの3.x.xと帳票スキーマの1.x.xを使用する,というようにです。

アップグレードプロセスは自動化しておくこと

開発と並行してアップグレードスクリプトを書くことの欠点は,それが多数になることです。それゆえ,開発者やリリース管理者などにとって,作業時間を節約するための自動化が強く望まれます。同時にデリバリ作業をスピードアップし,回復性も向上します。さらに自動化することで,アップグレードプロセスをCIプロセスにプラグインすることも容易になるのです。

ObjectivityではPSakeモジュール(PowerShell)を使用して,プロセスを自動化してます。PowerShellはMicrosoftのタスク自動化フレームワークで,.NET上に構築されたスクリプト言語に基づいています。PSakeの方はPowerShellで記述されたドメイン固有言語で,MSBuildのRakeに似た依存パターンを使用してビルドを生成するものです。PSakeビルドスクリプトは,複数のTaskで構成されます。各タスクは関数で,他のタスクへの依存を定義することができます。私たちObjectivityでは,アップグレードスクリプトをひとつのPSakeタスクとして記述するようにしています。

私たちの使用しているデータベースアップグレードのアルゴリズムを紹介しましょう。

  1. 既存のデータベースのバージョンをチェックする
  2. 現在のバージョンに対応するアップグレードスクリプトを検索する (このステップでは,DBバージョンに連動したファイル名称規約を利用しています)
  3. ファイルが見つかればそれを実行して,アウトプットを検証する - 終了またはエラー
  4. スクリプトがなければ,処理を終了する
  5. ステップ1に戻る

サンプル実装をリスト3に掲載します。

CI時にアップグレードスクリプトを検証すること

私たちObjectivityでは,データベースのアップグレードプロセスに不慣れな開発者が,プロジェクトの採用したアップグレードスクリプト記述ルールを誤って破ってしまうことがしばしばあります。継続的インテグレーション(Continuous Integration/CI)サーバにコミットする度に以下の項目をチェックして,アップグレードスクリプトの一貫性を検証しておくとよいでしょう。

  • ファイル名称規約 – 私たちは次のようなファイル名を使用しています: <prefix>_<current_version_from_db_version_table>_<target_version>_<optional_info_about_upgrade>.sql
    例: Upgrade_1.0.1_1.0.2_rename_column.sql
    複数のスキーマを使用する場合は,プレフィックスにスキーマ名を含めます。
  • ファイルの内容 – ヘッダとフッタをチェックすれば,テンプレートが使用されていることを確認できます。もうひとつ,ファイルの内容のバージョンと,ファイル名のバージョンとの検証も可能です。

この検証は,実際のコードを構築する前でも行うことができます。違反があった場合には,そのビルドを失敗させる必要があります。

アップグレードスクリプトを実行した結果のデータベース構造が,開発中のデータベースプロジェクトのデータベースと同じであるかどうか,検証することを強く推奨します。私たちは2つのデータベースインスタンスを使って,それを実現しています。

  • 第1のインスタンスは,アップグレードスクリプトの適用対象であるシステムから取得した,作業者に依存しないコピーをリストアしたもの。これを,
  • データベースプロジェクトから生成した第2のインスタンスと比較する。何らかの差異があれば,ビルドは失敗する。

更新前にデータベースをバックアップすること

アップグレードスクリプトはトランザクションとして記述されてはいますが,正常に終了する保証はありません。したがって万一に備えて,更新前にバックアップを行っておくのが理想です。このステップは自動化しておく必要があります。

アップグレードスクリプトの実行歴を記録すること

テスト中にデータベース関連の問題が発生した場合,対象となるデータベースに適用された更新の履歴があれば有効です。更新プロセスが自動化されていれば,実行されたすべてのアップグレードスクリプトをデバッグ用の専用テーブルに記録することも難しくありません。リスト4にサンプルのDbHistoryテーブル定義を載せてあります。

リスト1 – DbVersion定義

カラム名

カラム型

 

Version

Nvarchar(50)

Not null

UpdatedBy

Nvarchar(50)

Not null

UpdatedOn

DateTime

Not null

Reason

Nvarchar(1000)

Not null

リスト2 – アップグレードスクリプトのテンプレート

DECLARE @currentVersion [nvarchar](50)
DECLARE @expectedVersion [nvarchar](50) DECLARE @newVersion [nvarchar](50)
DECLARE @author [nvarchar](50)
DECLARE @textcomment [nvarchar](1000) SET @expectedVersion = '10.0.217'
SET @newVersion = '10.0.218'
SET @author = 'klukasik'
SET @textcomment = 'Sample description of database changes'

SELECT @currentVersion = (SELECT TOP 1 [Version] FROM DbVersion ORDER BY Id DESC)
IF @currentVersion = @expectedVersion
BEGIN TRY
BEGIN TRAN
-- ################################################ BEGIN OF SCRIPT ############################################################
--
#############################################################################################################################

-- custom database modifications

--################################################# END OF SCRIPT ############################################################
--
#############################################################################################################################
INSERT INTO DbVersion([Version],[UpdatedBy],[UpdatedOn],[Reason])
VALUES(@newVersion, @author, getdate(), @textcomment)
COMMIT TRAN
PRINT 'Database has been updated successfully to ' + @newVersion
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
DECLARE @ErrorMessage NVARCHAR(max),
@ErrorSeverity INT,
@ErrorState INT;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN;
END CATCH;
ELSE
BEGIN
PRINT 'Invalid database version - expecting: ' + @expectedVersion + 'currently: ' + @currentVersion
END

リスト3 – PSake UpgraeDatabaseタスクとPowerShellヘルパ

Task UpgradeDatabase -depends Initialize -description "Upgrades db with SQL scripts" {
$logFile = "$log_dir\DatabaseUpgrade.log"
if (Test-Path $logFile)
{
Remove-Item $logFile }
$connectionString = $script:tokens["@@ConnectionString@@"]
$getVersionQuery = "SELECT TOP 1 Version FROM dbo.DbVersion ORDER BY [Id] DESC"
$dbConnectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$dbConnectionStringBuilder.set_ConnectionString($connectionString)
$dbVersion = Get-DbVersion $dbConnectionStringBuilder $getVersionQuery
Write-Output ("Initial db version is {0}" -f $dbVersion)
while ($true)
{
$files = Get-ChildItem ("$database_upgrade_scripts_dir\Upgrade_{0}_*.sql" - f $dbVersion)
if ($files -ne $null)
{
$upgraded = $true
foreach ($file in $files)
{
Write-Output ("[$($dbConnectionStringBuilder.DataSource) / $($dbConnectionStringBuilder.InitialCatalog)] Upgrading with {0}..." -f $file.Name)
$sqlMessage = Run-Sql $file $dbConnectionStringBuilder $true
$nl = [Environment]::NewLine
Write-Output ("Executing $file.$nl$sqlMessage") | Out-File $logFile-append
if (! ($sqlMessage -like "*Database has been updated successfully to*"))
{
throw "Something went wrong. See $logFile" }
}
$dbVersion = Get-DbVersion $dbConnectionStringBuilder $getVersionQuery
if ($upgraded)
{
Write-Output ("Db version is {0}" -f $dbVersion)
}
}
else
{ break
}
}
}
function Run-Sql($inputFile, $dbConnectionStringBuilder, [bool]$isFile) {
$database = $dbConnectionStringBuilder.InitialCatalog
$ps = [PowerShell]::Create()
$e = New-Object System.Management.Automation.Runspaces.PSSnapInException | Out-Null
$ps.Runspace.RunspaceConfiguration.AddPSSnapIn( "SqlServerCmdletSnapin100", [ref]$e ) | Out-Null
$param = $ps.AddCommand("Invoke-Sqlcmd").AddParameter("database", $dbConnectionStringBuilder.InitialCatalog).AddParameter("serverinstance", $dbConnectionStringBuilder.DataSource).AddParameter("Verbose").AddParameter("QueryTimeout", 120)
if ($isFile) {
$param = $ps.AddParameter("InputFile", $inputFile)
} else {
$param = $ps.AddParameter("Query", $inputFile)
}
if (!$dbConnectionStringBuilder.ContainsKey("Integrated Security") -or[System.Convert]::ToBoolean($dbConnectionStringBuilder."Integrated Security") -eq $false) {
$param = $param.AddParameter("username", $dbConnectionStringBuilder."User ID").AddParameter("password", $dbConnectionStringBuilder.Password)
}
try {
$ps.Invoke() | Out-Null
} catch {
Write-Output $ps.Streams
throw
}
$sqlMessage = ""
$nl = [Environment]::NewLine
foreach ($verbose in $ps.Streams.Verbose) {
$sqlMessage += $verbose.ToString() + $nl
}
foreach ($error in $ps.Streams.エラー
$sqlMessage += $error.ToString() + $nl
}
return $sqlMessage
}
function Invoke-SqlCmdSnapin ($dbConnectionStringBuilder, $query) {

if (!$dbConnectionStringBuilder.ContainsKey("Integrated Security") -or[System.Convert]::ToBoolean($dbConnectionStringBuilder."Integrated Security") -eq $false) {
Invoke-SqlCmd -query $query `
-database $dbConnectionStringBuilder.InitialCatalog `
-serverinstance $dbConnectionStringBuilder.DataSource `
-username $dbConnectionStringBuilder."User ID" `
-password $dbConnectionStringBuilder.Password
} else {
Invoke-SqlCmd -query $query `
-database $dbConnectionStringBuilder.InitialCatalog `
-serverinstance $dbConnectionStringBuilder.DataSource
}
}

リスト4 – DbHistory定義

カラム名

カラム型

 

Filename

Nvarchar(50)

Not null

Content

Nvarchar(max)

Not null

RunOn

DateTime

Not null

7.最終考察

データベースのバージョニングとデリバリ戦略は,大部分のエンタープライズプロジェクトにおいて重要なものです。今回の記事をその指標として,既存のソリューションやプラクティスの再検討に,あるいはスクラッチからの新たな構築に役立てて頂ければと思います。すべてのルールが適応できることはおそらくないでしょうが,少なくともデータベースのアップグレード計画を客観的に評価する上での手助けにはなるはずです。さらなる詳細を知りたい場合,あるいは本記事のアドバイスに対する感想や他の重要なアイデアをお持ちの場合は,質問やコメントをお寄せ頂けください。可能な限り早急にお返事します。私への連絡はEメールklukasik@objectivity.co.ukまでお送りください。

著者について

Konrad Lukasik氏はMicrosoftの技術全般,特に.NETに関する支持者のひとりです。ほぼ10年の間,プロフェッショナルとして業界で経験を積んできました。現在はObjectivityでテクニカルアーキテクトを務め,チームが高品質のソフトウェアを提出ための支援をしています。氏は物事を "よりシンプルにではなく,可能な限りシンプルに" するための努力を続けています。

この記事に星をつける

おすすめ度
スタイル

こんにちは

コメントするには InfoQアカウントの登録 または が必要です。InfoQ に登録するとさまざまなことができます。

アカウント登録をしてInfoQをお楽しみください。

HTML: a,b,br,blockquote,i,li,pre,u,ul,p

コミュニティコメント

HTML: a,b,br,blockquote,i,li,pre,u,ul,p

HTML: a,b,br,blockquote,i,li,pre,u,ul,p

BT

あなたのプロファイルは最新ですか?プロフィールを確認してアップデートしてください。

Eメールを変更すると確認のメールが配信されます。

会社名:
役職:
組織規模:
国:
都道府県:
新しいメールアドレスに確認用のメールを送信します。このポップアップ画面は自動的に閉じられます。