Automate Build and Deployment of Azure SQL Database using Azure DevOps with Azure Pipelines
Continuous integration and continuous deployment make efficiency, standardization and repeatability to development and production processes of a product. Databases are part of the product so we cannot imagine application delivery process without database.
There are a lot of CI/CD tools available, and Azure DevOps with Azure Pipelines is a very powerful and popular CI/CD tool for automation.. In this article, I am going to demonstrate how to add database to CI/CD using Azure DevOps with Azure Pipelines.
Please note that this article assumes the following pre-conditions:
- You are using Visual Studio.
- You are using a repository supported by Azure DevOps.
- You have a subscription to be able to use Azure Pipelines.
- You are using Azure SQL and it has a user with dbowner role for the pipelines.
- You have a database project created by using Visual Studio Template.
Create Schema
Once you have Azure SQL, your database is already initialized in the server. Database schema needs to be created in the project. Instead of defining schema manually, you can import an existing database by using Import > Database.
Schema changes, adding procedures and implementing post & predeployment scripts can be done from the project. Since this is a simple CI/CD flow, I am not going to explain details of managing project usage. Depending on your flow, usage of the project can be changed to provide needs of development team, rollback and versioning.
Creating Database Publish Profile
Publish profile is used to decide what to continuously deploy. From the solution explorer, right click on database project and choose Publish. Following dialog will appear. Configure the publish settings then click on Create Profile button.
If you have completed the project settings, push the changes to the repository and move on to setting up the build pipeline.
Setup Build Pipeline
When a change is pushed repository, a build pipeline is required to build the project and generate dacpac. To create the build pipeline, go to Pipelines page from the left menu of Azure DevOps then click on Create pipeline button.
There are two configuration options for pipeline. You can create pipeline by using YAML or classic Editor. I am going to use classic Editor in this example. Click on Use the classic editor link after the page loads. Now choose your repository and click on Continue. I am using Azure Repos Git as source in this example. We don’t need the template so just choose Empty job on the next page.
After you create an empty job, you will see the page in the following image. You can rename pipeline name as you wish. I preferred MyDatabase as name. Choose an agent for the pipeline. We have only one environment in this project so you can inherit them in agent menu.
It is better to clean everything when job gets started. Cleaning should be set from Get sources menu in order to make clean build. Click on it then choose true as Clean then choose Source and output directory as Clean options.
We need to configure the agent before configuring the tasks. The agent chosen from the pipeline needs to be imported so pipeline agent will be used for the pipeline. Next step is to add tasks.
1- Build Task
We need to generate the dacpac file for deployment. If you are unfamiliar with dacpac, you can find the detail information from this link. In order to generate the dacpac file, we need to build our database project. For that, we will add Visual Studio build task. Some required fields are needed to be filled. Pick Visual Studio 2017 or higher as Visual Studio Version. Add given text under following image as MSBuild Arguments. Considering that solution supports any platform and has only release configuration, enter any cpu as platform. Choose release as configuration.
2- Copy DacPac
In order to publish dacpac file, it needs to be copied to artifact staging directory. To do that add Copy files task. Choose folder where dacpac file gets generated after build for Source Folder field. Enter your dacpac file name with file extension in Content field. Enter $(Build.ArtifactStagingDirectory) in Target Folder field. The value used for the last field is predefined variable for Azure Pipelines and it gives artifact location where Publish Artifact task is going to be used later on.
3- Copy Publish Profile
Publish profile is required for release pipeline. In order to publish “Publish Profile” file, it needs to be copied to artifact staging directory. To do that add Copy files task. Choose folder where the file is stored in the solution for Source Folder field. Enter your publish profile file name with file extension in Content field. Enter $(Build.ArtifactStagingDirectory) in Target Folder field.
4- Publish artifact
Final task to complete task configuration is publish artifact task. This task publishes dacpac file and publish profile as build artifact. This will be used for release pipeline. To make that, add Publish build artifacts task. Enter $(Build.ArtifactStagingDirectory) in Path to publish field
In order to provide continuous integration, it needs to be activated from trigger tab. Open its page from the tab, check Enable continuous integration then branch filter is required to be added. Choose the appropriate branch for your release flow. Now, build configuration is completed. Save & queue.
When a change is pushed to the branch, build pipeline will be executed. Each build can be seen on the pipeline page. In addition to this, you can also trigger a build manually from its page.
Setup Release Pipeline
In order to provide continuous deployment with Azure Pipelines, release pipeline needs to be configured. To create the release pipeline, go to Releases page from the left menu of Azure DevOps then click on New pipeline button.
When release creation is opened, Azure automatically adds a stage and prompts user to pick a template that is going to be used in the pipeline. No template is needed for our case. We are going to create our stage as empty so so choose Empty job then rename the stage name and pipeline name as you wish.
In previous steps, we created the build pipeline and now we will integrate it with the release pipeline. First, we need to set the artifact. Click on Add button in the artifacts box, choose Build as the source type, fill the required fields then click Add.
We now configured the database artifact. In order to provide continuous deployment, we need to set the trigger for the artifact. Once we activate the trigger, when the build pipeline generates a new artifact the release pipeline will be executed. You can see how to activate trigger from the following picture.
Now, we can move to the next part which is configuring stage part to complete the configuration. Stages and jobs inside of each stages depends on your release flow. Especially for the production environment, there are many parameters (versioning, backup, etc.) that needs to be considered for the flow. In this context, we are going to create a simple release flow that handles only deployment. Click on the link inside of Stage 1. Pick up an agent that has sql package installed.
In this stage, we will run the scripts on Azure SQL using the dacpac file we created with the build pipeline. A related task needs to be added into the stage. Follow below picture to find the Azure Sql Database deployment task and add it.
After adding the task, there are some required fields to be set. First of all, a subscription needs to be chosen. This subscription must have related permissions to maintain the Azure Sql. Subscription can be chosen in dropdown menu which is shown below.
Now we need to set SQL Database fields in order to execute deployment scripts. In this case, we are going to use SQL Server Authentication as Authentication Type. A user must be defined in master database on the Azure SQL server. Enter database name and login credentials for the next fields. Anything wrapped in $() means it is a variable. You can read more about them here. In this example, database name, login and password values are set from the variables.
We keep everything in the database project and everything including predeploy and post deploy scripts are generated when the project gets built so that SQL DACPAC File as deploy type must be chosen. Enter path of dacpac file and publish profile. The buttons which are near each path fields can be used to find the file.
Final step before saving the pipeline is to set variables. In this example, we used pipeline variables. They can be set under variables tab which is shown below.
Now everything that are required to be filled are set. It is time to save the pipeline and test. Once it is saved, release pipeline is ready to use. Now, make some changes in the project and push the changes. When the changes gets pushed, build pipeline will be triggered, and after a successful build the release pipeline will be triggered.
There are many tools for integrating databases into CI/CD processes. In this article, I tried to show how to integrate a database project using Azure Pipelines. Using automation tools reduces human error margin and leads to a more maintainable project so we need to automate our processes as much as we can.
