ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户
ope体育电竞百度

使命召唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户

admin admin ⋅ 2019-11-18 16:24:33

当您作为应用程序开发团队的成员时,值得树立一个例程,该例程从源代码操控开端进行日常构建,然后供给数据库的测验和开发实例。一个首要的长处是能够尽早查看代码和数据库,而且您能够证明能够彻底从代码构建作业数据库。这意味着办理层能够坚信财物悉数在源代码操控中。这也意味着测验能够尽早发现问题。

这需求主动化。最困难的部分是依照依靠联系的次序拼装单个目标脚本,或许在搬迁办法的情况下,依照清单指示的次序拼装单个目标脚本。最繁琐的部分是将数据加载到数据库中。当然,耗时的部分是预备数据,但这超出了本文的规模。咱们假定您有一个用于测验的规范数据集,而且一切服务器都将同享它。

我运用SQL Change Automation(SCA)来构建数据库。微软的SMO扮演非有必要的整合人物,我也运用它来创立数景甜性感据库。我运用SQL Clone(SQL Provisioning的一个组件)将完结的数据库分发到需求最新版别的各种根据团队的服务器。将流程调整为契合您的要求后,您能够在每次有人提交数据库更改时运转该流程。

SQL克隆简介

第一次处理SQL Clone时,总是会有细微的文明冲击,因而,我将作扼要阐明(有关更多信息,请拜见Clone的作业原理)。SQL Clone运用x64 Windows中的虚拟磁盘服务来答应将相同的字节(“数据映像”)作为mhxx要害任务“克隆”数据库重复运用,并根据需求屡次在SQL Server实例上运用。SQL S任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户erver彻底不知道Windows操作系统正在从事任何“烟雾和镜像”活动。数据映像仅在网络上的单个方位保存一次,而且永不更改。每个克隆上进行的任何更改都存储在本地克隆服务器上的差异磁盘中。

设置克隆所需的时刻很少,这是设置虚拟硬盘(.vhdx)并将其装入所需的时刻。不管实践数据库的巨细怎么,从该映像创立的初始克隆仅需求大约40MB的磁盘空间。

您将需求一个中心服务器,即SQL Clone服务器,在其上装置SQL Clone Web应用程序,该应用程序有必要在网络上对一切其他核算机可见。您的服务器不必是Windows域的一部分即可运转SQL Clone,可是假如您在混合开发环境中运转,最简略的办法是在与Clone相同的核算机上为映像供给同享目录。服务器。克隆能够在任何SQL Server上运转,在该SQL Server上,分配给克隆署理的用户能够经过网络拜访该同享映像目录。

网络中任何SQL Server实例都能够用来承载克隆。为此,他们需求在其上装置SQL Clone署理,这些署理被分配了具有sysadmin权限的本地用户。

一旦数据库开端需求许多数据来进行开发和测验作业,那么用当时版别的数据库和数据来配备一切必需的数据库实例的费事就会变得一发不可收拾。假如能够确认克隆具有正确的任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户数据,则能够经过形式同步来完结,但这是任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户不现实的。任何测验运转都必然会更改数据。要进行一系列集成测验,您需求在每次测验后将数据库复原到已知状况。传统上,这是经过备份/复原或重复别离和附加来完结的。

取而代之的是,我将运用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' = "$Repos南宁陈林菠itory\$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" = @(

@{ "Ne淮海西路55号tName" = "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" = "$($databas着衣e)6" }

)

}

该目录有必要与以下PowerShell脚本坐落同一目录中。

CI脚本

这是用于构建和填充数据库以及布置克隆的PowerShell脚本:

$VerbosePreference = "Continu60grannye"

#-- 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

$con任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户n = 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

$en爱拍才哥crypted = 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

}任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户

<# now that we've established all the connections we need, we now build the database #>

if ($errors.Count -eq 0)

{

#if the database already exists, then kill it

$TheBuildData叶紫涵反串扮演视频base = $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 '$($TheBuildDxcs联赛atabase)' 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`".`任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户"$($_.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. It's so great that you've 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.N程川陆烟ewBuildInstance.Trim())})

$ImageFileDestination = Get-SqlCloneImageLocation `

-Path $data.Image.ImageDirectoryURL

New-SqlCloneImage -Name $data.image.Name `

-SqlServerInstance $SqlServerInstance `

-DatabaseName $data.build.NewDatabase `

-Destination $ImageFil任务呼唤,ppt图片-ope体育电竞百度_ope电竞竞猜官方网站_ope电竞门户eDestination `

-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-SqlClon自宅警备员eSqlServerInstance | 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.Co具结书是什么意思unt -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的基本功能无疑为为更大的团队和数据库供给继续集成供给了很大的协助。

相关新闻

admin

admin

TA太懒了...暂时没有任何简介

精彩新闻