Deploy an Azure SQL Database project using Azure DevOps [CI/CD]
Efficient database management and deployment are essential in today’s dynamic software development landscape. Continuous Integration and Continuous Deployment (CI/CD) with Azure DevOps offers a streamlined solution for managing database changes. In this guide, we’ll show you how to create a SQL Server Database Project ( using scripts), set up an Azure DevOps pipeline, and automate Azure SQL Database deployment. With these practices, you can ensure version control and error-free deployments, making your database development a breeze.
Let’s explore the world of Azure DevOps CI/CD for database management.
Prerequisites
Before you begin, make sure you have:
- Azure DevOps Account: Sign up for an Azure DevOps account.
- Azure SQL Database: Set up an Azure SQL Database on Azure portal
- Visual Studio : Install Visual Studio with SQL Server Data Tools.
- Basic SQL Knowledge: Familiarity with SQL scripting.
With these in place, you’re ready to automate your database deployment with Azure DevOps.
Section 1: Creating the SQL Server Database Project
In the section we will create a new project to add some scripts that we need to run in our database for our application ProjectManagement.Let’s say we will add a ProjectStatus table.
Using Visual Studio create a new project : SQL Server Database Project
In the solution we add a new Post Deployment Script (This file contains SQL statements that will be appended to the build script).
Then we add a SQL Script and we add our script to Create the ProjectStatus table
USE TrainingDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProjectStatus')
BEGIN
CREATE TABLE [dbo].[ProjectStatus](
[ProjectStatusId] [int] IDENTITY(1,1) NOT NULL,
[Label] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProjectStatusId] PRIMARY KEY CLUSTERED
(
[ProjectStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
ELSE
BEGIN
PRINT 'The table "ProjectStatus" already exists.'
END
GOIn Script.PostDeployment.sql file we add this line (It means we need to run the script in ProjectStatusTable.sql)
:r .\ProjectStatusTable.sqlWe can add as much as we need scripts for our database. [Add SQL files then add them in Script.PostDeployment.sql file]
Make sure that the Target platform of the project is Microsoft Azure SQL Database
Build the project and make sure it succeeds 😎
Section 2: Setting up Azure DevOps:
After we finish our scripts in the SQL Server Database project , we add the project to Azure DevOps .
In Visual Studio click “Add to Source Control”. Then create the Git Repository to Azure DevOps as remote.
We check the our Azure DevOps account and verify that the solution exists.
Section 3: Creating the Azure DevOps Build Pipeline:
After pushing the code in our repository in Azure DevOps , we will create a Build Pipeline for our project .In this pipeline , we will save the output which is a database file (DACPAC) file into Azure DevOps artifact locations to be used after in the Release Pipeline.
The pipeline YAML will look like :
# .NET Desktop
# Build and run tests for .NET Desktop or Windows classic desktop solutions.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/apps/windows/dot-net
trigger:
- master
pool:
vmImage: 'windows-latest'
variables:
solution: '**/*.sln'
buildPlatform: 'Any CPU'
buildConfiguration: 'Release'
steps:
- task: NuGetToolInstaller@1
- task: NuGetCommand@2
inputs:
restoreSolution: '$(solution)'
- task: VSBuild@1
inputs:
solution: '$(solution)'
platform: '$(buildPlatform)'
configuration: '$(buildConfiguration)'
- task: VSTest@2
inputs:
platform: '$(buildPlatform)'
configuration: '$(buildConfiguration)'
# Add those tasks to copy and publish the Artifact (.dacpac file)
- task: CopyFiles@2
inputs:
targetFolder: '$(Build.ArtifactStagingDirectory)'
- task: PublishBuildArtifacts@1
displayName: 'Publish Artifact: drop'
inputs:
PathtoPublish: '$(build.artifactstagingdirectory)'Now , we run our pipeline for master branch :
Section 4: Creating the Azure DevOps Release Pipeline
After building the project , it’s time to run it . For our case ,it means we want to run the SQL queries that we have created in our solution.
To do that , we will create a Release Pipeline:
If you don’t have Releases option in your Azure DevOps , Go to organization settings and disable this option “Disable creation of classic release pipelines”
- New release pipeline, then choose “Empty Job” as a template.
- In the Artifacts , we choose “Build” as source type and select our Build pipeline created in the previous section
- For the stage , we add “Azure SQL Database deployment” task and we configure it with our Azure subscription.
Activate “Continuous deployment trigger” option to create a release every time a new build is available.
Now ,our Release pipeline is ready to run and deploy our project [Scripts] to our Azure SQL Server database.
Time to check our database :
Section 5: Testing
Let’s add some data in our table “ProjectStatus” and push the code and let’s see what will happen 🤖.
Going back to our code in Visual Studio , let’s add a new SQL Script :
INSERT INTO ProjectStatus (ProjectStatusId, Label)
VALUES
(1, 'In Progress'),
(2, 'Completed'),
(3, 'On Hold'),
(4, 'Not Started');
GO
:r .\ProjectStatusTable.sql
:r .\ProjectStatusData.sql Then Push the code to Azure DevOps and check the pipelines ⌛️
Conclusion
You’ve reached the end of this tutorial on Azure SQL Database deployment with Azure DevOps CI/CD. You’ve learned to automate and streamline your database development, ensuring version control and error-free deployments. With these newfound skills, you’re well-prepared to enhance your database management processes. Embrace the power of Azure DevOps CI/CD, and say goodbye to manual deployments. Your database development just got a whole lot easier.
Happy coding!
REBHI Zied
