可以在多个分片上运行脚本的VSTS构建任务 [英] VSTS Build Task that runs script on multiple shards

问题描述

我们使用Azure SQL数据库，并使用弹性库具有分片"结构.我们正在开始向Visual Studio Team Services过渡，并希望使用其构建和发布过程来部署到开发和生产环境.

We use Azure SQL Databases, and have a "shard" structure using the Elastic Library. We are starting the transition over to Visual Studio Team Services and want to use their Build and Release process to deploy to development and production environments.

是否有一个构建任务，它将在我们所有的分片上执行一个SQL脚本(确保它成功运行并符合某些重试条件).这些脚本将是幂等的.

Is there a build task that will execute a SQL script on all our shards (ensuring that it runs successfully and follows some retry conditions). The scripts will be idempotent.

如果我们还有一个可以从我们的EF实体模型生成SQL脚本的任务，则可以加分.如果没有，我的计划是找到一种方法来创建脚本，并将其作为构建过程的一部分，然后由发行版执行.

Bonus points if we can also have a task that will generate the SQL script from our EF entity models. If not, my plan was to find a way to create the script as part of the build process and have the release execute it.

推荐答案

我可以通过构建/发布过程帮助您从EF模型生成SQL脚本.要满足您的需求，请执行以下操作:

I can help you with generating SQL script from your EF models withing build/release process. To achive that you need:

1. 将EF升级到6.2版或更高版本.

1. Upgrade your EF to 6.2 version or higher.

从"/packages/EntityFramework.6.2.0/tools/"获取"migrate.exe"，并将其放入您的项目中(不要忘记将属性更改为始终复制"，这样它将与其他DLL复制在一起在构建过程中)

Get "migrate.exe" from "/packages/EntityFramework.6.2.0/tools/" and put to your project (do not forget change properties to "copy always" so it will be copied with other DLLs during build process)

创建将运行migrate.exe的PowerShell脚本，并将SQL scripr应用于目标数据库.您的脚本可能如下所示

Create PowerShell script that will run migrate.exe and apply SQL scripr on your target database. Your script may look like the following

:

param 
(
    [string] [Parameter(Mandatory=$true)] $dbserver,
    [string] [Parameter(Mandatory=$true)] $dbname,
    [string] [Parameter(Mandatory=$true)] $dbserverlogin,
    [string] [Parameter(Mandatory=$true)] $dbserverpassword,
    [string] [Parameter(Mandatory=$true)] $rootPath,
    [string] [Parameter(Mandatory=$true)] $buildAliasName
)

& "$rootPath\migrate.exe" Context.dll /connectionProviderName="System.Data.SqlClient" /connectionString="Server=tcp:$dbserver.database.windows.net,1433;Initial Catalog=$dbname;Persist Security Info=False;User ID=$dbserverlogin;Password=$dbserverpassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" /startUpDirectory=$rootPath /verbose /scriptFile=1.SQL
Write-Host "Running migration script..."
& "SQLCMD" -S "$dbserver.database.windows.net" -U $dbserverlogin@$dbserver -P $dbserverpassword -d $dbname  -i 1.SQL

其中Context.dll是EF数据上下文的程序集.脚本将运行migrate.exe，这将生成输出脚本到1.SQL文件.之后，它将使用SQLCMD.exe运行此脚本.请注意，默认情况下，使用VSTS中的HOSTED代理可以使用SQLCMD.exe.如果您使用的是"HOSTED 2017"，则需要指定SQLCMD.exe的完整路径(在编写此路径时为c:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE)

Where Context.dll is an assembly with your EF Data Context. Script will run migrate.exe which will generate output script to 1.SQL file. After that it will run this script using SQLCMD.exe. Please note that by default SQLCMD.exe is available using HOSTED agent in VSTS. If you are using "HOSTED 2017" than you need to specify full path to SQLCMD.exe (on the time of writing this path is c:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE)

这篇关于可以在多个分片上运行脚本的VSTS构建任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！