Quick answer: You can implement Azure SQL Database CI/CD in Azure DevOps by storing a SQL Database Project in source control, building it into a DACPAC, publishing the DACPAC as a pipeline artifact, and deploying it to each environment with the SqlAzureDacpacDeployment@1 task. Keep environment-specific credentials and approvals outside the database project.

Prerequisites:
1. An Azure DevOps project and Azure Repos repository containing a SQL Database Project.
2. An Azure service connection with permission to deploy to the target Azure resources.
3. An Azure SQL Database for each environment.
4. Azure Key Vault if you want to retrieve environment-specific secrets from the pipeline.

Create a SQL Database Project and Link It to Azure Repos

A SQL Database Project provides a source-controlled representation of your database schema. Build the project to produce a DACPAC, then use that DACPAC as the deployment artifact. Microsoft supports SQL Database Projects and DACPAC-based deployments in Azure DevOps CI/CD pipelines.

Create a DACPAC

A DACPAC contains the database schema and can be produced from a SQL Database Project or extracted from an existing database. Refer to this guide if you need to create a DACPAC from an existing SQL Server database.

Create a database project from a DACPAC

If you are starting with an existing DACPAC, follow our DACPAC in Visual Studio guide to create or open the database project.

Link the database project with Azure Repos

Create an Azure Repos Git repository and commit the SQL Database Project. Keep the database schema and pipeline YAML under source control so changes can be reviewed through pull requests.

Set Up the Azure SQL CI/CD Pipeline

The pipeline below uses separate build and deployment stages. The build creates the DACPAC and publishes it as an artifact. Deployment stages then consume the same artifact for Dev and Test.

Access Azure Key Vault from Azure DevOps

If your deployment uses database connection strings or other secrets, store them in Azure Key Vault instead of committing them to the repository. See Access Key Vault from Azure DevOps Pipeline for the setup.

Create the pipeline

1. Sign in to Azure DevOps and open the project containing your database repository.

2. Select Pipelines > New pipeline.

3. Select Azure Repos Git and choose the repository containing the SQL Database Project.

Create a new Azure DevOps pipeline for an Azure SQL database project

4. Select the repository containing the database project.

Select the Azure SQL database repository in Azure DevOps

5. Select Starter pipeline.

Select the Starter pipeline template in Azure DevOps

6. Replace the starter YAML with a pipeline similar to the following. Adjust the project, service connection, Key Vault, database, and DACPAC paths for your environment.

trigger:
- main

variables:
  vmImageName: 'windows-latest'
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

stages:
- stage: build
  displayName: Build
  jobs:
  - job: Build
    pool:
      vmImage: $(vmImageName)
    steps:
    - task: NuGetToolInstaller@1

    - task: NuGetCommand@2
      inputs:
        restoreSolution: '$(solution)'

    - task: VSBuild@1
      inputs:
        solution: '$(solution)'
        platform: '$(buildPlatform)'
        configuration: '$(buildConfiguration)'

    - task: CopyFiles@2
      inputs:
        SourceFolder: '$(agent.builddirectory)'
        Contents: '**/bin/$(buildConfiguration)/**'
        TargetFolder: '$(build.artifactstagingdirectory)'
        CleanTargetFolder: true

    - task: PublishBuildArtifacts@1
      inputs:
        PathtoPublish: '$(build.artifactstagingdirectory)'
        ArtifactName: 'drop'
        publishLocation: 'Container'

- stage: dev
  displayName: Dev Deploy
  dependsOn: build
  condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))
  jobs:
  - deployment: Deploy
    pool:
      vmImage: $(vmImageName)
    environment: dev
    strategy:
      runOnce:
        deploy:
          steps:
          - task: AzureKeyVault@1
            inputs:
              azureSubscription: 'dev-service-connection-name'
              KeyVaultName: 'dev-keyvault'
              SecretsFilter: 'azuresqldb-dbconnstring'
              RunAsPreJob: true

          - task: SqlAzureDacpacDeployment@1
            inputs:
              azureSubscription: 'dev-service-connection-name'
              AuthenticationType: 'connectionString'
              ConnectionString: '$(azuresqldb-dbconnstring)'
              DeployType: 'DacpacTask'
              DeploymentAction: 'Publish'
              DacpacFile: '$(Pipeline.Workspace)/drop/s/AzureOps.Sql/bin/Release/AzureOps.Sql.dacpac'
              AdditionalArguments: '/p:BlockOnPossibleDataLoss=true'
              IpDetectionMethod: 'AutoDetect'

- stage: test
  displayName: Test Deploy
  dependsOn: dev
  condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))
  jobs:
  - deployment: Deploy
    pool:
      vmImage: $(vmImageName)
    environment: test
    strategy:
      runOnce:
        deploy:
          steps:
          - task: AzureKeyVault@1
            inputs:
              azureSubscription: 'test-service-connection-name'
              KeyVaultName: 'test-keyvault'
              SecretsFilter: 'azuresqldb-dbconnstring'
              RunAsPreJob: true

          - task: SqlAzureDacpacDeployment@1
            inputs:
              azureSubscription: 'test-service-connection-name'
              AuthenticationType: 'connectionString'
              ConnectionString: '$(azuresqldb-dbconnstring)'
              DeployType: 'DacpacTask'
              DeploymentAction: 'Publish'
              DacpacFile: '$(Pipeline.Workspace)/drop/s/AzureOps.Sql/bin/Release/AzureOps.Sql.dacpac'
              AdditionalArguments: '/p:BlockOnPossibleDataLoss=true'
              IpDetectionMethod: 'AutoDetect'

Note:
BlockOnPossibleDataLoss=true prevents the deployment from continuing when DACPAC deployment detects a possible data-loss operation. Parameters such as DropObjectsNotInSource should be enabled only after understanding the impact on the target database.

Review and Test the Pipeline

Run the pipeline manually the first time and verify that the build produces the expected DACPAC and that each deployment stage connects to the intended database.

Run an Azure DevOps database deployment pipeline manually

Production Deployment

For production, use a separate Azure DevOps environment and add approvals/checks appropriate for your release process. The same DACPAC artifact can then be promoted after successful lower-environment deployments rather than rebuilding different database packages for each environment.

Deploying External Tables

The same Azure SQL CI/CD approach can be extended to external tables, external data sources, and database scoped credentials. For a detailed implementation, see Deploy Azure SQL External Tables Using an Azure DevOps CI/CD Pipeline.

Manually Deploy a DACPAC to Azure SQL Database

If you need to deploy the database project manually from Visual Studio, see Deploy DACPAC to Azure SQL Database.

Pro tips:
1. Test DACPAC deployments in lower environments before production.
2. Use Azure DevOps environment approvals/checks for production deployments.
3. Keep secrets out of YAML and source control; use Key Vault or another approved secret-management mechanism.
4. Treat destructive deployment options such as DropObjectsNotInSource with caution.

See more

Kunal Rathi

With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.