使用SQL Compare命令行来自动化数据库部署

SQL Compare是一款比较和同步SQL Server数据库结构的工具。现有超过150,000的数据库管理员、开发人员和测试人员在使用它。当测试本地数据库，暂存或激活远程服务器的数据库时，SQL Compare将分配数据库的过程自动化。

我们的团队为商业组织开发实际应用程序；主要是格鲁吉亚银行。这些应用程序基于MS SQL Server作为数据库的.Net-Windows-Forms应用程序。它们具有许多业务逻辑，这些逻辑包含在数据库例程中，例如存储过程、函数、视图和SQL CLR。

毫不奇怪，考虑到客户业务的性质，我们没有访问客户数据库或数据进行开发、测试或部署的权限。在TFS源代码管理下，我们只有开发数据库及其人工测试数据。开发人员在自己的数据库副本上工作，每个副本都有自己的示例数据，并且他们使用Redgate SQL Source Control提交开发更改。然后，我们使用SQL Compare命令行来自动化数据库部署。在本文中，我将解释如何实现此目标，并举例说明如何比较相同或不同分支中的数据库的两个修订版，并生成部署脚本。

在源代码管理中管理数据库

我们的数据库源代码控制和分支策略很简单。我们在Trunk中拥有最新的代码库；整个应用程序都在那里，包括其中的数据库部分。所有新功能和错误修复最初都在Trunk中进行。我们创建的每个分支只是Trunk的一个副本，因此代表了代码库的完整时间点状态。在应用了一些更改并签入Trunk之后，我们可以根据需要将其合并到这些分支中的任何一个。通常，这是为了修复已报告的错误，但当对我们的客户来说很重要时，我们还需要能够合并小的功能更改。例如，并非每个客户都能负担得起部署每个版本的费用，因此他们部署的版本通常落后三个或四个版本。但是，他们仍然需要我们为当前版本部署紧急修复程序，偶尔会使用一些“专有”功能。

那么，当我们开发软件时，这一切如何工作？让我们将其称为“under-source-control-application”（简称USCAPP）。我们在USCAPP_Trunk中拥有最新的代码库，并在TFS分支下提供了一些发行版本，称为v241、v242等。

直接或通过合并进行的所有更改都可以通过Trunk及其分支的普通TFS检入完成。在每次签入时，TFS都会创建一个称为变更集的东西，它具有唯一的参考号。变更集表示源代码管理中整个代码库的快照。像任何其他源代码控制系统一样，TFS可以针对任何给定的变更集编号，为任何修订生成代码库的时间点状态。

当然，对于集合中的所有TFS项目（包括其分支机构），TFS变更集编号都是全局的，并且每次对该项目集合进行每次检入时，TFS变更集编号都将逐渐增加。对我们来说，这意味着USCAPP_Trunk及其所有分支v241、v242等都共享相同的、全球的、不断增长的变更集号。

开发人员进行更改，每个人都在自己的专用数据库上工作，并通过SQL Source Control签入更改，这些更改将更新USCAPP_Trunk中的代码。根据需要，我们将所需的变更集合并到其他分支，在这些分支中创建新的变更集。因此，假设最新版本为v245，并且我们知道客户A已将v242部署到生产环境中。该客户尚不能升级到最新版本，但已部署了一个附加的升级脚本以修复一些错误并进行一些小的改进。换句话说，客户A正在运行非常特定的v242版本，我们可以将其转换为TFS变更集编号，该编号唯一地标识其已部署的分支v242的代码库的时间点状态。

使用SQL Compare命令行自动生成更改脚本

我们的目标是使生成同步SQL脚本的过程自动化，该脚本覆盖自上次发布脚本以来发生的所有更改。

假设客户A已经部署了分支v242，并且该数据库的发行版本标记有人类可读的版本号2.4.2.0，该版本号对应于变更集编号87300，即它是在变更集87300是当前最新版本时发布的代码库的全局变更集编号。

此后一个月过去了，我们已经在数据库中进行了更改，现在TFS中当前的更改集数量为88100。现在，我们要生成一个脚本，其中包含当月所做的每个更改，因此将数据库的v2.4.2.0升级到变更集编号88100表示的状态，我们将其称为v2.4.2.1。

为此，我们需要从TFS中检索数据库的两个时间点状态，一个代表源数据库（不会改变），另一个代表目标数据库（我们要升级）。因此，对于客户A，变更集88100代表源，而87300代表目标。我们需要比较这两种状态以找出差异，然后生成一个脚本以同步目标，以便其状态与源相同。对于两个数据库中都存在但有差异的任何数据库对象，必须更改目标中对象的定义以匹配其在源中的定义。应该创建源中存在但目标中不存在的任何对象，应该删除目标中存在但源中不存在的任何对象。

好消息是，我们不必手动执行此操作。SQL Compare GUI和SQL Compare命令行均支持此功能。我们希望使该过程自动化，因此我们使用命令行并将适当的参数传递给该命令行以生成同步脚本。我们还需要仔细记录该脚本将数据库的2.4.2.0版本升级到v2.4.2.1。当然，这里我们也需要一些保护措施。其中一项是检查，该检查将停止在不是v2.4.2.0的任何数据库上运行此脚本。在这里，我不会进行演示，但是最后，我将更详细地讨论这些需求。

比较同一分支中的两个修订

首先，我将描述我们如何发布称为“修复”的脚本，该脚本主要用于部署一些错误修复和较小的改进。主要版本保持不变。

我们使用SQL Compare命令行进行此操作，传递一个XML参数文件（argfile），该文件包含指示SQL Compare如何执行比较的所有必需命令行开关的值。或者，您可以指定每一个到命令行的开关，或在PowerShell中“splat”参数。

在这种情况下，唯一需要传递给SQL Compare的参数是XML Argfile的合格文件名，称为“shared.xml”

“％programfiles（x86）％\ Red Gate \ SQL Compare 13 \ sqlcompare” /Argfile:"shared.xml“

argfile的内容应完全按照SQL Compare命令行的在线文档中的说明填写。这是真实的示例：

<commandline>
  <SourceControl1 />
  <Revision1>88100</Revision1>
  <SourceControl2 />
  <Revision2>87300</Revision2>
  <Options>NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships</Options>
  <ScriptsFolderXML>Command Line\SourceControlAddress v242.xml</ScriptsFolderXML>
  <Filter>Command Line\Filters\Shared.scpf</Filter>
  <ReportType>Interactive</ReportType>
  <Report>Command Line\Output\Shared.html</Report>
  <ScriptFile>Command Line\Output\Shared.sql</ScriptFile>
  <Force />
  <Verbose />
</commandline>

Argfile包含五个命令行开关，我们使用它们来定义所需的行为。/ Sourcecontrol1和/ Sourcecontrol2切换指定我们的源，和目标，是源控制脚本的一个文件夹，在这种情况下，版本分别为88100和87300。

<SourceControl1 />
<Revision1>88100</Revision1>
<SourceControl2 />
<Revision2>87300</Revision2>

所述<ScriptsFolderXML>开关包含完整的文件路径为XML文件，SourceControlAddress v242.xml。该文件如下所示，包含分支v242的数据库的源代码控制地址：

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="TfsLocation">
  <ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
  <SourceControlFolder>$/USCAPP/Branches/v242/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

这是SQL Compare应从中恢复87300和88100变更集的地址。当执行SQL Compare的命令行版本时，它将把这些变更集还原为“脚本文件夹”（在撰写本文时，还原到Windows Temp中的文件夹中），并使用88100作为源和87300作为目标进行比较，以生成最终的升级脚本。

比较两个不同分支中的数据库

我们用来发布已经在Trunk中完成的所有新功能的过程与错误修正版本稍有不同，但是主要概念保持不变。同样在这种情况下，我们必须比较数据库架构的两个不同状态。即使它们的“真理来源”作为TFS源代码管理中的版本存在，它们也被导出到文件夹，作为Redgate称为“脚本文件夹”的东西。然后可以将它们作为两个数据库模式进行比较。在这种情况下，不同之处在于我们不是在一个TFS分支中比较由变更集表示的两个修订版（或时间点状态），而是在现在表示版本的两个分支之间进行比较。

要逐步进行：该过程首先从Trunk分支中创建一个新分支，并为其指定一个适当的名称。例如，如果v2.4.2是USCAPP应用程序的最后发行版本，那么在发行该版本时，我们已经创建了一个名为v242的分支。现在，我们已经对Trunk进行了更多更改，从逻辑上讲，我们将发布v2.4.3版本，因此我们的新分支将称为v243，从那时开始，就其所包含的内容而言，它将作为Trunk分支的确切副本。

现在，我们应该比较两个单独分支的两个变更集。我们用于比较的变更集必须是刚刚创建的新v243分支的变更集，并且是客户A已应用的上一个分支v242的最新发布的部署脚本所对应的变更集。此比较将揭示仅在Trunk的数据库上发生的更改，而上一个分支v242的数据库中缺少这些更改。

为此，我们需要指定一个而不是两个源代码管理文件夹位置，一个用于包含源/ ScriptsFolderXML1的TFS分支，另一个用于包含目标/ ScriptsFolderXML2的分支。我们使用SQL Compare保留关键字“HEAD”来指定我们想要源分支的最新的源控件更改集。生成的Argfile如下所示：

<commandline>
  <SourceControl1 />
  <Revision1>HEAD</Revision1>
  <SourceControl2 />
  <Revision2>88100</Revision2>
  <Options>NoDeploymentLogging,IgnoretSQLt,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,CaseSensitiveObjectDefinition,ObjectExistenceChecks,DropAndCreateInsteadofAlter,ForceColumnOrder,DoNotOutputCommentHeader,IgnoreUsersPermissionsAndRoleMemberships</Options>
  <ScriptsFolderXML1>Command Line\SourceControlAddress v243.xml</ScriptsFolderXML1>
  <ScriptsFolderXML2>Command Line\SourceControlAddress v242.xml</ScriptsFolderXML2>
  <Filter>Command Line\Filters\Shared.scpf</Filter>
  <ReportType>Interactive</ReportType>
  <Report>Command Line\Output\Shared.html</Report>
  <ScriptFile>Command Line\Output\Shared.sql</ScriptFile>
  <Force />
  <Verbose />
</commandline>

这是目标的源代码管理脚本位置XML文件（SourceControlAddress v242.xml）：

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="TfsLocation">
  <ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
  <SourceControlFolder>$/USCAPP/Branches/v242/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

这是源代码之一（SourceControlAddress v243.xml）：

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="TfsLocation">
  <ServerUrl>http://tfs:8080/tfs/projects</ServerUrl>
  <SourceControlFolder>$/USCAPP/Branches/v243/Database/Schema</SourceControlFolder>
</ISOCCompareLocation>

再一次，我们仅使用Argfile的地址作为唯一参数来调用SQL Compare命令行：

“％programfiles（x86）％\ Red Gate \ SQL Compare 13 \ sqlcompare” /Argfile:"shared.xml“

在SQL Compare命令行完成其工作之后，在“Shared.sql” 文件中，我们有了可以在目标数据库上运行的升级脚本，以将其升级到最新的主要版本。

进一步要求

在现实生活中，我们始终需要仔细检查自动生成的脚本，添加检查和控件，以确保例如以正确的顺序将所有必需的升级脚本应用到预期的数据库版本。我们还需要对SQL Compare的自动生成的部署脚本进行少量添加和自定义，例如处理数据插入或向每个脚本添加标头信息（创建脚本时，版权信息，联系信息等）。 或在每个自动生成的脚本的末尾附加一些动态生成的SQL脚本，以识别客户。

通过使用自定义迁移脚本修改SQL Compare部署，可以实现很多这样的目标，尽管实际上我们遇到了一些困难，例如它们减慢了SQL Source Control的运行速度或部署前和部署后脚本。

对我们来说，另一个需要考虑的因素是，SQL Compare迁移和部署前或部署后脚本是静态的，而我们的要求是动态生成的脚本。相反，我们在Visual Studio中构建了一个简单，轻便的工具，允许开发人员对SQL Compare脚本进行小的动态添加和自定义。

我避免在这里进行深入研究的另一个复杂性是，对于我们的每个客户，我们的源代码管理干线将客户数据库的所有共享逻辑与包含该组织专有的定制代码的小型例程结合在一起。在本文中，我演示了如何使用SQL Compare命令行来部署所有客户通用的数据库结构和代码。尽管该过程与特定于客户的例程基本相同，但是需要进行一些小的调整，以确保始终将独有功能仅部署到该客户的生产数据库中，并且没有任何客户可以看到专门为另一位客户编写的逻辑。我将在下一篇文章中描述我们如何实现这一目标！

结论

我们的经验是Redgate Source Control和SQL Compare可以协同工作，并且对我们自动化脚本生成过程起到了很大的作用。SQL Compare允许对其从Git或TFS源代码控制中提取脚本的方式进行非常精细的控制，从而为我们节省了大量手动脚本编写。我们将看到能够自动生成相应的回滚（降级）脚本以及升级脚本的更多可能性。它只需要反转我们用于源和目标的变更集并启动SQL Compare命令行即可！它是一种多功能工具。

本教程内容到这里就结束了，希望对您有所帮助~