In my previous blog post, Automate your Database Builds – Using Azure DevOps Pipelines, we successfully automated our Build process. As a next step, we will be automating our deployment process to Azure SQL.
In this blog post, we will go through the process step-by-step to set up our Release pipeline in Azure DevOps and execute our first release just by clicking a button.
Quick Overview
- Prerequisite
- Step-by-step Example
- Using Variables in the Release Pipeline
- Manually Trigger Your Release Pipeline
- Trigger Release Pipeline After a Successful Build
Prerequisite
Before you can follow the step-by-step example, you must set up your Database Project, have it available in Source Control and have created a Build Pipeline.
If you have made all the preparations, you are ready to continue with the step-by-step example. If not, I advise doing the preparation first.
To get started with Database Projects & Azure DevOps, you can find all related blog posts using the following link: Getting Started With Database Projects & Azure DevOps
When you are already working with Database Projects & Azure DevOps and you just need to set up your automated Database Project Build process, you can use the following blog post that will guide you through the process: Automate your Database Builds – Using Azure DevOps Pipelines
Step-by-step Example
As a first step, we go to our Azure DevOps environment, sign in and navigate to our Project. Go to Pipelines and choose Releases.
To continue, we go to the New button and choose New Release Pipeline.
As soon as the new Release Pipeline is created, we need to specify if we want to use a template or start from an Empty job. I will be starting from an Empty Job.
As soon as we have confirmed that we are starting from an Empty job, we change the name of the release pipeline to “CD-Development-DatabaseProjects-AzureSQL” by clicking on the New release pipeline title.
Now we are ready to build our Release Pipeline. As a first step, we will add an artifact to our Release Pipeline. To get this done, we go to Add an artifact.
Since we already created an Azure DevOps Pipeline to build our artifact, we choose “Build” as a Source type. To continue, we choose our “CI_Development-DatabaseProjects as a source.
To finalize, we choose Add.
Since we have now configured our source artifact, it now is time to configure our stage. If you are wondering what a stage actually is I will briefly explain. A stage is a logical group of steps you want to execute during deployment.
In my case, I will be creating 1 stage where I will configure everything to be able to deploy to the development environment.
The first step we will execute is renaming the stage from Stage 1 to Development. To make the change just click Stage 1, and change the Stage name to Development.
As a next step, we start the configuration for our deployment to the development environment. To start, we click on “1 job, 0 task” to get the Stage Tasks window available.
To continue, we click on the Agent job and change the Agent Job name to Deploy Development Environment.
As you can see in the screenshot above, I change the display name. Just to make sure I clearly know what this job is supposed to do. At this moment, we don’t change any other options. We will be using the Hosted Windows 2019 with VS2019 as an Agent to deploy our solution to Azure SQL.
If we scroll down a bit in the Agent Job options, we can see that it has automatically added the source artifact as well.
Now, it is time to add a new task to the Agent Job. To add a new step, we click the + sign on the right next to the Deploy Development Environment Agent Job.
As you can see, the Add tasks window has appeared on the right-hand side of the screen. we are now going to use the search functionality and search for Azure SQL.
As soon as we have found the Azure SQL Database deployment task, we click the add button and click on the newly added task.
Now, we can start to configure the required connection details. First, we change the Display name, choose how we want to connect to Azure and finally which Azure Subscription we want to use.
When selecting the Azure Subscription, it might be that you need to authorize the connection. Just click the Authorize button on the right-hand side, and follow the required steps.
As a next step, we will need to define the SQL Database connection. For ease of this blog post, I will be showing you how to connect with SQL Server Authentication. Though I would not recommend using this method in a business environment. I would advise using a Service Principal to create the connection from Azure DevOps to your Azure SQL Database.
As soon as we have specified all required information in the SQL Database section, we can continue to specify how we would like to deploy in the Deployment Package section.
First, we need to specify the deployment type, we will be deploying with a SQL DACPAC file and using the Publish action. As a next step, we need to specify where the SQL DACPAC file is located. You can easily do this by using the … – sign on the right-hand side.
In case you want to use a Publishing Profile, you can also identify the location in this section.
As soon as we have configured all the above, we can click on the save button at the top of our screen to finalize.
Using Variables in the Release Pipeline
In the database connection we made previously, I hardcoded the SQL Database Credentials, which of course isn’t very secure. What could already help is passing the credentials as variables.
We need to edit our existing Release Pipeline. To be able to edit the Release Pipeline, we navigate to the Release Pipeline section, select our Release Pipeline and choose edit.
To continue, we navigate to the Variables Tab to create the required variables.
For now, we will be using Pipeline variables, if you want to you can create Variable groups as well.
In this example, we will be creating 4 variables: VarSqlServer, VarUserName, VarPassword and VarDatabase. We will be changing the Variable Type to secret by clicking on the locket on the right.
As soon as we created the variables and clicked on the locket next to each variable. We are specifying the values for the variables.
To use these variables in the Release Pipeline, We navigate to the Tasks Tab and choose the Deploy Azure SQL Dacpac task.
Now, we navigate to the SQL Database section and change the hardcoded connection information with the newly created variables. To get this done we will need to use the following syntax:
$(VariableName)The end result looks like this:
Now, we just save the Release Pipeline and we’re good to go.
Manually Trigger Your Release Pipeline
We now have created our Release Pipeline and it is about time to test it. Click the Create release button next to the Save button.
Then, to finalize, just click the Create button at the bottom of the page. As soon as you have clicked the Create button, you will be redirected to a page which looks like this as soon as your Release Pipeline has finished:
Trigger Release Pipeline After a Successful Build
To automate our Release Pipeline, we are navigating to our Artifacts section in the Release Pipeline. At the right top corner of our artifact, we can see a lightning bolt to identify a Continuous deployment trigger.
As soon as we clicked on the lightning bolt, a configuration pane on the right will be showing. In this screen, we can choose which trigger we want to use. In my case, I will be automating the deployment process to my development environment after each successful build.
To automate the deployment after each successful build, I will be enabling the Continuous deployment trigger. By doing this, this Release pipeline will be executed every time a new successful build is available.
There are a couple of alternatives available as well. For example, a Pull request trigger and a scheduled release trigger.
Be First to Comment