Deploy a SQL DACPAC to Azure using VSTS
Recently I was tasked with deploying another WebAPI project to Azure. Deploying these WebAPI’s to Azure has become somewhat routine; build the solution, archive the artifacts, and define a release definition with a single Azure App Service Deploy task. Easy peezy.
This task was a little different though. This Solution had an accompanying SQL Database Project included. Not to worry, I have built and deployed SQL Database projects before, how hard could this be.
Build Definition
The Build Definition follows the same basic steps that you encounter most times you are building Visual Studio Solutions;
One difference was that I added a separate Publish Artifact step; one for each of the Projects contained in the Solution. In this instance, I have an Artifact for the Database project and one for the WebAPI Project. Each artifact was given a descriptive name so I can easily discern the two separate artifacts in the Release Definition.
Sql Database Project Targeting the Azure Platform
The other change I had to make, and the one that caught me the most off guard, had to do with the Target Platform of the Database Project. When I first ran the Release Job, I received the following error;
Publishing to database '[database_name]' on server '[Azure SQL Server]'.
Initializing deployment (Start)
Initializing deployment (Failed)
##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.
##[error]A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.
##[error]Azure SQL Dacpac task failed.##[section]Finishing: Execute Azure SQL : DacpacTask
In this instance, the developer had chosen SQL Server 2016.
In order to figure out what the correct value was supposed to be, I had to open the Solution in Visual Studio and choose the Microsoft Azure SQL Database V12 option, then save the Project.
Since the solution came from Source Control, I was able to just look at the changes on the local disk to figure out which value I needed to override, and what the correct value I needed to override it with.
Using this information, we just had to alter our Build Definition and override this single property.
Here is the MSBuild Argument for each copy\paste;
/property:DSP="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12SchemaProvider"Release Definition
For reference sake, this is what the Release Definition looks like;
