Introduction
A lot of the projects I work on are not code-first projects. In most of the projects a database already exists and during deployment of changes also database scripts must be executed to ensure a working environment.
It is possible to build a database project into a DACPAC file and use this file to deploy new databases or changes in existing databases, but sometimes a plain and simple SQL script is easier to use and maintain.
The build and release management environment in Azure DevOps enables us to embed custom tasks into a build or release pipeline. At the time of writing of this post (in the first quarter of 2016) a task for executing SQL scripts was not available from the store, so I decided to give it a try and build a custom build / release management task that can execute a SQL script on a given database.
TFS Cross Platform Command Line Interface
The tooling that enables users to upload customs tasks (and more) is the TFS Cross Platform Command Line Interface (TFX). This is a github project and documentation can be found on https://github.com/Microsoft/tfs-cli.
To get access to your Azure DevOps environment with TFX you must create a personal access token or use basic authentication. Read the post Using Access Tokens to access Visual Studio Online to read how you can create an access token.
The command “tfx build task create” will create a custom task template that you can change to your needs. It will create a new folder containing a json file, a sample Node.JS script, a sample Powershell script and an icon.
When the template is changed according your needs you can install the template in the collection that you are logged on to (with the tfx logon command) with the command “tfx build tasks upload –task-path <path to your custom task definition>”.
Custom Tasks
A custom task definition exists of a task description (json file), one or more PowerShell scripts and optional custom DLL’s.
The task description file describes and identifies the task, contains versioning information, describes the necessary capabilities of the agent it can run on, describes input variables and the way the task is executed (PowerShell or Node.JS). The post Custom Build Tasks in TFS 2015 contains some basic information on writing custom build tasks.
Run a SQL script with Powershell
Running a SQL script from Powershell is not a difficult task. There basically are two options: use Invoke-Sqlcmd cmdlet or use sqlcmd.exe. Because I want to have the possibility to run the SQL script directly from the VSO agent, I prefer to use sqlcmd.exe, which can be installed a part of the Microsoft SQL Server Feature Pack.
The scriptblok needed for running sqlcmd is a follows:
$scriptBlock = {
param($serverName, $databaseName, $useSQLAuthentication, $userName, $password)
if ($useSQLAuthentication)
{
. sqlcmd -S $serverName -d $databaseName -U $userName -P $password
}
else
{
. sqlcmd -S $serverName -d $databaseName
}
}
Note that I have created the possibility to choose for SQL authentication (with a SQL Server username and password) or Windows authentication (using the account that the VSO agent is running on).
The complete parameter list for the Powershell script:
- runOnAgent: will sqlcmd.exe run on the agent or on a different server;
- computerName: in case of running on different server, the name of this server (exists in the network of the VSO agent);
- credentialUserName: username for logging on this server;
- credentialPassword: password for logging on this server;
- serverName: name of the database server (including SQL instance name);
- databaseName: name of the database;
- useSQLAuthentication: use SQL authentication (or Windows authentication);
- userName: username of SQL account;
- password: password of SQL account;
- scriptName: full pathname of the SQL script.
The json file contains definitions for these input parameters:
"inputs": [
{
"name": "runOnAgent",
"type": "boolean",
"label": "Run task script on agent",
"defaultValue": "false",
"required": true,
"helpMarkDown": "Run this task on the VSO agent (needs Microsoft SQL Server CLI)."
},
{
"name": "computerName",
"type": "string",
"label": "Remote computer",
"defaultValue": "",
"required": false,
"helpMarkDown": "Remote computer on which SQL script must run.",
"visibleRule": "runOnAgent = false"
},
{
"name": "credentialUserName",
"type": "string",
"label": "Username",
"defaultValue": "",
"required": false,
"helpMarkDown": "Username of user on remote computer.",
"visibleRule": "runOnAgent = false"
},
{
"name": "credentialPassword",
"type": "string",
"label": "Password",
"defaultValue": "",
"required": false,
"helpMarkDown": "Password of user on remote computer.",
"visibleRule": "runOnAgent = false"
},
{
"name": "serverName",
"type": "string",
"label": "Databaseserver name",
"defaultValue": "",
"required": true,
"helpMarkDown": "Name (incl. instance) of SQL database server."
},
{
"name": "databaseName",
"type": "string",
"label": "Database name",
"defaultValue": "",
"required": true,
"helpMarkDown": "Name of SQL database."
},
{
"name": "useSQLAuthentication",
"type": "boolean",
"label": "Use SQL Server Authentication",
"defaultValue": "true",
"required": true,
"helpMarkDown": "Use SQL Server Authentication on database server (otherwise Windows Authentication is used)."
},
{
"name": "userName",
"type": "string",
"label": "Database server username",
"defaultValue": "",
"required": false,
"helpMarkDown": "User name for SQL logon.",
"visibleRule": "useSQLAuthentication = true"
},
{
"name": "password",
"type": "string",
"label": "Database server password",
"defaultValue": "",
"required": false,
"helpMarkDown": "Password for SQL logon.",
"visibleRule": "useSQLAuthentication = true"
},
{
"name": "scriptName",
"type": "filePath",
"label": "SQL script file name",
"defaultValue": "",
"required": true,
"helpMarkDown": "File name of SQL script that must be run."
}
],
Note that “computerName”, “credentialUsername” and “credentialPassword” are visible (when defining the task in the TFS or Visual Studio Team Services web interface) when the value of “runOnAgent” is true. Note also that “userName” and “password” are only visible when SQL Authentication must be used.
The json file also contains information on how the task is executed:
"execution": {
"PowerShell": {
"target": "$(currentDirectory)\\RunSqlScript.ps1",
"argumentFormat": "",
"workingDirectory": "$(currentDirectory)"
}
}
The Powershell script starts with the parameter definitions:
param (
[string]$runOnAgent,
[string]$computerName,
[string]$credentialUserName,
[string]$credentialPassword,
[string]$serverName,
[string]$databaseName,
[string]$useSQLAuthentication,
[string]$userName,
[string]$password,
[string]$scriptName
)
Note that the parameters “runOnAgent” and “useSQLAuthentication” are both string parameters and that these variables are defined as boolean in the “inputs” section of the json file. This is strange behavior of the system that passes the variables to the powershell script; all parameters are passed as strings. The boolean parameters must be converted from string to boolean in the Powershell script.
Use in vNext build definition or release definition
The task that was uploaded to TFS or VSTS can be used in a build definition or a release definition. You can choose it when adding a task to the definition:
And the task definition will be shown as below:
The script file name is a file that is stored in the artifacts of the build or release definition and can be selected from the linked artifacts folder.
Great tooling, isn’t it?
The code for this task is on GitHub so you can try it yourself.
