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
• Deploy Stage
  • Deploy .dacpac job 
    • run Deploy Report from dacpac artifact (optional) 
    • deploy dacpac, including pre/postscripts

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