Connect a Database Project in Azure Repos to Azure Pipelines

Reading Time: 6 minutes

During SQLBits one of the attendees mentioned that the missing link for them was how to connect a Database Project in Azure Repos to Azure Pipelines.

With this in mind, I thought I would cover how you do that in this post. Even though it can potentially spark a debate about whether to create pipelines using GUI or YAML code.

To help with some jargon here, Azure Repos and Azure Pipelines are both services available in Azure DevOps. Azure Repos is the service you use to store your code.

Azure Pipelines is the service you use to deploy to a large range of services. For example, to deploy updates to multiple SQL Server databases. There are two different ways you can create these pipelines in Azure Pipelines. One is a GUI based method and the other is a code method using YAML.

By the end of this post, you will know how to link a repository in Azure Repos to Azure Pipelines using both methods. For those who are already experienced in this area, feel free to comment on which method you prefer.

Azure Repos example

In this post I will use an example based on a SQL Server Database Project that exists in a Git repository in Azure Repos. Which you would create if you were looking to do state-based deployments to SQL Server databases.

In reality, there are multiple ways to create a Database Project. For example, from an existing database. In fact, you can also create them from an Azure Synapse SQL Pools as well. In the past, I wrote a post that showed how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio.

If you are new to Azure DevOps and you want to test items mentioned in this post I recommend creating your own Azure DevOps organization. So that you can test things safely.

Quick history lesson

Before I go through both methods, I better give a quick history lesson. In case anybody has been looking at older Azure DevOps material online and is looking for the ‘Builds’ feature in Azure Pipelines.

When Azure DevOps was first available the only option was to use GUI pipelines. Within Azure Pipelines you used the ‘Builds’ feature to build your artifacts and then the ‘Releases’ feature to do the employments.

For example, in the ‘Builds’ feature you would build a SQL Server dacpac based on the contents of a Database Project, and then use the ‘Releases’ feature to deploy that dacpac to SQL Server databases.

However, you can now use what is known as YAML pipelines to build(create) a dacpac and deploy the dacpacs in one place. You do this by creating one or more YAML files, which can be stored in the same Git repository as your Database Projects. So, instead of the ‘Builds’ feature you now have ‘Pipelines’.

Now that is covered, I will go through connecting up your Database Project for either method.

Connect Azure Repos to Azure Pipelines using the GUI method

You can still use the traditional GUI method by going into ‘Pipelines’ in Azure Pipelines and selecting the blue ‘New pipeline’ button.

In the ‘Where is your code?’ windows select the link at the bottom to use the classic editor.

From there, you can select the location of your Database Project. As you can see below your Database Project can be stored in various locations. Including GitHub repositories. However, the Database Project for this post exists in Azure Repos.

Afterwards you get to choose a template. It is common for people to choose ‘Empty job’ when they are looking to build(create) a dacpac file from a Database Project. One key point I want to highlight below is that even at this stage of setting up the GUI method Azure DevOps still checks if you want to use YAML files instead.

However, selecting this will allow you to select an existing YAML file in your repository. It does not swap over to setting up a YAML pipeline.

If you do select ‘Empty job’ Azure DevOps creates the below blank canvas for you. So that you can add the relevant steps use to build (create) a dacpac from your Database Project in Azure Repos. In addition, you tend to add a task publish your dacpac. So that it can be used by the ‘Releases’ feature.

Since this post is just to show how to connect your Database Project to Azure Pipelines this is as far as I will go about the Classic Editor. Azure DevOps Labs contains a lab about this called ‘Enabling Continuous Integration with Azure Pipelines‘ .

Connect Azure Repos to Azure Pipelines using the YAML method

In order to select the YAML method you go into ‘Pipelines’ and select the ‘New pipeline’ button. From there, it is a case of selecting where your code is. If the YAML tag exists next to the location it will use the YAML method.

For this example, we select Azure Repos Git since that is where the Database Project is stored.

Afterwards, we select the Git repository that the Database Project is in. Once done you can choose whether to create a new starter pipeline or select an existing YAML file as below.

Which highlights one good point about using YAML files. If you move your repository elsewhere it is easy to setup your pipeline again in Azure Pipelines. Because you can use this section to select the YAML file that you created before.

Anyway, if you choose to create a starter pipeline you get a blank canvas to work on. Now, there is one key point I want to highlight to those who are new to this. You can use the assistant on the right-hand side to start adding tasks.

For example, if I type in SQL in the search tasks box, I get a few results back.

From there, I can select one and fill out the required details and then click the ‘Add’ button. Doing this will add the YAML for that task into the pipeline.

However, I should point out that this can add details that you may find sensitive. For example, your Azure subscription details. If you are new to all of this, I recommend doing all this in your own Azure DevOps organization.

Azure DevOps Labs contains a lab about YAML pipelines called ‘Configuring CI/CD Pipelines as Code with YAML in Azure DevOps‘.

Final words on how to connect a Database Project to Azure Pipelines

I hope this post about how to connect a Database Project in Azure Repos to Azure Pipelines helps some of you.

I am aware there are many different opinions in the community about which one to use. In fact, I suspect showing both of these will cause a debate amongst some of you.

There are perfectly valid arguments for using both. For example, that the Classic Editor is better for demos and the YAML pipeline is more portable.

With this in mind, I invite you to share your preference and why in the comments.