Part 4. Orchestrate Azure SQL Database Release with Continuous Deployment

This blog is part 4 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
Part 3. Create Nuget package for Azure SQL Database
Part 4. Orchestrate Azure SQL Database Release with Continuous Deployment (this page)
Part 5. Use your own Build and Deployment Agent

In part 4, we will play through how to automate release workflow for Azure SQL Database using continuous deployment. To successfully run this part, you must successfully run part 2 first.

In this scenario, I will use the following simplified release workflow.

  1. When a new build is successfully created, the database is automatically deployed to User Acceptance Test (UAT) server so that user acceptance test can be performed.
  2. Production server deployment is pending while UAT is in progress.
  3. When UAT is passed and approver signs off the deployment to PROD, deployment to PROD environment is executed.

In part 4, we will play through

  • How to configure release environment and deployment workflow.
  • How to configure deployment task.
  • How to configure release trigger with continuous deployment.

 


 

Go to RELEASE page on your team project web portal and add new Release Definition by clicking +

menu

Use Empty deployment template

deployment template

Provide a name for the definition.

Rename Default environment to UAT which represents User Acceptance Test environment in our scenario.

Click + Add tasks and add Azure SQL Database Deployment.

add task

First, configure Deploy Azure SQL DACPAC task. It will start with following page.

task config

Click [Manage] for Azure Classic Subscription property. It will open Services control panel.

Click New Service Endpoint >> Azure Classic as shown below.

add service

It will start ADD NEW AZURE CLASSIC CONNECTION page shown below.

Select Certificate Based and provide a name for the connection. e.g MyConnection

Get publish settings file by clicking download link at the bottom of the dialog and open the file. Copy and paste corresponding string values from the file to connection configuration dialog. Id, Name and ManagementCertificate.

Then click OK to create new connection.

<?xml version="1.0" encoding="utf-8"?>
<PublishData>
  …
<Subscription
…
Id="yoursubscriptionid"
Name="yoursubscriptionname"
ManagementCertificate="yourmanagementcertificate " />
…
</PublishData>

add connection

Go back to Release Definition page and select the configured connection from drop down.

connection dropdown

Click … on DACPAC File property.

Link with your build definition. Then select a dacpac file.

link source

select file

Set Target properties by providing your Azure SQL server name, database name for UAT environment. Let’s name it as MyDatabaseUAT. PROD database name will be MyDatabase.

  • Provide Server Admin Login name.
  • For Password use variable $(password) to secure the password. You don’t want to show your password in clear text here.
  • Set Firewall to AutoDetect.

set target

Go to Configuration section and add variable ‘password’ with value. Don’t forget to lock it.

add variable

Save. Let’s add PROD environment

Clone UAT to add PROD environment.

clone env

Change Database Name of Azure SQL Database Deployment task to MyDatabase in PROD environment.

target for prod

The next step is configuring continuous deployment on this release definition and release workflow.

Go to Triggers page and select Continuous Deployment.

Select your database build definition for [Set trigger on artifact source to] property. When a build is successfully completed, the build process will create a new release workflow item for UAT and PROD environments.

trigger

For release workflow and orchestration, let’s configure the following

  • Start deploying database to UAT automatically whenever a new version of database is successfully built.
  • PROD deployment stand-by with pending state until UAT deployment is successful AND all authorized personnel sign-off for the deployment to PROD environment.

Click Edit icon for each environment and configure UAT.

Configure UAT Trigger to [After release creation]

Trigger config

Configure PROD Trigger to [After successful deployment on another environment] and set Triggering environment to [UAT]

trigger config for prod

On Prod Configuration page, go to Approvals page.

Add Pre-deployment approver and enable email notification.

approver

Save all changes.

Now we can test the end-to-end flow.

Go to Build page and select your build definition. Queue Build…

Upon successful build, Team Services creates a new release for UAT and PROD environments as we defined.

UAT deployment is performed automatically and PROD deployment is pending for approval.

Approve the PROD deployment. It will continue the release workflow and finish the deployment to PROD.

release approve

You have completed automation of deploying Azure SQL Database and you have a fully working environment to develop, build and deploy Azure SQL Database with CI and CD.

In Part 5. Use your own Build and Deployment Agent, we will go over a more advanced topic where you can bring your own build and deploy agent instead of using hosted agent. This way you can have more control over the build agent’s resource, build tools version such as DACFx and SQLPackage.exe and target environment etc.