Part 2. Automate Building Azure SQL Database with Continuous Integration

This blog is part 2 of Automate Build and Deployment of Azure SQL Database with Continuous Integration and Continuous Deployment.

Content of Tutorial:

Part 1. Prerequisite for Azure SQL Database Build and Deployment Automation
Part 2. Automate Building Azure SQL Database with Continuous Integration (this page)
Part 3. Create Nuget package for Azure SQL Database
Part 4. Orchestrate Azure SQL Database Release with Continuous Deployment
Part 5. Use your own Build and Deployment Agent

In part 2, we will play through

  • How to configure a build definition for your database project
  • How to enable continuous integration that triggers a new build when source code changes.

 


 

Let’s start by creating a new build definition.

Click Team Explorer >> Builds in Visual Studio.

Add New Build Definition under Build Definitions section.

te-build2

It will open Create new build definition dialog on your team project web portal.

Select Empty and click Next.

build definition dialog

On Create new build definition page, you can simply use default settings. We will look into more in detail about Agent Queue in part 5. Just use Hosted for now.

Click Create button.

create new build definition

After the build definition is created, click Add build step… on the build definition page.

Add two tasks; MSBuild and Copy & Publish Build Artifact.

msbuild add copy and publish add

Frist, configure MSBuild task with the following properties. Other settings are optional values.

  • Project: browse and select *.sln or *.sqlproj file for your database project
  • MSBuild Arguments: insert following argument

/t:build /p:CmdLineInMemoryStorage=True

build task config

Next, configure Copy and Publish Build Artifacts task.

  • Copy Root: Browse and select your database project folder and concatenate /bin/Debug at the end. If you changed the build output path in your sqlproj file, use that path.
  • Contents: *.dacpac
  • Artifact Name: MyDatabase
  • Artifact Type: Server

copy publish config

Save the build definition.

Let’s test the build definition. Queue a new build like below.

queue build

It will start a new build and produce build artifacts and save it for each build.

build in action

You can Download or Explorer build artifacts in Artifacts page after build is complete.

build success

The final step is to configure the condition to trigger a build.

Edit your build definition and click Triggers section.

Enable Continuous Integration (CI). When you check-in a source file with new change to team project, a new build will be triggered.

You can also schedule build as shown below.

build trigger

Go to Visual Studio, make a change to your database project and check-in the change.

It will trigger a new build.

You have completed setting up build automation for your Azure SQL Database.

Using this build automation, you can package dacpac as nuget package or use the build artifacts to orchestrate release workflow with continuous deployment.

To learn about nuget package, see Part 3. Create Nuget package for Azure SQL Database

To learn about release orchestration and automation, see Part 4. Orchestrate Azure SQL Database Release with Continuous Deployment