In this post I’ll be having a quick look at the VSTS Sql Server Database Deployment Task. You can find it in the marketplace as part of the IIS Web App Deployment Using WinRM package. It is a bit confusing that you have to look for it under the IIS section not the SQL section in the marketplace.
Take note this task is using WinRm, the instructions to enable it is on the marketplace page and includes a link to a PowerShell script you can run to configure WinRM.
Lets take it for a spin. Follow the installation process from the marketplace to install the extension on your account or download it for on-prem TFS servers. You will see the new WinRM – SQL Server DB Deployment tasks in the Deploy tasks menu.
Add the task to your deployment and set a few properties, this was quick test I did. You will need a task before this one to copy the dacpac file to a location where the agent can reach it.
I didn’t have ports open on my firewall for the VSTS hosted agent to reach my machine so I installed the VSTS agent locally and set the task to use localhost. You can specify multiple machines or even use Machine Groups, you will find it in the Test hub in VSTS.
The output from the task doesn’t show much about the actual database changes taking place but you will at least know if the deployment failed.
Side Note: Don’t put plain text passwords in your build tasks, use variables and mark them as secret by clicking the padlock icon next to the variable value.
You can find some more information in the readme file for the task, which they include as a link in error messages when a step fails, which is really helpful.
If you are looking for more information around continuous deployment of databases have a look at these posts in my continuous deployment of databases series.
Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.
