理论与实践中的数据库CI
CI背后的理论是，如果我们每天多次将代码集成到共享存储库中，然后通过运行自动构建和后续测试来验证每个提交，那么我们会及早发现并根除问题，并提高软件的质量。
本文的内容是关于设置管道，使您能够将CI理论付诸实践，用于数据库。当我在2013年发布关于这个主题的原始文章时，它使用了SVN和Jenkins，并且证明非常受欢迎。这仍然是一条可行的路线，但在我们的行业中，半年是永恒的，其他技术和工具也越来越受欢迎。PowerShell已经成为Microsoft堆栈的主要脚本语言，而Git是每个人都流行的源代码控制工具。Jenkins，或者甚至是TeamCity，仍然可以轻松地用于CI服务器，但上个月Azure DevOps取代了Visual Studio Team Services（VSTS），感觉它是撰写2013年原始帖子新版本的最佳时机。Azure DevOps有免费套餐 任何想要学习本教程的人都可以使用。
因此，本文将使用：
- Git - 作为源控制系统。
- Azure DevOps - 托管远程Git仓库并自动化数据库构建
- Redgate SQL源代码控制（SoC） - 在本地提交数据库更改。我们克隆远程仓库然后将其链接到SoC，因此我们可以直接从SSMS提交数据库更改
- PowerShell - 将提交推送到Azure DevOps上托管的远程存储库
- Redgate SQL Change Automation（SCA） - 使用PowerShell cmdlet自动执行本地数据库构建（可选步骤）
- Azure DevOps的SCA插件 - 自动化数据库构建和部署过程
当然，仅仅因为你现在拥有一些CI工具就说你是CI实践者，就像你购买第一架望远镜时声称自己是天文学家一样。这些工具只是必要的第一步。团队仍然需要采用良好的CI实践。开发人员需要编写单元测试，证明每个小的更改都有效。他们必须尽可能多地每天多次提交对共享源代码控制存储库的更改。每次提交都应触发自动数据库构建，以确保团队始终具有可用的数据库构建过程。每天晚上，团队应该运行集成测试（本文未涉及），以证明所有单独的单元一起工作以实现所需的过程。这些CI实践自然会导致自动部署，
当然，你可以在没有这些工具的情况下练习CI，但是工具越好，你就能找到更容易和更有价值的CI，就像望远镜和天文学一样。
你需要什么来开始
本文假定您对SQL Server，Git和PowerShell有基本的了解。为了重现这个概念证明，我使用了以下软件版本：
- 具有管理员访问权限的Windows Server 2016 VM。您还应该能够在Windows 10上使用所有类似的结果。
- SQL Server 2017开发人员版。你可以在这里下载。
- Redgate SQL Toolbelt（2018年10月版）。您可以在这里下载它，它提供14天免费试用。特别是，您需要在本地工作站上安装SQL Source Control和SQL Change Automation。
- Git 2.19。你可以在这里下载。
- Azure DevOps帐户，您可以在这里免费创建一个帐户。
您可以使用该软件的较旧版本或较新版本，但由于Azure DevOps是托管服务，而且时间是一个复杂的问题，我不会对兼容性做出任何承诺。同样，如果您将来几个月或几年阅读此帖子，您可能会发现Azure DevOps UI已更改：
您还需要一个数据库来玩。我将使用StackOverflow数据库，因为它有一个简单的架构，我知道它的工作原理。对于您的第一次构建，尝试使用可以单独构建的简单内容localDB。这不包括AdventureWorks，因为localDB不支持全文搜索。此外，现在尝试避免依赖于其他数据库的数据库。
步骤1：创建新的Azure DevOps项目并克隆存储库
导航到Azure DevOps中的Projects选项卡（在我的例子中，https：//dev.azure.com/DLMConsultants/_projects）
为项目指定名称，例如数据库的名称。在版本控制下，选择Git，然后选择您的首选工作项流程。如果有疑问，默认的工作项流程（敏捷）很好，因为它对本教程没有任何影响。
将repo克隆到本地工作站。要执行此操作，请复制Azure DevOps中的HTTPS链接：
现在，打开PowerShell窗口，导航到您要存储源代码的目录并克隆repo。基本命令如下所示：
git clone https：//[email protected]/etc
这是我的完整命令窗口：
您现在应该在计算机上有一个本地存储库，其中包含一个隐藏的.git目录，您可以在其中添加数据库源代码。
第2步：将数据库链接到源
我们将使用Redgate SQL Source Control，因此请确保已安装并且您可以在SSMS中访问您的数据库。打开SSMS并右键单击要链接到源代码管理的开发或测试数据库。选择“将数据库链接到源代码管理... ”
Redgate SQL Source Control将在查询窗口中打开。选择“ 链接到我的源代码管理系统 ”，从支持的源代码管理系统中选择“ Git ”，然后粘贴到本地源代码控制存储库的路径中。然后单击“ 浏览 ”和“创建新文件夹 ”并将“ 状态 ”目录添加到源控件存储库的根目录，并将数据库链接到状态目录。其原因将在步骤2.1中变得清晰。
成功链接后，请注意RedGate.ssc文件已添加到Git 仓库中的“ state ”目录中。然后转到Redgate SQL Source Control中的“ Commit ”选项卡，您将看到所有数据库对象的列表，准备提交给源代码管理。确保选中所有对象，键入提交消息并单击commit：
当SQL Source Control表示所有更改都已提交时，请再次查看您的git仓库。您应该看到所有数据库对象都编写到各种目录中。
由于Git是一个分布式源代码控制系统，因此您只需将这些更改提交到本地仓库。您尚未将这些文件推送到Azure DevOps中托管的远程仓库。为此，请再次打开PowerShell终端，导航到git repo的根目录并运行命令：git push。
您可能会在SQL源代码管理提交选项卡中看到“ 推送 ”按钮，但它可能无法正常工作。这是Azure DevOps中托管的Redgate SQL Source Control和Git repos的已知问题。虽然有点令人沮丧，但打开PowerShell终端并输入“git push”并不是很麻烦，而且SQL Toolbelt中的其他功能可以弥补它。也就是说，如果破碎的“推送”按钮让你烦恼，你可以在这里添加你的投票。
将源代码推送到Azure DevOps之后，您应该能够在Azure DevOps网站的“ 代码 ”选项卡下看到它：
步骤2.1：使用SCA和PowerShell编写数据库构建脚本（可选步骤）
我们现在在源代码控制中有我们的数据库，所以下一步是设置一个构建过程来检查我们的源代码是否“编译”。通过这个，我的意思是它是可部署的。例如，如果源文件中存在一些非法的T-SQL语法，'或者如果某些视图缺少依赖项，可能是因为我重构了一些表，但忘记更新我的视图，那么SQL Server将无法执行我的源代码，我的数据库构建将失败。
此步骤是可选的，或者至少使用PowerShell在本地运行它是可选的。另一种方法是“跳过此步骤直接跳到步骤3，开始在Azure DevOps上配置数据库构建。但是，我喜欢先在本地运行我的构建。它帮助我了解幕后发生的事情。它还帮助我了解我的第一个构建是否由于我的源代码或我的Azure DevOps配置而失败。
在源代码管理的根目录中，在“ state ”目录旁边创建一个名为“ build ” 的新目录。这解释了为什么我没有在步骤2中将我的数据库链接到我的Git仓库的根目录。将其他相关文件放在源代码管理中是有用的，但是你不想将它们放在你的Redgate文件夹中。
使用localdb验证构建
在构建目录中创建一个名为“ build.ps1 ” 的新文件，并将以下PowerShell代码复制到其中。您可能希望更改参数的默认值$packageID以反映数据库的名称。现在，您应该保留其他参数。我将很快解释$packageVersion,$packagePath和$testPath参数，我将在进一步扩展下解释其他。
PARAM
（
[ string ] $ packageVersion = '0.1' ，
[ string ] $ packageID = 'StackOverflow' ，
[ string ] $ packagePath = 'C：\ packages' ，
[ string ] $ testPath = 'C：\ testResults' ，
[ string ] $ targetServerInstance = 'TARGETSERVERINSTANCE' ，
[ string ] $ targetDatabase = 'TARGETDATABASE'
）
$ errorActionPreference = “停止”
导入模块 SqlChangeAutomation - ErrorAction silentlycontinue - ErrorVariable + ImportErrors
“*****参数*****
packageVersion 是 $ packageVersion
packageID 是 $ packageID
packagePath 是 $ packagePath
testPath 是 $ testPath
* * * * * * * * * * * * * * * * * * * * * * “|写入输出
＃在父目录中搜索状态文件夹
$ myDir = Split-Path - Parent $ MyInvocation 。我的命令。路径
$ scriptsFolder = Join-Path - Path $ myDir - ChildPath '.. \ state'
$ scriptsFolder
如果 （-not （测试的路径 - PathType 集装箱 $ scriptsFolder ））
{
写错误 “ 无法找到$ scriptsFolder ”
}
#Using Redgate SCA验证状态目录中的代码
尝试
{
$ validatedScriptsFolder = Invoke-DatabaseBuild $ scriptsFolder ＃-SQLCompareOptions'NoTransactions'
}
赶上 ＃
{
$ _ 。例外。信息
“ $（$数据库。名称; ）无法验证，因为$（$ _ 。异常。消息）” | Foreach {
写错误 $ _
}
}
<＃
＃导出NuGet包
$ databasePackage = New-DatabaseBuildArtifact $ validatedScriptsFolder -PackageId $ packageID -PackageVersion $ packageVersion
Export-DatabaseBuildArtifact $ databasePackage -Path $ packagePath
＃运行测试
$ testResultsFile =“$ testPath \ $ packageID.junit。$ packageVersion.xml”
$ results = Invoke-DatabaseTests $ databasePackage
Export-DatabaseTestResults $ results -OutputFile $ testResultsFile
＃同步测试数据库
$ targetDB = New-DatabaseConnection -ServerInstance $ targetServerInstance -Database $ targetDatabase
Test-DatabaseConnection $ targetDB
Sync-DatabaseSchema -Source $ databasePackage -Target $ targetDB
＃>
此PowerShell代码使用SQL Change Automation在其中创建新数据库localDB并将所有源代码部署到该数据库。一旦部署了数据库，SQL Change Automation就会立即将其删除，因为它已经达到了目的。如果您每周运行10次构建一周，则不希望有50个测试数据库。
您应该能够从PowerShell运行脚本，如果您愿意，可以覆盖默认参数。
CD 的 根目录 中 的 源 控制\ 构建
。\ build 。ps1 - packageVersion'0.1 ' - packageID'MyDatabase '
这是完整的命令窗口，来自我的例子：
如果您的构建脚本有效，那太好了！如果没有，你可能会发现一些破碎的代码，也很棒！通常，这些是代码损坏，缺少依赖性，localDB未安装或SQL Server凭据不正确的结果。PowerShell输出是你的朋友。
使用临时数据库验证构建
您可能会遇到无法部署数据库的情况localDB，例如，它缺少对某些其他数据库的依赖关系，或者使用localDB不支持的SQL Server功能。例如，AdventureWorks使用不会部署到的全文搜索localDB。相反，您可以在“temproary”SQL Serve实例上部署到数据库，该实例设置了数据库所需的所有功能，依赖项，文件组等。（请参阅Invoke-DatabaseBuild cmdlet文档中的示例3和4。）
您可以下载一个PowerShell构建脚本（DBBuildTempServer.ps1），以稍微不同的样式编写，以展示可能的方法，以了解它是如何工作的。
您应该能够从PowerShell运行脚本，如果您愿意，可以覆盖默认参数。
CD 的 根目录 中 的 源 控制\ 构建
。\ build 。ps1 - packageVersion'0.1 ' - packageID'MyDatabase ' - TempServer'MyServerInstance ' - User_id '' - 密码 ''
如果您没有可用于此过程的SQL Server实例，只需-TemporaryDatabaseServer $TempconnectionString从SCA Invoke-DatabaseBuild命令中删除它，它将使用localDB，如前所述。Phil Factor的文章还提供了许多有关使用PowerShell和SCA的额外信息。
创建NuGet包
在这个阶段，我们所做的就是验证构建; 换句话说，证明了数据库将成功构建。完成后，我们可以开始探索其他SCA cmdlet，它们将从经过验证的构建生成并导出数据库构建工件，在其上运行我们的测试套件，然后部署测试的更改，以使目标数据库与经过验证和测试的同步资源。
我将在稍后的“ 扩展”部分中介绍测试和同步cmdlet ，但是现在您可能希望生成一个NuGet包。NuGet包可以用作构建工件，代表了基于包的部署和持续交付的重要里程碑。您只需在构建脚本中取消注释几行代码即可生成NuGet包。
只需在底部注释块中的#Export NuGet包下立即取消注释两行（例如，将<＃几行移动到下面），并确保默认值为$packagePath不在git仓库中的现有目录。如果你把它留空，它将在你的git repo的build目录中创建一个NuGet包，我不推荐。
现在，当您运行成功的构建时，将创建包含源代码的NuGet包。请注意，如果您尝试创建两个具有相同$packageID和$packageVersion相同$packagePath的包，则构建将失败，因此您需要覆盖构建号或每次重新运行构建时删除旧包。
提交对源代码管理的更改并将其推送到服务器。
> git add build。PS1
> git commit - m “添加构建脚本”
> git 推
第3步：使用Azure DevOps自动构建
您现在拥有源代码管理中的数据库代码，并将其推送到Azure DevOps。如果您按照步骤2.1中的说明进行操作，那么您还可以在源代码管理中使用PowerShell脚本来构建数据库，并且您知道源代码是“编译”的。现在，我们希望每次将新更改推送到源代码控制时，Azure DevOps都会构建数据库，以验证是否可以部署更改并捕获任何错误。
我们将使用Redgate Azure DevOps扩展来自动化数据库构建，因为这不需要您遵循步骤2.1。如果您确实按照步骤2.1而不是使用Redgate插件，则可能更喜欢使用执行build.ps1文件的原始PowerShell任务来运行构建脚本，并根据需要覆盖默认参数。如果采用该方法，您可能会发现Azure DevOps预定义变量的索引很有用，您可能希望使用$（Build.BuildNumber）或$（Build.BuildID）来创建具有顺序版本号的包。
您的自动构建将由“构建代理”执行。如果您已经localDB可以单独构建数据库，则应该能够立即使用托管构建代理。否则，您需要在托管的服务器上配置本地代理，该服务器可以访问合适的目标SQL Server实例以运行构建。
在Azure DevOps中，将鼠标悬停在“ 管道”选项卡上，然后从下拉列表中选择“ 构建 ”。
点击New Pipeline按钮，确保选择了正确的源控制存储库和主分支。然后为模板选择“ 空作业 ”。
将为您创建一个新的管道，其默认名称（在我的情况下为StackOverflow-CI）和默认的代理队列（在我的情况下，托管VS2017，这是Azure DevOps的默认名称。管道包含一个名为AgentJob1的默认作业，虽然如果你选择它，你可以重命名它。让我们称之为Build Stack Overflow Database。
我们可以将任务添加到将运行MSBuild或执行PowerShell脚本等的代理作业，以编译和测试我们的代码。为此，请单击代理作业旁边的+按钮。
Redgate SQL Change Automation Azure DevOps扩展默认不可用，但如果您在添加任务下的Marketplace选项卡中搜索“Redgate”，您将找到它。按照说明添加SQL Change Automation：Build扩展。
安装完成后，找到返回构建过程的方法。您可能需要再次点击上面的第2-5点，因为您的新定义可能尚未保存。这一次，如果您搜索“Redgate”，您会发现该扩展程序无需再次通过Marketplace即可使用，您只需单击“ 添加”即可。
将在Build StackOverflow数据库下创建构建任务，并警告某些设置需要注意。单击此构建任务，然后在“ 操作”下，选择“ 构建SQL源代码管理”项目。然后，在Database文件夹下选择“ Database scripts文件夹是VCS根目录的子文件夹 ”，在Subfolder Path下使用向导选择由SQL Source Control维护的状态目录（不要选择Git repo的根目录） ，如果您按照步骤2.1，还将包含您的构建脚本！）。
在Output NuGet Package ID下，为您的构建工件写一个名称。通常，这将是您的数据库的名称。
单击“ 保存并排队”以手动触发构建。默认设置应该没问题。您应该看到一个黄色通知，表明构建已排队。如果单击该号码，则会转到实时构建日志。
将为您分配托管代理。然后，它将下载SQL Change Automation并localDB在云中自行构建源代码并报告结果。
请注意，如果您的数据库不构建在Azure中托管构建代理上的本地数据库的默认安装上，则构建将失败。如果您的数据库不能构建localDB，则需要提供一个单独的SQL Server实例来运行构建。然后，在Build StackOverflow Database构建任务中，将Temporary服务器类型从SQL LocalDB（推荐）更改为SQL Server，并向服务器实例提供连接详细信息。如果要在本地托管此服务器实例，则可能需要安装可以访问本地服务器的本地构建代理来运行构建。
构建可能需要几分钟。就我而言，使用Stack Overflow和托管构建代理程序，花了大约两分半钟。使用更大，更复杂的模式，您可能需要更长时间。
如果构建失败，请查找日志中的错误并进行故障排除。常见问题包括缺少依赖项，不正确的凭据，对数据库的权限不足，使用不受支持的功能localDB以及损坏的T-SQL代码。保持堵塞，直到你的构建变绿。如果您跳过步骤2.1并且在构建时遇到问题，请考虑返回并尝试步骤2.1，因为这是解决源代码问题的一种更简单的方法，可以帮助您确定问题是否与源代码或Azure DevOps配置。
一旦构建为绿色，您需要设置构建触发器以确保每次将新代码推送到Azure DevOps服务器时构建都会运行。从Azure DevOps中的任何页面，将鼠标悬停在顶部的“ 管道”选项卡上，然后选择“ 生成”以导航回您的构建定义。选择Build Definition，然后选择Edit按钮。
选择触发器并确保选中启用持续集成，您就完成了。
如果您已经做到这一点，并且将新代码推送到Azure DevOps，那么它将自动启动构建并验证您的代码，并且您可以继续持续集成并快速获得有关错误的反馈。
进一步扩展
自动化数据库构建只是第一步。所有这些都将测试您的源代码是否可部署。它不会测试您的存储过程是否按设计运行。它不会自动更新集成数据库，因此它始终与源控件保持同步。它不会将您的代码部署到测试，登台和生产服务器。
首先，最重要的是，您需要开始编写将在每次构建时自动运行的数据库的测试。查看tSQLt和Redgate SQL Test。
如果使用写在PowerShell脚本步骤2.1，并且已安装数据库的tSQLt框架，并致力于其源代码管理，您可以通过下取消注释行你构建的一部分运行测试＃运行测试。如果您还没有将tSQLt框架提交给源代码控制，那么这将失败。您还应确保将$ testPath缺省参数设置为git repo之外的现有目录。与包一样，每次运行构建时都需要覆盖$ packageVersion。
如果您希望构建将更改部署到集成数据库，请取消注释#Sync a test database下的行并适当地设置$ targetServerInstance和$ targetDatabase 的默认值。
如果您在Azure DevOps中使用Redgate SQL Change Automation扩展，则需要：
- 添加SQL Change Automation：Build任务，操作设置为“使用tSQLt测试从SQL Source Control项目测试构建工件”。同样，除非您已经在源代码管理中使用了tSQLt框架和测试，否则这将无法工作。
- 添加SQL Change Automation：Build任务，操作设置为“将构建工件从SQL源代码管理项目同步到目标数据库”。