Streamline your SQL deployment using Azure DevOps and Database Projects
In previous blogs, we learned how to streamline your SQL development with Database Projects and Git and how to integrate Database Projects with Data Factory for enhanced efficiency.
In this final blog post of this three-part series, we’ll demonstrate how to deploy your database project to a production database automatically using CI/CD. By doing so, our production database is updated every time we update the main git branch without having to manually make changes.
First things first
Before lift-off, make sure the following prerequisites are met:
The previous two blog posts in this series were read and the steps were replicated.
In this tutorial, we will create the YAML pipeline step by step. The full YAML pipeline can be found on the Plainsight Pro GitHub.
Two additional Azure SQL databases have been created where we will deploy the dacpac files. One for QA and one for PROD.
.dacWHAT?
Let’s first reiterate on the concept of a .dacpac. Database projects consist of pre- and post-deployment scripts along with the database objects (like tables, views, stored proc,...). These components are then built and will generate a .dacpac file. This file can then be published to another database (the production database in this case).
Create a YAML pipeline
The following diagram describes how our YAML pipeline will work:
When a pull request is made to the main branch in the repo, the pipeline will be triggered to build the dacpac file and deploy this to the QA database. Next, after the manual validation has been accepted the deployment to the production database will start. This approach gives us the ability to automatically deploy to our production database but still has manual check in place and the ability to view the changes in QA.
The first part of this will be the Continuous Integration or CI part. CI will make sure that the code pushed to git is tested before being sent to production. To start off, go to Azure Devops. Navigate to your project and click on pipelines. Then click create pipeline. Next, our code is on an Azure repo, so select Azure Repos Git and select the right repository. We are going to start off with a starter pipeline. Now the editor shows up. Delete everything except the trigger on main and the pool. Change the pool from ubuntu-latest to windows-latest. Your code should look like this now:
trigger: - main pool: vmImage: windows-latest
First create a stage and job for the build process, this can be done by typing the following code:
stages:
- stage: BuildProject
displayName: Build the database project
jobs:
- job:
displayName: Build and publish artifact
steps:
This will create a stage for building the project and a job that will have the tasks to build and publish the artifacts.
Put the cursor below the code, click on show assistant and go to tasks. Choose .Net Core and enter **/*.sqlproj in the path. This task will build our solution and check for mistakes. Next, we will add the Copy files task. Enter the following content:
This will copy our dacpac file from the build to the artifact staging directory. After this task, add the Publish Pipeline Artifacts task. Give the directory path and an artifact name:
The copy and publish jobs will make sure that our artifacts can be used in the next jobs.
The build stage is now complete. We will continue with the deploy stage. Enter the following code to create the new stage and the new job to deploy this to QA:
- stage: DeployProject
displayName: Deploy database project to QA and Production
jobs:
- job : DeployToQADatabase
displayName: Deploy to QA database
steps:
For the first step, choose the download pipeline artifacts task. Enter the same name as in the publish task.
Now that our dacpac file is downloaded, we will deploy this to the QA database. Choose the Azure SQL Database deployment task. Choose the right Azure subscription, your QA database and enter the authentication details. In this case I picked SQL Server authentication.
If you use this type of authentication, make sure that you disable Entra authentication only in the sql server settings in the Azure Portal:
Use the following code to create a login on the master database and create a user with db_owner role on the QA and PROD databases
CREATE LOGIN [Username] WITH PASSWORD = 'Password'; CREATE USER [Username] FOR LOGIN [Username]; EXEC sp_addrolemember 'db_owner', Username;
Now in DevOps fill in the right details for the sql login and password. The best practice is to use a variable which can be created by clicking on variables and creating one. Then fill in $(VariableName) into the password field. In my cased I used $(sqlpassword).
In the deployment package, you can optionally enter additional arguments. The arguments used here are:
/p:DropObjectsNotInSource=True /p:ExcludeObjectTypes=Users;Logins
These arguments will delete objects not in the dacpac file except users and logins. This is not the default behavior when deploying the dacpac file.
Now that our dacpac file is deployed to the database, we will add a new job that makes sure manual validation is required before continuing with the deploy to production job. Add the following code below the last job:
- job: waitForValidation displayName: Wait for external validation pool: server dependsOn: DeployToQADatabase timeoutInMinutes: 4320 steps:
The pool here is specified as server because the manual validation can only run as an agentless job (as described in the Microsoft documentation). Defining it in the job will overwrite the default pool (windows-latest, defined at the top of the YAML file) for this job. The dependsOn property makes sure that it is only run when the DeployToQADatabase task has been successfully completed. The timeout for this job must be higher than the timeout for the task, which is why we set it on 3 days. Now add the Manual validation task. Enter your email in the notify users tab and optionally instructions. Add the following line to the task after it is added to the project:
timeoutInMinutes: 1440
This will set the timeout to one day. The default timeout is one hour.
The manual validation job is now complete. The last job will be the deployment to the production database. Add the following code for the job:
- job: DeployToProductionDatabase displayName: Deploy to production database dependsOn: waitForValidation steps:
Add the download pipeline artifacts task and Azure SQL Database deployment task like we did for the QA database. The easiest way is to copy the text from the QA part and change the database name to the production database if they are on the same server.
Let’s take our YAML pipeline for a spin!
After adding the last job, the pipeline is now finished. Click save and run to test the pipeline.
When first running the project, the following error message could appear:
To resolve this, fill out the form on: https://aka.ms/azpipelines-parallelism-request
This request generally gets approved quickly. After this error has cleared up, verify that the pipeline finishes successfully and apply the manual validation when needed.
The CI/CD to the QA and Production database is now fully set up.
Stay tuned!
By implementing this solution, whenever the main branch is updated, the CI/CD process will:
Automatically build the dacpac and check for any warnings or errors
Deploy to the QA database to verify the changes to the database
Wait for manual validation before deploying to production
Update the production database
This solution makes sure that no changes will be needed to the production database directly and that there is always a check and validation in place before deployment to the production database. It also makes it possible for developers to use the full functionality of git in their SQL development process.
👉 Do you have any questions or need help implementing this solution?
At Plainsight we are always in for a chat! ☕
Anxious to know what Plainsight could mean for you?
Read more “About us”
Consider “your career at Plainsight 🚀”