Introduction
This post will be a union of multiple topics. It is part of the SQL CI/CD series and as such will build upon Deploying .dacpacs to Multiple Environments via ADO Pipelines | Microsoft Community Hub and Managed SQL Deployments Like Terraform | Microsoft Community Hub while also crossing over with the YAML Pipeline series
This is an advanced topic in regard to both Azure DevOps YAML and SQL CI/CD. If both of these concepts are newer to you, please refer to the links above as this is not designed to be a beginner's approach in either one of these domains.
Assumptions
To get the most out of this and follow along we are going to assume that you are 1.) On board with templating your Azure DevOps YAML Pipelines. By doing this we will see the benefit of quickly onboarding new pipelines, standardizing our deployment steps, and increasing our security.
We also are going to assume you are on board with Managed SQL Deployments Like Terraform | Microsoft Community Hub for deploying your SQL Projects. By adopting this we can increase our data security, confidence in source control, and speed our time to deployment.
For this post we will continue to leverage the example cicd-adventureWorks repository for the source of our SQL Project and where the pipeline definition will live.
Road mapping the Templates
Just like my other YAML posts let's outline the pieces required in this stage and we will then break down each job
- Build Stage
- Build .dacpac job
- run `dotnet build` and pass in appropriate arguments
- execute a Deploy Report from the dacpac produced by the build and the target environment
- copy the Deploy Report to the build output directory
- publish the pipeline artifact
- Build .dacpac job
- Deploy Stage
- Deploy .dacpac job
- run Deploy Report from dacpac artifact (optional)
- deploy dacpac, including pre/postscripts
- Deploy .dacpac job
Build Stage
For the purposes of this stage, we should think of building our .dacpac similar to a terraform or single page application build. What I am referring to is we will produce an artifact per environment, and this will be generated from the same codebase. Additionally, we will run a 'plan' which will be the proposed result of deploying our dacpac file.
Build Job
We will have one instance of the build job for each environment. Each instance will produce a different artifact as they will be passing different build configurations which in turn will result in a different .dacpac per environment.
If you are familiar with YAML templating, then feel free to jump to the finish job template.
One of the key differences with this job structure, as opposed to the one outlined in Deploying .dacpacs to Multiple Environments via ADO Pipelines is the need for a Deploy Report. This is the key to unlocking the CI/CD approach which aligns with Terraform. This Deploy Report detects our changes on build, similar to running a terraform plan. Creating a Deploy Report is achieved by setting the DeployAction attribute in the SQLAzureDacpacDeployment@1 action to 'DeployReport'
Now there is one minor "bug" in the Microsoft SQLAzureDacpacDeployment task, which I have raised with the ADO task. It appears the output path for the Deploy Report as well as the Drift Report are hardcoded to the same location. To get around this I had to find out where the Deploy Report was being published and, for our purposes, have a task to copy the Deploy Report to the same location as the .dacpac and then publish them both as a single folder.
Here is the code for the for a single environment to build the associated .dacpac and produce the Deploy Repo
stages:
- stage: adventureworksentra_build
variables:
- name: solutionPath
value: $(Build.SourcesDirectory)//
jobs:
- job: build_publish_sql_sqlmoveme_dev_dev
steps:
- task: UseDotNet@2
displayName: Use .NET SDK vlatest
inputs:
packageType: 'sdk'
version: ''
includePreviewVersions: true
- task: NuGetAuthenticate@1
displayName: 'NuGet Authenticate'
- task: DotNetCoreCLI@2
displayName: dotnet build
inputs:
command: build
projects: $(Build.SourcesDirectory)/src/sqlmoveme/*.sqlproj
arguments: --configuration dev /p:NetCoreBuild=true /p:DacVersion=1.0.1
- task: SqlAzureDacpacDeployment@1
displayName: DeployReport sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net
inputs:
DeploymentAction: DeployReport
azureSubscription: AzureDevServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-dev-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\s/src/sqlmoveme/bin/dev/sqlmoveme.dacpac
AdditionalArguments: ''
DeleteFirewallRule: True
- task: CopyFiles@2
inputs:
SourceFolder: GeneratedOutputFiles
Contents: '**'
TargetFolder: $(Build.SourcesDirectory)/src/sqlmoveme/bin/dev/cus
- task: PublishPipelineArtifact@1
displayName: 'Publish Pipeline Artifact sqlmoveme_dev_dev '
inputs:
targetPath: $(Build.SourcesDirectory)/src/sqlmoveme/bin/dev
artifact: sqlmoveme_dev_dev
properties: ''
The end result will be similar to: (I have two environments in the screenshot below)
One can see I have configured this to run a Deploy Report across each regional instance, thus the `cus` folder, of a SQL DB I do this is to identify and catch any potential schema and data issues.
The Deploy Reports are the keys to tie this to the thought of deploying and managing SQL Databases like Terraform. These reports will execute when a pull request is created as part of the Build and again at Deployment to ensure changes from PR to deployment that may have occurred.
For the purposes of this blog here is a deployment report indicating a schema change:
This is an important artifact for organizations whose auditing policy requires documentation around deployments. This information is also available in the ADO job logs:
This experience should feel similar to Terraform CI/CD...THAT'S A GOOD THING! It means we are working on developing and refining practices and principals across our tech stacks when it comes to SDLC. If this feels new to you then please read Terraform, CI/CD, Azure DevOps, and YAML Templates - John Folberth
Deploy Stage
We will have a deploy stage for each environment and within that stage will be a job for each region and/or database we are deploying our dacpac to. This job can be a template because, in theory, our deploying process across environments is identical.
We will run a deployment report and deploy the .dacpac which was built for the specific environment and will include any and all associated pre/post scripts. Again this process has already been walked through in Deploying .dacpacs to Multiple Environments via ADO Pipelines | Microsoft Community Hub
Deploy Job
The deploy job will take what we built in the deployment process in Deploying .dacpacs to Multiple Environments via ADO Pipelines | Microsoft Community Hub and we will add a perquisite job to create a second Deployment Report. This process is to ensure we are aware of any changes in the deployed SQL Database that may have occurred after the original dacpac and Deployment Report were created at the time of the Pull Request.
By doing this we now have a tight log identifying any changes that were being made right before we deployed the code. Next, we need to make a few changes to override the default arguments of the .dacpac publish command in order to automatically deploy changes that may result in data loss.
Here is a complete list of all the available properties SqlPackage Publish - SQL Server | Microsoft Learn. The ones we are most interested in are DropObjectsNotInSource and BlockOnPossibleDataLoss.
DropObjectsNotInSource is defined as:
Specifies whether objects that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. This value takes precedence over DropExtendedProperties.
This is important as it will drop and delete objects that are not defined in our source code. As I've written about previously this will drop all those instances of "Shadow Data" or copies of tables we were storing.
This value, by default, is set to false as a safeguard from a destructive data action. Our intention though is to ensure our deployed database objects match our definitions in source control, as such we want to enable this.
BlockOnPossibleDataLoss is defined as:
Specifies that the operation will be terminated during the schema validation step if the resulting schema changes could incur a loss of data, including due to data precision reduction or a data type change that requires a cast operation. The default (True) value causes the operation to terminate regardless if the target database contains data. An execution with a False value for BlockOnPossibleDataLoss can still fail during deployment plan execution if data is present on the target that cannot be converted to the new column type.
This is another safeguard that has been put in place to ensure data isn't lost in the situation of type conversion or schema changes such as dropping a column. We want this set to `true` so that our deployment will actually deploy in an automated fashion. If this is set to `false` and we are wanting to update schemas/columns then we would be creating an anti-pattern of a manual deployment to accommodate.
When possible, we want to automate our deployments and in this specific case we have already taken the steps of mitigating unintentional data loss through our implementation of a Deploy Report. Again, we should have confidence in our deployment and if we have this then we should be able to automate it.
Here is that same deployment process, including now the Deploy Report steps:
- stage: adventureworksentra_dev_cus_dacpac_deploy jobs: - deployment: adventureworksentra_app_dev_cus environment: name: dev dependsOn: [] strategy: runOnce: deploy: steps: - task: SqlAzureDacpacDeployment@1 displayName: DeployReport sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net inputs: DeploymentAction: DeployReport azureSubscription: AzureDevServiceConnection AuthenticationType: servicePrincipal ServerName: sql-adventureworksentra-dev-cus.database.windows.net DatabaseName: sqlmoveme deployType: DacpacTask DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_dev_dev\**\*.dacpac AdditionalArguments: '' DeleteFirewallRule: False - task: CopyFiles@2 inputs: SourceFolder: GeneratedOutputFiles Contents: '**' TargetFolder: postDeploy/sql-adventureworksentra-dev-cus.database.windows.net/sqlmoveme - task: SqlAzureDacpacDeployment@1 displayName: Publish sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net inputs: DeploymentAction: Publish azureSubscription: AzureDevServiceConnection AuthenticationType: servicePrincipal ServerName: sql-adventureworksentra-dev-cus.database.windows.net DatabaseName: sqlmoveme deployType: DacpacTask DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_dev_dev\**\*.dacpac AdditionalArguments: /p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false DeleteFirewallRule: True
Putting it Together
Let's put together all these pieces. This example will show an expanded pipeline that has the following stages and jobs
- Build a stage
- Build Dev job
- Build Tst job
- Deploy Dev stage
- Deploy Dev Job
- Deploy tst stage
- Deploy tst Job
And here is the code:
resources:
repositories:
- repository: templates
type: github
name: JFolberth/TheYAMLPipelineOne
endpoint: JFolberth
trigger:
branches:
include:
- none
pool:
vmImage: 'windows-latest'
parameters:
- name: projectNamesConfigurations
type: object
default:
- projectName: 'sqlmoveme'
environmentName: 'dev'
regionAbrvs:
- 'cus'
projectExtension: '.sqlproj'
buildArguments: '/p:NetCoreBuild=true /p:DacVersion=1.0.1'
sqlServerName: 'adventureworksentra'
sqlDatabaseName: 'moveme'
resourceGroupName: adventureworksentra
ipDetectionMethod: 'AutoDetect'
deployType: 'DacpacTask'
authenticationType: 'servicePrincipal'
buildConfiguration: 'dev'
dacpacAdditionalArguments: '/p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false'
- projectName: 'sqlmoveme'
environmentName: 'tst'
regionAbrvs:
- 'cus'
projectExtension: '.sqlproj'
buildArguments: '/p:NetCoreBuild=true /p:DacVersion=1.0'
sqlServerName: 'adventureworksentra'
sqlDatabaseName: 'moveme'
resourceGroupName: adventureworksentra
ipDetectionMethod: 'AutoDetect'
deployType: 'DacpacTask'
authenticationType: 'servicePrincipal'
buildConfiguration: 'tst'
dacpacAdditionalArguments: '/p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false'
- name: serviceName
type: string
default: 'adventureworksentra'
stages:
- stage: adventureworksentra_build
variables:
- name: solutionPath
value: $(Build.SourcesDirectory)//
jobs:
- job: build_publish_sql_sqlmoveme_dev_dev
steps:
- task: UseDotNet@2
displayName: Use .NET SDK vlatest
inputs:
packageType: 'sdk'
version: ''
includePreviewVersions: true
- task: NuGetAuthenticate@1
displayName: 'NuGet Authenticate'
- task: DotNetCoreCLI@2
displayName: dotnet build
inputs:
command: build
projects: $(Build.SourcesDirectory)/src/sqlmoveme/*.sqlproj
arguments: --configuration dev /p:NetCoreBuild=true /p:DacVersion=1.0.1
- task: SqlAzureDacpacDeployment@1
displayName: DeployReport sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net
inputs:
DeploymentAction: DeployReport
azureSubscription: AzureDevServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-dev-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\s/src/sqlmoveme/bin/dev/sqlmoveme.dacpac
AdditionalArguments: ''
DeleteFirewallRule: True
- task: CopyFiles@2
inputs:
SourceFolder: GeneratedOutputFiles
Contents: '**'
TargetFolder: $(Build.SourcesDirectory)/src/sqlmoveme/bin/dev/cus
- task: PublishPipelineArtifact@1
displayName: 'Publish Pipeline Artifact sqlmoveme_dev_dev '
inputs:
targetPath: $(Build.SourcesDirectory)/src/sqlmoveme/bin/dev
artifact: sqlmoveme_dev_dev
properties: ''
- job: build_publish_sql_sqlmoveme_tst_tst
steps:
- task: UseDotNet@2
displayName: Use .NET SDK vlatest
inputs:
packageType: 'sdk'
version: ''
includePreviewVersions: true
- task: NuGetAuthenticate@1
displayName: 'NuGet Authenticate'
- task: DotNetCoreCLI@2
displayName: dotnet build
inputs:
command: build
projects: $(Build.SourcesDirectory)/src/sqlmoveme/*.sqlproj
arguments: --configuration tst /p:NetCoreBuild=true /p:DacVersion=1.0
- task: SqlAzureDacpacDeployment@1
displayName: DeployReport sqlmoveme on sql-adventureworksentra-tst-cus.database.windows.net
inputs:
DeploymentAction: DeployReport
azureSubscription: AzureTstServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-tst-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\s/src/sqlmoveme/bin/tst/sqlmoveme.dacpac
AdditionalArguments: ''
DeleteFirewallRule: True
- task: CopyFiles@2
inputs:
SourceFolder: GeneratedOutputFiles
Contents: '**'
TargetFolder: $(Build.SourcesDirectory)/src/sqlmoveme/bin/tst/cus
- task: PublishPipelineArtifact@1
displayName: 'Publish Pipeline Artifact sqlmoveme_tst_tst '
inputs:
targetPath: $(Build.SourcesDirectory)/src/sqlmoveme/bin/tst
artifact: sqlmoveme_tst_tst
properties: ''
- stage: adventureworksentra_dev_cus_dacpac_deploy
jobs:
- deployment: adventureworksentra_app_dev_cus
environment:
name: dev
dependsOn: []
strategy:
runOnce:
deploy:
steps:
- task: SqlAzureDacpacDeployment@1
displayName: DeployReport sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net
inputs:
DeploymentAction: DeployReport
azureSubscription: AzureDevServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-dev-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_dev_dev\**\*.dacpac
AdditionalArguments: ''
DeleteFirewallRule: False
- task: CopyFiles@2
inputs:
SourceFolder: GeneratedOutputFiles
Contents: '**'
TargetFolder: postDeploy/sql-adventureworksentra-dev-cus.database.windows.net/sqlmoveme
- task: SqlAzureDacpacDeployment@1
displayName: Publish sqlmoveme on sql-adventureworksentra-dev-cus.database.windows.net
inputs:
DeploymentAction: Publish
azureSubscription: AzureDevServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-dev-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_dev_dev\**\*.dacpac
AdditionalArguments: /p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false
DeleteFirewallRule: True
- stage: adventureworksentra_tst_cus_dacpac_deploy
jobs:
- deployment: adventureworksentra_app_tst_cus
environment:
name: tst
dependsOn: []
strategy:
runOnce:
deploy:
steps:
- task: SqlAzureDacpacDeployment@1
displayName: DeployReport sqlmoveme on sql-adventureworksentra-tst-cus.database.windows.net
inputs:
DeploymentAction: DeployReport
azureSubscription: AzureTstServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-tst-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_tst_tst\**\*.dacpac
AdditionalArguments: ''
DeleteFirewallRule: False
- task: CopyFiles@2
inputs:
SourceFolder: GeneratedOutputFiles
Contents: '**'
TargetFolder: postDeploy/sql-adventureworksentra-tst-cus.database.windows.net/sqlmoveme
- task: SqlAzureDacpacDeployment@1
displayName: Publish sqlmoveme on sql-adventureworksentra-tst-cus.database.windows.net
inputs:
DeploymentAction: Publish
azureSubscription: AzureTstServiceConnection
AuthenticationType: servicePrincipal
ServerName: sql-adventureworksentra-tst-cus.database.windows.net
DatabaseName: sqlmoveme
deployType: DacpacTask
DacpacFile: $(Agent.BuildDirectory)\sqlmoveme_tst_tst\**\*.dacpac
AdditionalArguments: /p:DropObjectsNotInSource=true /p:BlockOnPossibleDataLoss=false
DeleteFirewallRule: True
In ADO it will look like:
We can see the important Deploy Report being created and can confirm that there are Deploy Reports for each environment/region combination:
Conclusion
With the inclusion of deploy reports we now have the ability to create Azure SQL Deployments that adhere to modern DevOps approaches. We can ensure our environments will be in sync with how we have defined them in source control. By doing this we achieve a higher level of security, confidence in our code, and reduction in shadow data.
To learn more on these approaches with SQL Deployments be sure to check out my other blog articles on the topic "SQL Database Series" in "Healthcare and Life Sciences Blog" | Microsoft Community Hub and be sure to follow me on LinkedIn
Updated Mar 04, 2025
Version 1.0j_folberth
Microsoft
Joined August 11, 2022
Healthcare and Life Sciences Blog
Follow this blog board to get notified when there's new activity