使用升级脚本进行数据库版本管理及发布

1、简介

数据库升级经常被拖到发布任务的“收官阶段”，它经常被留在整个项目的最后，或者是发布前的最后一个 sprint 才完成，这种状况很不理想，因为：

• 每次软件发布在测试环境上时，数据库经常会被重建，这意味着测试人员每次都会丢失他们的测试数据。
• 如果项目的周期较长，升级脚本有时会拖延到最初的数据库改变发生后数月才开始编写，而这时关于如何迁移数据的想法可能早已遗忘，或者有所缺失了。
• 如果升级脚本没有在发布到生产环境前经过彻底并且重复性的测试，那失败的风险就非常高。
• 编写升级脚本所需的时间难以预料，这使按照预定日期和成本发布的风险再度提高了。
• 为了避免这些问题，经验告诉我，一个良好的数据库版本管理及发布策略对多数企业级项目来说是必需的。我们在 Objectivity 时是这样处理的。

2、关于敏捷

我们的项目采用了敏捷方式，意味着应用程序是渐进与迭代式进行开发的，数据库也成为这套软件开发流程中的一部分。首先要做的是定义“完成标准”（Definition of Done – DoD），这对每个高效团队都是非常重要的。用户故事（User Story）级别的完成标准应该包含一个“可发布”的条件，这表示我们只需考虑用户故事的完成，而它随后可以通过脚本自动发布。当然完成标准中还有其它很多条件（编写数据库升级脚本也是其中之一），不过这个主题完全可以自成一篇文章了。

按照这种方式制订的完成标准对 sprint 计划及预估也起到一定影响，它作为一份检查列表，可以验证是否所有的主要任务都已落实了。在数据库方面，每个团队成员都应该了解如何按照团队遵循的规则编写升级脚本：采用怎样的格式？是否使用了某种模板？文件保存在哪里？又该遵循何种命名规范？诸如此类。

在开发过程中，开发者并行地完成对代码和数据库的修改。除了对数据库项目进行改动之外，团队成员还需要编写升级脚本，随后和其它代码一起签入版本控制，并且在一个独立的环境中对用户故事进行测试。

当一个 sprint 结束后，如果决定把这部分软件功能部署到生产环境，那这些脚本会和其它必需步骤一起加入整个安装过程。

3、版本管理的方式

每个项目对数据库进行版本管理的实现细节都是不同的，但都包含了相似的关键元素，举例如下：

• 数据库由版本控制管理 – 这是一个明显的起点。如果我们不能指出数据库的变化，又如何为它们编写升级脚本呢？我们成功地应用了 Visual Studio 2010 中的数据库项目，或是 RedGate Sql Source Control 来管理数据库的结构，这两者都支持 TFS 版本库。这一部分现在已经得到了许多工具的支持。
• 数据库版本信息保存在数据库本身 – 为了检查某个指定的环境中运行的数据库架构的版本，数据库本身也需要做标记。实现这一点的方式很多（用户自定义函数、扩展属性、特殊命名的对象等等），但在 Objectivity，我们始终使用一个专门的数据库表保存版本信息，通常这张表叫做 DbVersion。这种方式的优点是，数据库表是标准的数据库对象，它为开发者及管理员广泛了解和使用，并且从代码端也可以方便地访问。数据库可以选择保存当前版本，或者是整个版本历史。可以在以下的表 1 中看到一个示例表结构定义。
• 在应用程序启动时检查数据库版本 – 在应用程序的代码内包含对数据库版本的检查功能，并在初始化时进行校验。如果某个条件没有满足，应用程序会显示某种错误信息并停止运行。这种最佳实践能使开发团队免于受困于主要的部署错误，并使得浪费测试时间的风险降到最低。
• 编写升级脚本与开发并行进行 – 在开发者修改数据库架构时，更改数据库的 SQL 脚本已事先准备好。我们为这种脚本准备了一套模板（参见表 2 中的示例）。模板的前几行会检查所期待的数据库版本，一旦版本正确，会自动启动一个事务。当特定的数据库改动（例如由开发者所编写的部分）执行完成后，模板会更新数据库版本表，提交事务并显示成功信息。这种实践解决了本文的简介部分所提到的第 2 点和第 4 点问题。

4、混合式解决方案

有些时候，如果一个数据库的对象数量太过庞大（并非指数据），升级脚本也会变得臃肿，尤其是如果我们使用了存储过程或自定义函数的时候。一种对策是尽量把升级脚本仅仅控制在某些对象种类上，通常上就是存储数据的对象（如表）。并在升级过程的最后阶段重新装载其它类型的对象。如果某个团队刚刚接触数据库升级流程，并且数据库中包含了大量的业务逻辑时，我极力推荐你这种混合式解决方案。

5、怎样处理数据？

数据基本上可以被分为两组处理：

• 初始化数据，这是运行或启动一个应用程序必不可少的，例如引用数据，数据字典等等。
• 业务数据，这是通过应用程序的界面所创建、由外部数据源导入，或者是为了让开发者和测试人员可以开始工作而预先创建的示例数据。

推荐的做法是将这两组数据从项目的一开始就进行分离，以避免在“收官阶段”才发现问题。

在初始化数据库时，我们把每一组数据分别编写成脚本或 CSV 文件，放在不同的文件夹内，或者将初始化数据内嵌在升级脚本内（这在小型系统内可以简化部署）。除了把数据放在不同的文件夹，最好在编写脚本的时候也加以留意，使编写出的脚本可以多次运行（以避免产生副作用）。另一个你需要处理的问题是数据库表的插入顺序。在复杂的数据库架构中（比如包含循环依赖的数据库），要准确地设定数据库表的顺序是不可能的，因此最好的实践是在数据插入时先禁止外键关系，等数据插入后再重新打开。

6、版本管理最佳实践

以下实践并非必需，但我发觉它们非常有用，你至少应该考虑在新项目中应用它们。

使用三部分版本字符串

我们发现用以下格式字符串表示数据库版本是非常灵活的：

< 主版本号 >.< 子版本号 >.< 修订号 >

第一部分在系统的重要发布或重大阶段会进行改变，比如每几个月一次。下面两部分是由开发者控制的。子版本改变意味着数据库中加入了破坏性的改动（例如新的必需字段），这使得“旧的”应用程序与新的数据库架构不再兼容。修订号则是每次非破坏性的变动发生时（例如新的索引、新表、新的可选字段等等）进行递增的。

编写不依赖于环境的脚本

理论上，升级脚本的编写应该允许它们在不同的环境中运行，而不需要进行改动。这意味着它不应该包括路径，数据库实例名称，SQL 用户名称及关联服务器设置等等。在 Microsoft SQL Server 中，可以使用 SQLCMD 变量达成这一目标。更多的信息可以查看这里。

如果多个团队同时在一个数据库里开发，将整个数据库划分为多个架构

如果将一个大数据库划分为多个架构，那么多个并行团队同时在数据库中开发就变得非常有效了。每个架构包括自己的版本和更新脚本，这能将代码合并的冲突降至最低。当然，DbVersion 表也需要进行相应的修改，以允许存储架构版本（新字段）。我们可以分离两种架构：共享架构及独占架构。当某个团队计划改变共享架构时，必须征询其它团队的意见，以确保对共享对象的结构修改是正确的。而独占架构则由某个团队完全控制。

另一种方案是，如果某数据库是个遗留系统，并且我们无法引入新架构，那么我们可以将数据库对象分为几个虚拟的部分，并且对每个部分分别进行版本控制。

当签入升级脚本后，永远不要修改它们

当数据库显示了它当前的版本时，你就会自然地使用它。作为开发人员，你一般不会把它与原始版本进行比较。因此如果不同版本的升级脚本被同时应用到某个数据库实例中，这种情况也是难以发现的。如果你在你的升级脚本里错误地修改了某些东西，那么就写一个新的脚本以抵消之前的修改 – 不要修改原始脚本，因为它也许已经被应用到某些环境里了。

当同时开发多个版本时，保留一定范围的版本号以简化合并操作

当多个团队在同一个系统和数据库内并行地进行多个发布号的开发时，最好能事先达成一致，为每个团队预留一定范围的版本号，以避免可能发生的合并问题。

举例来说：当前处于发布号 1 的团队 A 可以为共享架构使用 2.x.x 的版本号，为订单架构使用 1.x.x 的版本，而当前处于发布号 2 的团队则为共享架构使用 3.x.x 的版本号，并为报表架构使用 1.x.x 的版本号。

自动化升级过程

在开发过程中编写升级脚本的一个缺陷是它的数量过多。因此，自动化是理想的方案，因为它节省了开发者和发布经理等人的大量时间。另外，它也加速了整个发布过程，使得整个过程适应性更强。并且将升级过程自动化也便于将它加入持续集成的流程中。

在 Objectivity 我们使用 PSake 模块（PowerShell）以实现流程自动化。PowerShell 是微软的任务自动化框架，它包含了一个建立于.NET Framework 之上的脚本语言。另一方面，PSake 是用 PowerShell 编写的一个领域特定语言，它使用一种类似于 Rake 或 MSBuild 的依赖模式来创建构建。一个 PSake 构建包含了多个任务。每个任务是一个方向，可以定义对其它任务方法的依赖。我们的升级脚本就编写为一个独立的 PSake 任务。

这就是我们的数据库升级步骤：

1. 检查数据库的当前版本
2. 检查与当前版本相关联的升级脚本（这一步骤依赖于与数据库版本相对应的文件命名规范）
3. 如果找到了文件，则执行文件内容并验证输出，如果出现错误则退出
4. 如果没有发现脚本，则直接退出
5. 重复步骤 1

可以在表 3 中找到一个示例实现。

在你的持续集成流程中验证升级脚本

我们在 Objectivity 项目中经常发现，对数据库升级流程不熟的开发者有时会在编写升级脚本时破坏项目中采用的规则。因此最好在每次签入到持续集成服务器后验证你的升级脚本的一致性，包括检查以下内容：

• 文件命名规范 - 我们为文件名称使用以下格式： < 前缀 >_< 数据库版本表中的当前版本号 >_< 目标版本号 >_< 有关升级的其它信息 >.sql，

  例如：Upgrade_1.0.1_1.0.2_rename_column.sql

  如果使用了多个架构，则在前缀中包含架构名称。

• 文件内容 – 可以通过检查脚本的头部和尾部内容以确保使用了正确的模板，另外，可以对比文件名与内容中的版本号进行检验。

检验过程可以在代码实际构建前进行。一旦查出违反规则的情况就使这次构建失败。

如果升级脚本所针对的数据库与开发过程中使用的数据库项目有着相同的结构，我也强烈建议你进行检查，我们是通过在持续集成的过程中建立两个数据库实例来实现它的：

• 第一个实例是从生产环境恢复的版本，并且已经应用了升级脚本。
• 第二个实例是通过数据库项目所创建的。对这两个实例进行对比，一旦发现差异就立刻使这次构建失败。

在升级前备份数据库

虽然升级脚本是使用一种事务性的方式编写的，但依然不能保证它一定能通过，因此为防万一，最好在升级前首先备份。这一步骤应该实现自动化。

记录应用升级脚本的历史

如果在测试过程中发生了数据库相关的问题，这时候如果有一份对指定数据库应用更新的历史列表会很有用。如果你的升级流程实现了自动化，很容易实现将所有已执行的升级脚本记录在一个专门的历史表中，为调试提供便利。表 4 描述了一个示例的 DbHistory 表的定义。

表 1 – DbVersion 定义

Column name

Column type

**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 UpgradeDatabase 任务及 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.Error) {
       $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
     }
}

表 3 – Psake UpgradeDatabase 任务及 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.Error) {
       $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 定义

Column name

Column type

**Filename**

Nvarchar(50)

Not null

Content

Nvarchar(max)

Not null

RunOn

DateTime

Not null

7、最后感想

数据库版本管理及发布策略对多数企业级项目非常关键。使用这篇文章作为你的指南，你能够检视及改善你的现有解决方案和实践，或者打造一个全新的方案。也许并非每个规则都适用于你的情况，但至少能帮助你有针对性地评估你的数据库升级策略。如果你对我所描述的内容还需要更多的解释，或者想提出任何反馈意见，又或者你还有任何重要的提示，请把你的问题和留言发给我，我会尽快解答。你可以通过我的电子邮件找到我， klukasik@objectivity.co.uk

关于作者

Konrad Lukasik热衷于微软方面的技术，尤其关注.NET 平台。他是一位有着超过 10 年商业经验的专家。目前他在 Objectivity 担任技术架构师，帮助团队交付高质量的软件。他致力于“使事情尽量简化，但并非过于简单”。

查看英文原文： Database Versioning and Delivery with Upgrade Scripts