当您作为应用程序开发团队的成员时，值得建立一个例程，该例程从源代码控制开始进行日常构建，然后供应数据库的测试和开发实例。一个主要的优点是可以尽早检查代码和数据库，并且您可以证明可以完全从代码构建工作数据库。这意味着管理层可以确信资产全部在源代码控制中。这也意味着测试可以尽早发现问题。
这需要自动化。最困难的部分是按照依赖关系的顺序组装单个对象脚本，或者在迁移方法的情况下，按照清单指示的顺序组装单个对象脚本。最繁琐的部分是将数据加载到数据库中。当然，耗时的部分是准备数据，但这超出了本文的范围。我们假设您有一个用于测试的标准数据集，并且所有服务器都将共享它。
我使用SQL Change Automation（SCA）来构建数据库。微软的SMO扮演次要的整合角色，我也使用它来创建数据库。我使用SQL Clone（SQL Provisioning的一个组件）将完成的数据库分发到需要最新版本的各种基于团队的服务器。将流程调整为符合您的要求后，您可以在每次有人提交数据库更改时运行该流程。
SQL克隆简介
第一次处理SQL Clone时，总是会有轻微的文化冲击，因此，我将作简要说明（有关更多信息，请参见Clone的工作原理）。SQL Clone使用x64 Windows中的虚拟磁盘服务来允许将相同的字节（“数据映像”）作为“克隆”数据库重复使用，并根据需要多次在SQL Server实例上使用。SQL Server完全不知道Windows操作系统正在从事任何“烟雾和镜像”活动。数据映像仅在网络上的单个位置保存一次，并且永不更改。每个克隆上进行的任何更改都存储在本地克隆服务器上的差异磁盘中。
设置克隆所需的时间很少，这是设置虚拟硬盘（.vhdx）并将其装入所需的时间。无论实际数据库的大小如何，从该映像创建的初始克隆仅需要大约40MB的磁盘空间。
您将需要一个中央服务器，即SQL Clone服务器，在其上安装SQL Clone Web应用程序，该应用程序必须在网络上对所有其他计算机可见。您的服务器不必是Windows域的一部分即可运行SQL Clone，但是如果您在混合开发环境中运行，最简单的方法是在与Clone相同的计算机上为映像提供共享目录。服务器。克隆可以在任何SQL Server上运行，在该SQL Server上，分配给克隆代理的用户可以通过网络访问该共享映像目录。
网络中任何SQL Server实例都可以用来承载克隆。为此，他们需要在其上安装SQL Clone代理，这些代理被分配了具有sysadmin权限的本地用户。
一旦数据库开始需要大量数据来进行开发和测试工作，那么用当前版本的数据库和数据来配置所有必需的数据库实例的麻烦就会变得一发不可收拾。如果可以确定克隆具有正确的数据，则可以通过模式同步来实现，但这是不现实的。任何测试运行都必然会更改数据。要进行一系列集成测试，您需要在每次测试后将数据库还原到已知状态。传统上，这是通过备份/还原或重复分离和附加来完成的。
取而代之的是，我将使用SQL Clone，这使得拆卸和恢复克隆非常迅速，容易，这是拆卸的一部分，并且可以在PowerShell的一行中轻松地自动完成此过程。
PowerShell自动化的CI
接下来我将介绍的PowerShell脚本将允许您使用源控制程序的最新版本的架构以及一个或多个测试或开发的数据版本来维护测试和开发所需的各种数据库。我提供了一个AdventureBuild zip文件，其中包含AdventureWorks数据库的源以及开发和测试所需的数据（作为bcp文件）。如果您从包含敏感数据或个人数据的数据集开始，则在配置开发和测试服务器之前，需要屏蔽数据。
我假设这种情况下的原始数据库是正在开发的数据库，并且其源保存在“源代码管理”中，因此我不备份现有克隆，也不在删除克隆之前保存更改，并且不检查是否任何人仍然都有与克隆一起运行的会话。
该脚本通过同步到使用SMO创建的空数据库来使用SCA进行构建（但是您也可以使用SQL Compare）。您需要在运行SQL Server版本（等于或低于您需要将克隆部署到的最旧版本）的实例上构建数据库。无法将克隆运行在比其父代更早的SQL Server版本上。
我们使用BCP将数据导入到空版本中，然后使用SQL Clone创建数据库映像并将克隆部署到一台或多台服务器上的“克隆”数据库列表中。将使用创建过程创建的数据库副本来创建克隆或刷新现有克隆。最后应该是所有具有相同元数据和数据的数据库。您需要安装最新版本的Clone PowerShell cmdlet（Clone应用程序中的Settings | PowerShell）。
如果您有SCA但没有SQL Clone，则仍然可以执行此操作，但是它会慢得多，并且会占用更多空间。
该解决方案省略了一些更详细的细节，您可以在其他文章中找到大多数细节。例如，我不会为数据库生成文档网站，也不会使用SQL Code Guard 对代码进行检查，也不会发送有关构建进度的信息。我没有显示如何将脚本和模板应用于每个单独的克隆以自定义安装或使数据库符合服务器的设置。
配置文件
为简单起见，我将所有设置放在单独的脚本中。其中包含要创建或更新的克隆列表，必须构建的数据库的详细信息，要在其中找到数据和源代码的位置以及克隆服务器和共享映像目录的位置。显然，您需要全部填写。
$Database = 'OurBuild'
$Repository= 'OurPathToTheScriptAndData'
@{
"Source" = @{
#specify the various directories you want in order to store files and logs
#The location of the executable SQL data insertion script.
'DataSyncPath' = "$Repository\$Database\data";
#where you want to put the reports for this particular database.
'ReportPath' = "$Repository\$Database";
#where you have (or want to put) the source of the current database.
'DatabasePath' = "$Repository\$Database\Build";
}
"Build" = @{
#now we'll specify where we want the new build. We will clone from this.
'NewBuildServer' = 'OurServer'; #The Server
'NewBuildInstance' = ''; #The SQL Server instance
'NewDatabase' = "New$Database"; #The name of the database
'username' = 'Phil Factor';#leave blank if windows authentication
'SQLCompareOptions' ='NoTransactions'# you sometimes need these for a complex build
}
"Image" = @{
#create an image of what we built
'Name' = "$($database)";
'ServerURL' = 'http://CloneServer:14145';
'ImageDirectoryURL'='\\CloneServer\Clone'
}
"Clones" = @(
@{ "NetName" = "AServer"; "Database" = "$($database)1" },
@{ "NetName" = "AnotherServer"; "Database" = "$($database)2" },
@{ "NetName" = "YetAnotherServer"; "Database" = "$($database)3" },
@{ "NetName" = "StillAnotherServer"; "Database" = "$($database)4" },
@{ "NetName" = "AndYetAnotherServer"; "Database" = "$($database)5" },
@{ "NetName" = "YeGodsAnotherServer"; "Database" = "$($database)6" }
)
}
该目录必须与以下PowerShell脚本位于同一目录中。
CI脚本
这是用于构建和填充数据库以及部署克隆的PowerShell脚本：
$VerbosePreference = "Continue"
#-- just to save typing ----
$MS = 'Microsoft.SQLServer'
$My = "$MS.Management.Smo"
$Mc = "$MS.Management.Common"
$popVerbosity = $VerbosePreference
$VerbosePreference = "Silentlycontinue"
# the import process is very noisy if you are in verbose mode
Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality
$VerbosePreference = $popVerbosity
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop"
<# just to make it easier to understand, the various parameter values are structured in a
hierarechy. We iterate over the clones when making or updating them #>
#First we read in the configuration from a file (do it so we can use the ISE as well)
$Data = &"$(If($psISE)
{Split-Path -Path $psISE.CurrentFile.FullPath}
Else {$global:PSScriptRoot})\MySCACloneConfig.ps1"
<# we read in the data as a structure.
Then we do some sanity checking to make sure that the data is reasonably viable.
We apply defaults if possible #>
$Errors = @()
# the fourth value means -1 provide a blank default, 0 = not a directory-must be there,
# 1=create if not exist, 2 = must already exist
@(($data.source.DatabasePath, 'source', 'DatabasePath', 2),
($data.source.DataSyncpath, 'source', 'DataSyncPath', 2),
($data.source.ReportPath, 'source', 'ReportPath', 2),
($data.Build.NewBuildServer, 'Build', 'NewBuildServer', 0),
($data.Build.NewBuildInstance, 'Build', 'NewBuildInstance', 0),
($data.Build.NewDatabase, 'Build', 'NewDatabase', 0),
($data.Build.username, 'Build', 'username', -1),
($data.Build.SQLCompareOptions, 'build', 'SQLCompareOptions', -1)
($data.Image.Name, 'Image', 'Name', 0),
($data.Image.ImageDirectoryURL, 'Image', 'ImageDirectoryURL', 0),
($data.Image.ServerURL, 'Image', 'ServerURL', 0)
) | foreach{
if ($_[0] -eq $null) #if the parameter has'nt been provided
{# we give a default '' else flag up an error
if ($_[3] -eq -1) { $data.$_[1].$_[2] = '' }
else
{ $Errors += "There is no $($_[1]).$($_[2]) defined" }
}
elseif ($_[3] -ge 1) #it is a directory that needs to be tested
{
if (-not (Test-Path -PathType Container $_[0]))
{
if ($_[3] -eq 2)
{
New-Item -ItemType Directory -Force -Path $_[0] `
-ErrorAction silentlycontinue -ErrorVariable +Errors;
}
else { $Errors += "the path '$($_[0])'in $($_[1]).$($_[2]) does not exist" }
}
}
}
$TheLogfile="$($data.source.ReportPath)\ReportFile.txt"
if ($data.build.NewBuildInstance -eq '')
{$NewBuildServerInstance= "$($data.build.NewBuildServer)"}
else
{$NewBuildServerInstance= "$($data.build.NewBuildServer)\$($data.build.NewBuildInstance)"}
if ($NewBuildServerInstance -eq '') {$errors += 'No build server specified'}
if ($errors.Count -eq 0) #any errors are displayed at the end
{
#first make sure we can connect
$conn = new-object "$Mc.ServerConnection" $NewBuildServerInstance `
-ErrorAction silentlycontinue -ErrorVariable +Errors;
if ($data.build.username -ieq '')
{
# Crikey, this is easy, windows Passwords. Dont you love 'em?
$conn.LoginSecure = $true;
}
else
{
<# This is more elaborate a process than you might expect because we can't assume
that we can use Windows authentication, because of Azure, remote servers outside
the domain, and other such complications. We can't ever keep passwords for SQL
Server authentication as part of the static script data. At this stage, we ask
for passwords if they aren't known, and otherwise store them as secure strings
on file in the user area, protected by the workstation security.
#>
#create a connection object to manage credentials
$conn = new-object "$Mc.ServerConnection"
$conn.ServerInstance = $NewBuildServerInstance
$encryptedPasswordFile = `
"$env:USERPROFILE\$($data.build.Username)-$($conn.ServerInstance).txt"
# test to see if we know about the password in a secure string stored in the user area
if (Test-Path -path $encryptedPasswordFile -PathType leaf)
{
#has already got this set for this login so fetch it
$encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString
$Credentials = `
New-Object System.Management.Automation.PsCredential($data.build.Username, $encrypted)
}
else #then we have to ask the user for it
{
#hasn't got this set for this login
$Credentials = get-credential -Credential $data.build.username
$Credentials.Password | ConvertFrom-SecureString |
Set-Content $encryptedPasswordFile
}
$conn.LoginSecure = $false;
$conn.Login = $Credentials.UserName;
$conn.SecurePassword = $Credentials.Password;
}
$data.build.ServerConnection = $conn; #this sets our server connection for the build database
}
<# now that weve established all the connections we need we now build the database>
if ($errors.Count -eq 0)
{
#if the database already exists, then kill it
$TheBuildDatabase = $data.build.NewDatabase
$BuildServer = new-object ("$My.Server") $data.build.ServerConnection
if ($BuildServer.Databases[$TheBuildDatabase] -ne $null)
{ $BuildServer.KillDatabase($TheBuildDatabase) }
$BuildDatabase = `
New-Object ("$My.Database") ($BuildServer, $TheBuildDatabase)
$BuildDatabase.Create()
if ($BuildDatabase.name -ne $TheBuildDatabase)
{
$Errors += "Can't create the database '$($TheBuildDatabase)' in '$($data.build.ServerInstance)"
};
if ($data.build.ServerConnection.LoginSecure)
{
$data.build.Connection = New-DatabaseConnection `
-ServerInstance $NewBuildServerInstance `
-Database $TheBuildDatabase `
-ErrorAction silentlycontinue -ErrorVariable +Errors;
}
else
{
$data.build.Connection = New-DatabaseConnection `
-ServerInstance $NewBuildServerInstance `
-Database $TheBuildDatabase `
-Username $data.build.ServerConnection.Login `
-Password $data.build.ServerConnection.Password `
-ErrorAction silentlycontinue `
-ErrorVariable +Errors;
}
$syncResult = Sync-DatabaseSchema `
-Source $data.source.DatabasePath `
-Target $data.build.Connection `
-AbortOnWarningLevel None `
-SQLCompareOptions $data.build.SQLCompareOptions `
-ErrorAction silentlycontinue `
-ErrorVariable +Errors
}
if ($errors.Count -eq 0) # we can put the data in the database
{
<# Now we BCP all the table data in. As we are using native mode the utility
disables constraints for the table before doing the import #>
$BuildDatabase.Tables | #for every table
foreach {
$filename = "$($_.schema)_$($_.Name)" -replace '[\\\/\:\.]', '-';
$TheScriptPath = "$($data.source. DataSyncPath)\$($filename).bcp";
if (-not ($data.build.ServerConnection.LoginSecure))
{
$whatHappened = "`"$TheBuildDatabase`".`"$($_.Schema)`".`"$($_.Name)`""
$WhatHappened +=
BCP "`"$TheBuildDatabase`".`"$($_.Schema)`".`"$($_.Name)`"" `
in "`"$TheScriptPath`"" -q -n -N -E "-U$($data.build.ServerConnection.Login)" `
"-P$($data.build.ServerConnection.Password)" "-S$($data.build.ServerConnection.ServerInstance)";
}
else
{
$WhatHappened =
BCP "`"$TheBuildDatabase`".`"$($_.Schema)`".`"$($_.Name)`"" `
in "`"$TheScriptPath`"" -q -N -T -E "-S$($data.build.ServerConnection.ServerInstance)";
}
if ($WhatHappened -like '*Error *')
{ throw ("$whatHappened adding data to $TheBuildDatabase.$filename on $TheScriptPath") };
}
$result = $BuildServer.ConnectionContext.ExecuteNonQuery(
"EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'");
"$($data.build.ServerConnection.ServerInstance) $TheBuildDatabase has been stocked with data"
}
<# we get through to clone>
if ($Errors.Count -eq 0)
{
#initialise SQL Clone by attempting to initate a connection with a SQL Clone Server.
Connect-SqlClone -ServerUrl $data.image.ServerURL -ErrorAction SilentlyContinue `
-ErrorVariable +Errors
}
<# now we slaughter all existing versions of the database. its so great that youve put everything worthwhile in source control and have sql prompt tab magic as well.>
if ($Errors.Count -eq 0)
{
$image = Get-SqlCloneImage `
-Name $data.image.Name -ErrorAction SilentlyContinue
if ($image -ne $null) #if it already exists
{
$clones = Get-SqlClone `
-Image $image -ErrorAction SilentlyContinue
if (!($clones -eq $null)) #delete all existing clones (normally, you'd save changes
{
$clones | Remove-SqlClone | Wait-SqlCloneOperation
}
Remove-SqlCloneImage -Image $image | Wait-SqlCloneOperation
}
}
if ($Errors.Count -eq 0)
{
Write-Verbose " Creating new image of $NewBuildServerInstance.$($data.build.NewDatabase)"
#create an image of what we built. We name it whatever we have specified
$SqlServerInstance = [RedGate.SqlClone.Client.Api.Objects.SqlServerInstanceResource](Get-SqlCloneSqlServerInstance |
Where-Object{ ($_.Serveraddress -ieq $data.build.NewBuildServer.Trim()) -and ($_.Instance.Trim() -ieq $data.build.NewBuildInstance.Trim())})
$ImageFileDestination = Get-SqlCloneImageLocation `
-Path $data.Image.ImageDirectoryURL
New-SqlCloneImage -Name $data.image.Name `
-SqlServerInstance $SqlServerInstance `
-DatabaseName $data.build.NewDatabase `
-Destination $ImageFileDestination `
-ErrorAction silentlycontinue `
-ErrorVariable +Errors | Wait-SqlCloneOperation
$ourCloneImage = Get-SqlCloneImage `
-Name $data.image.Name `
-ErrorAction SilentlyContinue
if ($ourCloneImage -eq $null)
{
$Errors += "couldn't find the clone $($data.image.Name) That has just been created"
}
if ($ourCloneImage.State -ne 'Created')
{$Errors += "We hit a problem with the image. It's state is $($ourCloneImage.State)"}
}
#clone it as whatever database is specified to whatever SQL Clone servers are specified
$data.clones | foreach {"$($_.Netname,$_.Database ) is ok"}
if ($Errors.Count -eq 0)
{
$data.clones | foreach {
$clone = $null; $Thedatabase = $_.Database;
#get the correct instance that has an agent installed on it.
$sqlServerInstance = (Get-SqlCloneSqlServerInstance | Where server -ieq $_.NetName);
if ($sqlServerInstance -eq $null) { Throw "Unable to find the clone agent for $($_.NetName)" }
write-verbose "Cloning $($_.Database) on $($_.NetName)"
$clone = Get-SqlClone `
-ErrorAction silentlyContinue `
-Name "$($TheDatabase)" `
-Location $sqlServerInstance
if (($clone) -ne $null)
{
write-warning "Removing Clone $Thedatabase that already existed on $($_.NetName)"
Remove-SqlClone $clone | Wait-SqlCloneOperation
}
Get-SqlCloneImage -Name $data.Image.Name |
New-SqlClone -Name "$($Thedatabase)" -Location $SqlServerInstance |
Wait-SqlCloneOperation
if ($errors.Count -gt 0)
{
break
}
}
}
if ($errors.Count -gt 0)
{
$errors| foreach{Write-error $_; "$((Get-Date).ToString()): $($_) the build process was aborted">>$TheLogFile;};
write-error("$($_)")
}
else
{
"$((Get-Date).ToString()): the build process had no errors">>$TheLogFile
}
结论
使用SQL Server数据库执行自动CI的方法有很多。考虑到丰富的团队方法，这也是一样。对我来说，SQL Clone的最大优势不仅在于自动化的简化和磁盘空间的节省，还在于抛弃正在测试的数据库并将其还原为构建版本的便捷性。数据库的大小越大，能够执行此操作的优势就越大。
我在这里显示的这个脚本是为了说明可能性。您可以添加许多内容，例如数据生成，脚本检查和数据屏蔽，但是SQL Clone的基本功能无疑为为更大的团队和数据库提供持续集成提供了很大的帮助。