Automate your Database Builds – Using Azure DevOps Pipelines

After we successfully introduced a database development strategy in my previous blog post series, Getting Started With Database Projects & Azure DevOps, we can look at how to introduce a database deployment automation strategy using Database Projects and Azure DevOps Pipelines.

As a starter, we will first be implementing a build automation process and in future blog posts, we will go through the different ways of deployment to different environments. On top of that, we will also discuss the differences between SQL Server and AzureSQL database deployments.

Quick Overview

Prerequisite

Step-by-step Example

Build Pipeline First Execution

Trigger Build pipeline when a new change is identified

Help: I can’t run my Build pipeline

Help: Classic Editor option not available

What’s Next?

Prerequisite

In this blog post, we will be implementing a build pipeline in Azure DevOps step-by-step. The prerequisite to getting started is that you have already set up your Database Project and are already using it to execute development and your deployments manually.

Step-by-step Example

For this example, we will be automating the Build-process for the incoming changes on our development branch, which will be deployed to the development environment in a future blog post.

As a first step, we navigate to the Azure DevOps portal, sign in and navigate to our Project where our source code is located.

In my case, I have opened my DatabaseDeploymentAutomation Project.

As a next step, we are navigating to Pipelines on the left-hand side of the screen as marked in the screenshot above.

To get started, we will now choose “New pipeline” on the right-hand side of the screen.

For this blog post, I will choose to create a pipeline using the classic editor (GUI). if you want, you can also create a pipeline using YAML. if you want to find out how to use YAML: YAML pipeline editor guide – Azure Pipelines | Microsoft Learn

As mentioned, for now, I will choose the “Use the classic editor” option.

If you don’t have this option, go to the following section in this blog post: Help: Classic editor option not available.

To continue, we need to define the source we want to use. I want to get a specific Azure DevOps Repo, from which I want to build the development branch continuously when a change is made.

So, I select Aure Repos Git, I first need to define in which project my repository is located, I will be choosing my DatabaseDeploymentAutomation project. Secondly, I need to define the specific repository, in my case I want to build my AdventureWorks repository and finally, I need to choose which branch, which is the development branch for this example.

To finalize, I choose “Continue”.

As a next step, you will be able to select a template if you want, but for this example, I will be building the build pipeline from scratch so I choose the Empty job option.

The first step I now take is to give my pipeline a name, since we are currently looking at a Continuous Integration (=CI) pipeline, I’m naming my pipeline: CI-Development-DatabaseProjects

As you can see in the screenshot above, a couple of tasks are predefined and configured. If you take a closer look at the Agent Pool section, you will see that the default Aure Pipelines agent pool will be used for the Build process.

By default, It already created Agent job 1 for us where we now can start to define the required steps to automate our Build.

As a first step, I will be renaming Agent job 1 to ensure that it is really clear what this agent job is going to do. To get this done, I click on Agent job 1 and change the Display name to Build Database Project. We leave all other options as is.

Now we are going to add our first step in the agent job, which will be to build our Visual Studio solution. To get this done, I click on the + sign next to my agent job (Build Database Project)

As you can see we now get a list of possible tasks, search for the Visual Studio build task and choose Add

Now I select the newly added task and can start configuring. As you can see it will build all solution files in all available subfolders by default. if you want to build a specific Visual Studio Solution, you will need to navigate to the exact solution file you want to build.

For now, I will just leave the defaults and continue by adding a second step. The next step will be a Copy files task to copy the results from the Build process to an accessible location.

To get this done, I add another step to my agent job by clicking the + sign next to my Build Database Project agent job and I search for the Copy Files task and choose Add.

In this step we really need to do some configuration, as a first step, I’m changing the Display name to: Copy Files To Target Location.

As a second step, I will a defining the Source Folder where the files need to be copied from, in this case, I want to use the default working directory since we didn’t adjust any of the default configurations. To get this done I use the following variable in the Source Folder field:

$(system.defaultworkingdirectory)

Now we can dine the Contents, I only want to copy my dacpac file. To get this done, I will be using the following syntax in the Contents field:

**\*.dacpac

In case you are working with Publishing Profiles, you will need to specify this in the Contents field as well, then your Contents field could look like this:

**\*.dacpac
**\*.publish.xml

But in this example, I will only be copying the dacpac file.

As a final step, we will be defining the Target Folder, in this example, I will be copying the artifact to the default Artifact Staging Directory. To get this done I will be using the following syntax

$(build.artifactstagingdirectory)

As a result, I now have the following configuration in my Copy Files task:

To finalize our pipeline, we need to add 1 more step to publish our newly created artifact. So we first choose to add an extra step to our Build Database Project agent job and we search for the Publish build artifacts task and choose Add.

In this task, we specify the Display name as Publish Artifact: Publish Artifact: dev-drop first. Then we leave the Path to publish untouched and we continue to specify the Artifact name. I have chosen to call it dev-drop

As soon as this is done, it finally is time to hit that Save & queue button.

Provide a meaningful save comment and choose “Save and run” at the bottom.

Build Pipeline First Execution

As soon as you have clicked the “Save and run” button, you will be redirected to a page similar to:

If you wait for a couple of moments, you will see that the Build has been completed successfully.

If your build failed, and you received the following error message:

Checkout what to do in the help section below: Help: I can’t run my Build Pipeline

If your run was successful, we can identify this run was executed for my AdventureWorks repository and to be more specific, for my development branch. The total duration was 27s and it has published 1 artifact.

If you want to check the Artifact, just click on the 1 published; 1 consumed section and you will be redirected to the Artifacts page where you can find out which dacpac file is available.

In my case you can see that in my dev-drop artifact 1 dacpac is available:

Enable your pipeline to build when a change is identified

As a final step, we are going to enable our Build pipeline to run every time a change is made to the development branch.

To get this done, we navigate to our newly created pipeline, click on the 3 dots on the right and choose Edit.

Then we navigate to the Triggers section and mark the Enable continuous integration tickbox.

If you want to execute it on a daily base instead of continuously, you can create a Scheduled trigger in this section as well.

To finalize, we navigate to the Save & queue button and choose Save.

We now have successfully automated our Build Process!

Help: I can’t run my Build Pipeline

When you are executing a pipeline for the first time in an Azure DevOps Organization, you will probably get the following error:

To get this issue resolved, you will need to fill out a form which looks like this:

As soon as Microsoft has revised your request, you will be informed. If you then re-execute your pipeline it should work just fine.

Help: Classic Editor option not available

If you don’t have the: “use the Classic Editor” option, it probably will be disabled either on the Project level or the Organization level settings.

To find out, navigate to the Project settings, and in the Pipelines section choose Settings. If you scroll down you will find the “Disable creation of classic build and classic release pipelines” option. If this setting is enabled, you should disable it to be able to use the classic build pipeline option.

In case this doesn’t solve your issue, you will need to check the organization-level settings. Navigate to your organization, choose Organization settings, go to the Pipelines section and choose Settings.

In this overview you should look for the following setting:

What’s next?

Shortly, I will be releasing a couple of blog posts which will guide you through the following processes:

• Building an Azure DevOps Release Pipeline for Azure SQL
• Building an Azure DevOps Release Pipeline for SQL Server
• …