Implement a deployment that includes database tasks

In this guide, you’ll learn how to design and execute Azure deployments that include critical database operations. Handling schema changes, data migrations, performance tuning, and backups as part of your CI/CD pipeline ensures data integrity, minimizes downtime, and keeps your application and database versions in sync.

Why Database Tasks Matter in Deployments

Database updates often accompany application code changes—whether modifying table structures, migrating existing data, or tuning performance. Properly sequencing and automating these tasks in your pipeline reduces risk and ensures smooth rollouts.

Balancing Safety and Speed

DevOps strives for fast, reliable releases. When database changes are involved, you must prevent data loss or corruption while keeping downtime to a minimum. Automate every step—from generating migration scripts to running post-deployment checks—to strike the right balance.

Four Key Database Task Types

Plan your deployment by categorizing database work into these four areas:

1. Schema Updates
   Add or modify tables, columns, indexes, and constraints.
2. Data Migration
   Move or transform existing data to fit the new schema.
3. Performance Tuning
   Optimize indexes, queries, or database configurations.
4. Backup and Restoration
   Create and validate backups; prepare rollback procedures.

Azure Tools for Database Deployments

Use these Azure-native tools to streamline database operations:

Real-World Example: SSDT + Azure DevOps

Follow this workflow to add a new table and index to an Azure SQL Database using SSDT and Azure Pipelines:

1. Create or Update SSDT Project
   In Visual Studio, define schema changes (new table, index).
2. Commit to Version Control
   Push your SSDT project to Azure Repos (Git).
3. Configure the CI/CD Pipeline
   Build the solution, generate the DACPAC, and deploy it.
4. Validate Post-Deployment
   Run automated tests or manual queries to confirm success.

Sample Azure Pipelines YAML

trigger:
- main

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildConfiguration: 'Release'
  dacpacPath: '$(Build.ArtifactStagingDirectory)/db/MyDatabase.dacpac'

steps:
- task: NuGetToolInstaller@1
  inputs:
    versionSpec: '5.x'

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

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

- task: PublishBuildArtifacts@1
  inputs:
    pathToPublish: '$(Build.ArtifactStagingDirectory)'
    artifactName: 'db'

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '$(AzureServiceConnection)'
    AuthenticationType: 'servicePrincipal'
    serverName: '$(SqlServerName).database.windows.net'
    databaseName: '$(DatabaseName)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '$(dacpacPath)'
    SqlUsername: '$(SqlUser)'
    SqlPassword: '$(SqlPassword)'

Best Practices for Reliable Deployments

• Automate Everything
  Combine application and database steps in a single pipeline to prevent version drift.
• Use Representative Environments
  Test changes in staging instances that mirror production.
• Validate and Monitor
  Run integration tests post-deployment and monitor performance metrics.
• Have a Rollback Plan
  Keep backups or leverage DACPAC drift detection to revert if needed.

Links and References

• Azure DevOps
• SQL Server Data Tools (SSDT)
• Azure Data Studio
• Entity Framework Migrations