VSTS Build Task that runs script on multiple shard

Question

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.

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.

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.

Answer 1

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

1. Upgrade your EF to 6.2 version or higher.

2. 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)

3. 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

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)