In my previous blog post, we created an Azure DevOps YAML Pipeline to automate our Database Project Build process. In this blog post, we are going to create an Azure DevOps YAML Release Pipeline for Azure SQL.
Quick Overview
- Prerequisites
- Step-by-step Example
- —Download Pipeline Artifact
- —Deploy Dacpac to Azure SQL
- —Adding the right trigger
- Considerations
Prerequisites
If you are just joining this blog post series and you want to follow the step-by-step examples, I advise starting with my previous blog post, Azure DevOps YAML Pipeline – Build Database Projects
Step-by-step Example
During the step-by-step example, we will download the Pipeline Artifact created in my previous blog post. Afterwards, we configure the steps to deploy to Azure SQL. Near the end of this blog post, I will also include a link to my version of the YAML file.
As a first step, we navigate to the Pipelines section in Azure DevOps and we create a new Pipeline as we have done in my previous blog post. To continue we specify where our code can be found. In my case, Azure Repos Git and I select my AdventureWorks repository.
We start from a Starter pipeline and in the following sections, we will configure the required steps.
Before we start with the configuration, remove all boilerplate code besides:
# Starter pipeline# Start with a minimal pipeline that you can customize to build and deploy your code.# Add steps that build, run tests, deploy, and more:# https://aka.ms/yamlpool: vmImage: windows-latestPlease notice that I already updated the vmImage to windows-latest.
To continue we are going to add the concept of stages and jobs which might feel like overkill but depending on your branching strategy this can become useful.
To start with, we start with a new line in the YAML file after the definition of the vmImage. and we type the following syntax:
stages: stage: PublishToAzureSQL jobs: - job: PublishToAzureSQLDevelopment steps: - checkout: noneIf we take a look at the syntax above, you can see that we create 1 stage named PublishToAzureSQL. The created stage contains 1 job: PublishToAzureSQLDevelopment. On top of that, we already added the first step in the job. We define that we don’t require a checkout when this pipeline runs.
The reason why we add this step is that a YAML pipeline uses a checkout mechanism that downloads all the code in the repository by default. If you don’t specify this step you would get the following result during the execution of your deployment pipeline.
Download Pipeline Artifact
As a next step, we will add the step to download the Pipeline Artifact. To start, we create a new line in the YAML file after the checkout step. To continue, we open the Assistant.
Now we search for the Download Pipeline Artifacts task.
Because we use the assistant, we can benefit from the graphical interface to configure the task. Since we want to download the result from the most recent run of our CI-Development-YAML pipeline, we choose a Specific run.
Then, we select the Project where the Build pipeline is located and we choose a Build pipeline from the list. To continue, we configure the Build version to the Latest. For all other configurable fields, we just leave the defaults as is. To finalize, we click the Add button.
At this moment, your YAML file should look like this:
Deploy Dacpac to Azure SQL
We are adding the Azure SQL Database deployment step, to get this done, we create a new line at the bottom of the YAML file. To continue we open the assistant and search for the Azure SQL Database Deployment task.
We click on the Azure SQL Database deployment task but before we start the configuration, we are going to configure a couple of Variables which we will be using to define our database credentials.
To get this done, we click the Variables button next to the Save and Run button.
Now we can create the following variables:
- VarDatabaseServerName
- VarDatabaseName
- VarDatabaseUserName
- VarDatabasePassword
To create them I will be executing the following steps repeatingly:
- Choose new variable
- Define the name and value
- select the tickbox to keep the value secret
- Confirm the variable by clicking “OK”
Now that we have executed this for all the variables we can start to configure the task. As a first step, we specify the connection type. We use the Azure Resource Manager and choose the subscription where the Destination Azure SQL is created in. When you select the subscription, you will need to authorize it.
Now, in the SQL Database section, we can use our variables to determine the Azure SQL Server, Database, Login and Password. To use the variables we can use the following syntax:
$(VariableName)Finally, we specify the location where the .dacpac file is located after we downloaded it. we use the following syntax to get that done:
$(Pipeline.Workspace)\**\AdventureWorksLT.dacpacAs a final result, we can see the following configuration in the task:
As soon as we click the add button, we will see the following YAML code added to the file.
Adding the right trigger
To finalize our configuration, the only step that is left is to specify when the pipeline needs to be triggered.
To get this done, we go to the top of the YAML file and create a new line where we specified the pool. Now we first specify that we want to trigger this pipeline based on changes in the development branch by using the following code:
trigger: branches: include: - developmentThe last thing we need to specify is that this pipeline is dependent on the Build Pipeline that we created before. We can do that with the following syntax:
:resources pipelines: - pipeline: 'CIDevelopmentYAML' source: 'CI-Development-YAML'After we have added these sections, we can save our pipeline and execute it. If you would like to get the full code, you can find it on my GitHub.com page: blogging/cd-development-azuresql.yml at main · Oliviervs/blogging (github.com)
As you will see, I replaced a couple of values with *** since they are references to my Azure subscription and project id.
Considerations
In this and previous blog posts, I have always separated my build process from my deployment process. I only separated this because this works for me. If you are using different branching strategies, it might be that other pipeline configurations work better.
