Continuous database deployments with Azure DevOps

By:   |   Comments (14)   |   Related: > DevOps


Problem

Microsoft Azure DevOps is the next generation of Visual Studio Team Services in the cloud.  This product combines scrum project management tools, software version control, continuous integration and continuous deployment into one service.  This means that the build and release process for a given database project is centralized within the cloud service.  Unlike Visual Studio 2017, approval gateways can be used to guarantee that signoff is given before a given environment is updated.

How can we build and release pipelines to deploy our database project?

Solution

The Pipelines menu contains sub-menus for builds, releases, library, task groups and deployment groups.  We will be focusing on the first two sub-menus.

A build pipeline defines the steps to take during build and the triggers that initiate a build.  A release pipeline can be used to automate database deployments to one or more environments.  Approvers can be added to guarantee that releases occur at the right time in the project schedule.  Of course, logging of both types of pipelines allows the support staff to easily debug any issues that arise.  Last but not least, releases can be initiated manually or automatically off a build.

Business Problem

Our boss has asked us to investigate how to manage continuous deployments using Azure DevOps.  I will be continuing with the Adventure Works Data Warehouse sample that I have been using for this series of articles.

Configuring Azure DevOps

Many of the popular software deployment tools used with continuous integration and continuous deployment (CI/CD) are using the YAML language.  YAML is a human-readable data serialization language which is similar to Python when it comes to indentation.

If you want to learn yet another language, then you are in luck.  The default configuration of a new Azure Dev Ops account using the preview YAML experience for pipeline development.  I rather use the provided menus to quickly create my simple database build and release pipelines.  With that said, let's work on restoring the menus.

The image below shows a sample YAML file named "azure-pipelines.yml".

Continuous Deployment - The YAML experience is the default view for new pipelines.

Please locate the user menu in Azure Dev Ops which is located at the top right-hand corner.  Click the circle to initiate the drop-down menu.  Select the preview features submenu so that we can configure the pipeline behavior.

Continuous Deployment - DevOps Account - Use the preview features option menu to configure the pipeline experience.

The new pipeline creation experience is located at the very bottom of the preview features menu.  Of course, this location can change in the future.  Move the slider from "On" to "Off" to disable this new experience.

Continuous Deployment - Preview Features - Turn off the new YAML pipeline creation experience.

In a nutshell, YAML is a scripting language used by many third-party automation tools.  Microsoft has decided to use YAML for pipeline creation.  In this article, I will only be covering how to create pipelines using the available menus.  Please turn off this preview experience.

Business Goal

The ultimate goal is to create both a build and release pipeline for the Adventure Works database.  The image below shows a completed pipeline that builds our database.

Continuous Deployment - Azure DevOps - Continuous deployment, build pipeline.

In similar fashion, the image below shows a completed pipeline that releases our database to a target server.

Continuous Deployment - Azure DevOps - Pipelines Continuous deployment, release pipeline.

Now that we know two tasks that we need to complete, let's learn about creating a build pipeline.

New Pipeline (Build)

The Builds submenu shows a list of existing pipelines.  Please see the correct image in the Business Goal section.  Choose the new drop-down menu action.  This drop down allows for the building of a brand-new pipeline or importing of an existing one.  We want to build a brand-new pipeline.

Build Pipeline - select source - Choose the team project, repository and branch to build from.

This first step assumes that the developer has checked in a database project into the repository.  There are many different version control systems that can be used with Azure DevOps.  Select the default source which is Azure Repos Git.  Find the correct team project, repository and branch within your account.  Click the continue button to proceed.

Build Pipeline - select template - Choose the empty job template.

The next step is to choose a template.  The quickest way to create the pipeline is to use an empty job.  Click the continue button.

Use the search box to find a given task.  The first task is to use MS Build to convert the database files stored in the Repository into a DACPAC file.  Click the add button to move this task type to our agent job.

Build Pipeline - Add MS Build Task - MS Build compiles the code that is stored in the repository.

Under the details section of the first task, find the project in the master branch.  The display name can be changed if you desire.

Choose the project to build.

The second task is to copy the output files from the MS Build action to the artifacts staging directory.  The screen shot below shows the search and addition of the task to the agent job.

Copy output files from build directory to staging before archiving.

Use the tool tips hints to fill in the source and target folders for the copy operation.

Build Pipeline - Copy Files - Choose the correct source and target folders.

The third task is to publish the build artifacts.  These files will be used by the release process in the future.  The image below shows the search and addition of this task to the agent job.

Build Pipeline - Publish Build - The build has to be published as an artifact to be used by the deployment pipeline.

This task requires two key pieces of information: the path to artifact staging directory and the location to publish to.  The display and artifact names are user defined.  You can change them to your liking.

Choose a name for the artifact.

Now that agent job is complete, save the pipeline and queue it for execution.

Build Pipeline - Save Build Pipeline - Queue the job for processing.

The above image shows the save and queue dialog box.  Do not enter any text in the commit box.  It is actually a number given to the build and can be left blank.

Build Pipeline - Build Pipeline - Logging - The pipeline built correctly.

The above screen shot shows the execution of the complete build pipeline.  A hosted Visual Studio 2017 virtual machine is used to check out the source files from the repository, build the solution using MS Build, copy the output files and source files to a staging directory and publish the files in the staging directory as an artifact.

Clicking on the green succeeded or failed hyperlinks brings up a details window with line by line entries from the log file.  Shown below are the steps of the MS Build task.

Build Pipeline - Build Solution - Log Details - Detailed logging is provided by the service for professionals to debug a failing process.

The artifact explorer can be used to browse the files published as a result of the build.  Please see the build results image.  This artifact explorer button is located at the top right of the window just under the title bar.  Both the release and artifacts buttons are right next to each other.  Make sure you click the correct one.

Looking in the bin/debug directory using the artifact explorer, the three files that are generated by Visual Studio 2017 during a normal build operation are now located in Azure DevOps after executing our "advwrks4tips19-CI" build pipeline.  Please see image below.

This is not surprising news since Azure DevOps is using the same components that Visual Studio 2017 installed on a local machine.  This is a fact since we can see that the "CoreCompile" entry in the image which has a file path including the words "Visual Studio 2017".

This explorer shows the files that are

To recap, the build pipeline is a four-step process.  First, we need to check out the source files from the Git repository.  Second, MS Build is used to compile and create our output files.  Third we need to copy the output and source files to a staging directory.  Last but not least, we need to publish the staged files as a named artifact.  These artifacts will be used by the release process.

For future research, you should look into build triggers.  You can have a build take place at a schedule time or have a build happen after code is checked in.  I am not covering these topics today.

New Pipeline (Release)

The Release submenu shows a list of existing pipelines.  Please see the correct image in the Business Goal section.  Choose the new drop-down menu action.  This drop down allows for the building of a brand-new pipeline or importing of an existing one.  We want to build a brand-new pipeline.

Once again, we want to choose an empty job as our template.

Release Pipeline - select template - Choose an empty job template.

The first step in creating a new release pipeline is to choose our artifact.  Select MS Build as the source type.  Find the dev ops project and build pipeline.  Click the add button to complete this task.

Release Pipeline - select template - Select the artifact from the built projects.

The second step in creating a release pipeline is to choose the deployment task.  We can use the search button to look for all database related tasks.  Select the Azure SQL Database deployment task.  Click the add button to continue.

Release Pipeline - Database Task - Use the Azure SQL Database deployment task.

To configure this task, we need to add certain static information such as Azure subscription name, SQL server name and database name.  We need to add both the login and password information.  However, this information will be added to the log file.  There must be a better way to configure this task?

Release Pipeline - Azure SQL Publish - Select the server and database name.  Note, user and passwords will not be secure.

We can overcome the potential security issue by using variables.  The variable names will show in the log file instead of the secrets we are trying to protect.  The image below shows that a sqluser and sqlpwd variable have been defined and set.

Release Pipeline - Job Variables - Use job variables to obscure the user name and password.

Going back to the Azure SQL (database deployment) Publish task, we can leverage the variables to complete the form.  Make sure the variable name is enclosed with $() so that a look is performed instead of the literal characters being used.

Release Pipeline - Change Entries - Use the variables instead of hard coding values.

If we take a look at the tasks under Stage 1, we see the one agent job executing an Azure SQL publish task.

Release Pipeline - The tasks - Only one task is needed to deploy the project.

To save the new release pipeline named advwrks4tips19-CD, we are prompted to provide a comment.

Release Pipeline - Save work - Add a comment to save the work.

There are several options that we can choose for a release pipeline.  First, we can schedule when the deployment happens.  For instance, the development environment could get the latest development code on Saturday at 8 am.  The lightning bolt without a checkmark indicates that a given step has a manual trigger.

Second, we can choose various pre-deployment conditions for Stage 1.  Approvals and Gates will not be covered today.  I leave these topics for you, the reader to research.   What will trigger Stage 1 and execute the tasks associated with the agent job?  It can happen right after the artifact is created or we can manually release Stage 1.  Let's choose the later to see what happens.

Release Pipeline - Set Options - The triggers can be set to automatic or manual.  Both approvals and gates can be used to ensure the timing of the release.

If we take a look at the release for this pipeline, we can see that no results are found.

Release Pipeline - Current releases - Existing releases will show up in this list.

In summary, there are two steps to create a release pipeline.  First, we define the artifact that we want built.  Second, we define the actions that will be performed by the agent job.  In our case, we want to deploy the database project to an Azure SQL database.  There are many options to configure for scheduling, approvals, gates, and trigging.

Testing a Release Pipeline

If we manually trigger the release pipeline, it will stop at stage one.  This means we have to go into the release and confirm the deployment.  The image below shows this situation.  Manually deploying step 1 will continue the execution of the pipeline.  In short, trigger types, approvals and gates can prevent an unintentional release from happening.

Test - Release Pipeline - Manually release the pipeline.

Just because code has been tested by the developer, checked into the repository, build by a pipeline and manually deployed, it does not mean that an error can't occur.

Test - Release Pipeline - The release fails.  Looing thru the log, the target environment is wrong.

I left this bug in the build intentionally to prove this point.  A database project has various settings including target platform.  Right now, the target is set to SQL Server 2016.  We need to change this setting to Azure SQL database.

Visual Studio - Project Settings - Change the target platform.  Save the project.

Of course, this change needs to be committed both locally and remotely.  The remote push of the code will update the repository in Azure Dev Ops.

Visual Studio - Check In Changes - Use team explorer to commit and push the changes to the Dev Ops repository.

We have new code in the repository but our artifact is from build number 8.  We need to queue up a new build of the code.  The image below shows the dialog box that is used to perform this task.

Create build #9 using the newest code.

Build number 9 executed without any errors.  That is great.  Now we can use the release pipeline to deploy the project to the target database.

Test - Release Pipeline - Build #9 compiles and creates the artifact successfully.

Use the create a release button on the release pipeline named advwrks4tips19 to start the process.  The image below shows that a comment is required to start the release.

Test - Release Pipeline - Execute the release pipeline.

The release will stop at stage 1 since we have a manual, not automated trigger for this stage.  Add another optional comment and click the deploy button.

Test - Release Pipeline - We can see that release 1 failed.  Manually release try #2.

It is always a good practice to verify the execution of new pipelines.  We can see that the deployment was successful.

The log file of the release pipeline is all green.  Which means everything worked.

Before executing the release pipeline, the dbs4advwrksprd database located on server sqvr4tips19 was empty.  After the release, the database has the schema defined in our database project.

SSMS - Object Explorer & Query Window - The newest project objects are now deployed to the Azure SQL database.

The concept of a release pipeline can be summarized in a few words.  The process takes an existing build artifact and deploys it to a target system.

Summary

Today, companies are creating agile teams in which members collaborate with each other, teams self-organize the work load, and cross functional skills sets of the members allow for rapid delivery of work items.  It is not surprising that Microsoft Azure DevOps combines scrum project management tools, software version control, continuous integration and continuous deployment into one service.

The Pipelines menu is the main place to create both build and release pipelines.

A build pipeline defines the steps to take during build and the triggers that initiate a build.  To build a database project, three simple tasks are used to complete the process.  Build can be initiated manually, take place at a scheduled time or after code has been committed (continuous integration).

A release pipeline can be used to automate database deployments to one or more environments.  Of course, logging of both types of pipelines allows the support staff to easily debug any issues that arise.  A release can be initiated manually, take place at a scheduled time or after a built has completed (continuous deployment).  Approvers and gates can be added to guarantee that releases occur at the right time in the project schedule. 

If you are not using an automated build and release process, you should take a serious look at the Pipeline features in Azure Dev Ops.  For more information, please see the MSDN documentation using this link.

Next Steps
  • Leveraging pre-deployment and post deployment scripts
  • Using Azure Test Plans for Quality Assurance


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, October 21, 2021 - 2:22:55 AM - Amir Back To Top (89345)
very nice explanation. It helped me understand how sql project can be added to ci/cd. I do have question. When the project is build once somoene check in the code. The build can be trigger, it creates file. What are those files? if Deckpac what do they have a complete database? I am thinking in the process that if the script files/ decpac are created and if there is a change in an existing database table we cannot drop and create a new table because it will delete all the existing rows. So if you can tell me about the file generated and how we can perserve the intergirty of the data?

Wednesday, June 30, 2021 - 9:22:17 AM - Mustaf Back To Top (88925)
Hi John,
Awsome article.

I just have some doubt, If while DACPAC deployment, our deployment fails and lets say 10 objects is being modified, Do we have any option to rollback the release?

I just don't want to deploy anything if my deployment fails, Please throw some light here.

Thursday, April 8, 2021 - 7:54:00 AM - Ananth Back To Top (88495)
Excellent Article I have ever found in the internet (for the Database related code Deployments in Azure/using Azure DevOps).

Thursday, June 18, 2020 - 12:28:25 AM - Sri Back To Top (86016)

Excellent article


Friday, May 15, 2020 - 6:11:26 AM - Abraham Ocon-Caballero Back To Top (85670)

Hi John,

Awesome article!!

I have a question regarding the deployment account permissions, currently as DBA I am removing all db_owner permissions on accounts and as a result, the dev teams cannot deploy anymore with the TFS user, even when the user has ddl_admin rights on the database.

Have you seen that problem before and how have you fixed it? By PCI policies, we are not allowed to give db_owner on Production (and for common sense of course) so we have run into a wall.

Thanks for any advice!!

Keep the great work!

Abraham


Tuesday, April 21, 2020 - 7:48:14 AM - Shashank Dahake Back To Top (85431)

Great work ... with picture it always helps easy to understand man ... great work thanks for this.


Monday, February 24, 2020 - 11:24:11 AM - John Miner Back To Top (84769)

Dear Reader,

The process is the following.

1 - Make changes in VS project.

2 - Publish changes into GIT.

3 - Create new build/release

4 - Deploy new release to the correct environment.

Since sqlpackage.exe is used in the process, only deltas are deployed to the target database.

See image of MSDN documentation stating this fact.

Sincerely, John


Wednesday, February 19, 2020 - 5:23:02 AM - Azure Learner Back To Top (84633)

This is excellent. I have one question, to explain that, i am considering three environments (dev, test and prod). For suppose, I have created build and release pipelines to deploy SQL code from Dev env to test and prod using pipelines and lets say it is successfull. Now, I would alter the schema in Dev or lets say I create some objects, tables etc in Dev Env, what is the process to deploy the delta changes to test and prod without creating a new solution again from VS?


Thursday, October 3, 2019 - 9:56:39 PM - Ross Back To Top (82663)

What if all hell breaks loose during a deployment? Is there a way to rollback automatically?


Tuesday, August 20, 2019 - 8:42:25 AM - Anbarasan Dhanushkodi Back To Top (82109)

Thanks for the post, can I use this task to execute .sql file from a repo on Azure Data warehouse directly ? 


Sunday, August 4, 2019 - 11:59:48 AM - Rafael Braga Back To Top (81958)

You sir, are a gentleman! Worked 100%, I'm now using this on my devops pipelines to properly update my stored procedures. 


Tuesday, May 28, 2019 - 11:43:50 AM - astmart Back To Top (81234)

What a fantastic well written article and even though MS continues to make changes to the AzureDevOps Portal the principles outlined here remains very much the same!!

Works like a dream too


Friday, May 24, 2019 - 6:52:02 AM - Dominic Back To Top (80187)

Many thanks, John! Excellent article. You helped me a lot with my first Azure DevOps CI/CD process.


Friday, April 5, 2019 - 6:16:43 PM - j mo Back To Top (79487)

 Helpful! A very helpful future article would be using Azure DevOps to build and deploy to an on-premises sql server using integrated auth, assuming the organization already has Azure AD setup.















get free sql tips
agree to terms