Study guide
10.3 Database DevOps: State-Based & Migration-Based Strategies
Microsoft Certified: Azure DevOps Engineer Expert AZ-400
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Flow of Work, Traceability & Work Tracking
1.1 Flow of Work, GitHub Flow & Feedback Cycles 1.2 Work Tracking with Azure Boards & GitHub Projects 1.3 End-to-End DevOps Traceability & Quality Tracking
2 Chapter 2: DevOps Metrics, Dashboards & Team Collaboration
2.1 DORA Metrics, Flow Metrics & Dashboard Design 2.2 Lifecycle Metrics Across the Six DevOps Phases 2.3 Work Item Queries, WIQL Charts & Analytics 2.4 Project Documentation: Wikis, Mermaid Diagrams & Release Notes 2.5 Webhooks, Service Hooks & Microsoft Teams ChatOps
3 Chapter 3: Git Branching Strategies & Pull Request Workflows
3.1 Git Branching Models & Strategy Design 3.2 Pull Request Workflows & Merge Strategies 3.3 Branch Policies, Quality Gates & Compliance Restrictions
4 Chapter 4: Repository Management, Optimization & Git Data Operations
4.1 Git LFS Architecture & .gitattributes Tracking 4.2 LFS History Migration, git-fat & File Locking 4.3 Git Repository Scaling, Scalar & Monorepo Governance 4.4 Repository Permissions & Git Tag Governance 4.5 Git Data Recovery & Sensitive-Data Scrubbing
5 Chapter 5: Package Management & Dependency Versioning
5.1 Selecting Package Tools: Azure Artifacts vs. GitHub Packages 5.2 Feed Scoping, Upstream Sources & Pipeline Authentication 5.3 Package Views, Immutability & Quality Promotion 5.4 Dependency Versioning: SemVer, CalVer & Automated Versioning 5.5 Pipeline Artifact Versioning & Artifact Storage Strategy
6 Chapter 6: Pipeline Infrastructure, Agents & Integration
6.1 Selecting a CI/CD Solution: Azure Pipelines vs. GitHub Actions 6.2 GitHub-Azure Pipelines Integration & Hybrid Migration 6.3 Hosted vs. Self-Hosted Agents & Runners 6.4 VMSS Elastic Pools, Container Jobs & Agent Governance 6.5 Pipeline Triggers, Filters & Concurrency Management
7 Chapter 7: YAML Pipeline Architecture & Reusable Elements
7.1 YAML Pipeline Anatomy: Stages, Jobs, Steps & Workspaces 7.2 Job Execution Order: DAG Dependencies, Parallelism & Conditions 7.3 Reusable Elements: Templates, Parameters & Variables 7.4 Variable Groups, Secret Variables & Key Vault Linking 7.5 YAML Environments, Checks & Deployment Approvals
8 Chapter 8: Testing Strategy, Quality Gates & Code Coverage
8.1 Test Strategy Design: Local, Unit & Integration Testing 8.2 Load, Stress & Performance Testing in Pipelines 8.3 Test Execution Tasks, Agents & Results Integration 8.4 Code Coverage Analysis & SonarQube Integration 8.5 Quality Gates, Release Gates & Governance Checks
9 Chapter 9: Modern Deployment Strategies & Zero-Downtime Delivery
9.1 Progressive Delivery: Blue-Green, Canary & Ring Deployments 9.2 Minimizing Downtime: Deployment Slots & Rolling Updates 9.3 Feature Flags with Azure App Configuration & Feature Manager 9.4 Designing the High-Priority Hotfix Path 9.5 Ordered Dependency Deployments, Resiliency & Rollback
10 Chapter 10: Artifact Deployment & Database DevOps
10.1 Containerized Application Delivery: ACR, AKS & Container Apps 10.2 Binary, Web App, Function & Scripted Deployments 10.3 Database DevOps: State-Based & Migration-Based Strategies
11 Chapter 11: Infrastructure as Code (IaC) & Azure Deployment Environments
11.1 Infrastructure as Code Strategy: Bicep, ARM & Terraform 11.2 Desired State Configuration & Azure Machine Configuration 11.3 Azure Deployment Environments (ADE) & Developer Self-Service
12 Chapter 12: Pipeline Maintenance, Health & Modernization
12.1 Pipeline Health, Flaky Tests & Reliability Engineering 12.2 Pipeline Optimization: Caching, Slicing & Concurrency 12.3 Artifact & Build Retention Governance 12.4 Modernizing CI/CD: Classic to YAML Migration
13 Chapter 13: Authentication, Authorization & Identity Governance
13.1 Microsoft Entra Service Principals & Managed Identities 13.2 Service Connections, Workload Identity & Personal Access Tokens 13.3 Azure DevOps Projects, Teams & Permission Evaluation 13.4 Access Levels, GitHub Roles & Outside Collaborators
14 Chapter 14: Secrets Management, Sensitive Data & DevSecOps Scanning
14.1 Azure Key Vault, Secret Variables & Secretless CI/CD 14.2 Security Scanning Strategy, GHAS & Secret Scanning 14.3 CodeQL SAST in Pipelines and Containers 14.4 Software Supply Chain, Container Scanning & Cloud Defender
15 Chapter 15: Telemetry Collection, Azure Monitor & Alerting
15.1 Azure Monitor Architecture, AMA & Data Collection Rules 15.2 Application Insights & Specialized Azure Monitor Insights 15.3 Alerting Rules, Action Groups & GitHub Repository Insights
16 Chapter 16: Telemetry Analysis, Distributed Tracing & SRE Practices
16.1 Distributed Tracing, Application Map & Exception Triage 16.2 KQL Log Analytics, SRE Observability & Error Budgets
10.3 Database DevOps: State-Based & Migration-Based Strategies
Key Takeaways Explain This Topic AI
Database DevOps bridges the critical gap between ephemeral, stateless application code and persistent, stateful data stores where destructive operations can cause permanent data loss.
State-based deployments (SSDT / DACPAC) maintain a declarative model of the desired target state in source control and rely on deployment engines (DacServices) to calculate differential delta scripts at release time.
Migration-based deployments (Flyway, Liquibase, EF Core) utilize immutable, sequentially ordered transition scripts executed strictly once per environment and tracked via a schema history table.
Zero-downtime database evolution mandates the Expand/Contract (Parallel Run) pattern, decoupling schema modifications across multiple phased deployments to maintain continuous backward compatibility.
Automated database rollbacks are inherently dangerous and often catastrophic; teams must prioritize backward-compatible schema design and forward-fixing migrations over blind schema restoration.
Last updated: September 2026
10.3 Database DevOps: State-Based & Migration-Based Strategies
Continuous delivery for application code is a well-established discipline: if a newly deployed container or binary fails, the environment can be rolled back or recreated instantly because application tiers are stateless. Databases, however, represent persistent state. A flawed database deployment cannot simply be overwritten without risking data corruption, catastrophic table locks, or irrevocable data loss.
Integrating the database into CI/CD pipelines—often termed Database DevOps —requires a deep understanding of deployment philosophies, zero-downtime schema evolution patterns, and automated drift detection. On the AZ-400 exam, candidates must master the trade-offs between state-based (declarative) and migration-based (imperative) database delivery, configure DACPAC pipelines with safety gates, implement the Expand/Contract (Parallel Run) zero-downtime pattern, and handle production database rollback scenarios safely.
1. The Database DevOps Challenge
Why is database continuous delivery fundamentally harder than deploying web services or APIs?
Persistence vs. Ephemerality : Application containers can be terminated and replaced in milliseconds. Databases store irreplaceable business transactions; dropping a column or modifying a data type permanently alters state.
Schema Drift : In many organizations, emergency hotfixes, database administrators (DBAs) tuning indexes, or reporting teams creating views execute ad-hoc SQL directly against production. This creates schema drift , where production diverges from source control.
Concurrence & Availability : Altering a table containing hundreds of millions of rows (such as adding a non-null column without a default value) can trigger exclusive table-level locks ( X-locks ), stalling incoming user queries and generating cascading HTTP 504 gateway timeouts.
Rollback Complexity : You cannot rollback a database by restoring yesterday's backup without throwing away all financial transactions, customer registrations, and orders processed between the backup and the failure event.
2. State-Based Deployments (The Declarative Model)
In a state-based (declarative) deployment model, source control contains the definition of what the database should look like at the target state. Developers do not write explicit ALTER TABLE scripts; instead, they maintain declarative object scripts (e.g., CREATE TABLE Customers (...) ). [Source Control Git Repo] ──────► [MSBuild / dotnet build] ──────► [Compiled .dacpac Artifact]
- (Declarative SQL Schemas) (Desired Target Model)
- │
- ▼
- [SqlPackage / DacServices Engine]
- │
- ┌───────────────────────────────────────┴───────────────────────────────────────┐
- ▼ ▼
- [Inspect Target Database] [Generate Delta Script]
- Read Live Schema Catalog • Compare Target vs. Desired
- Detect Out-of-Band Schema Drift • Check /p:BlockOnPossibleDataLoss
- │ │
- └───────────────────────────────────────┬───────────────────────────────────────┘
- ▼
- [Execute Differential Migration]
- (Morph Target Schema to Desired State)
- Tooling & Architecture: SSDT and DACPAC
The dominant state-based ecosystem in Microsoft Azure is SQL Server Data Tools (SSDT) and Data-Tier Application Packages (DACPAC) :
SQL Project ( .sqlproj ) : A Visual Studio project containing individual .sql files for every table, view, stored procedure, and constraint in the database.
Compilation : During the CI build, the project compiles into a single binary artifact with a .dacpac extension. The DACPAC contains an XML model of the entire database schema.
Deployment Engine ( SqlPackage.exe / DacServices ) : During the CD release pipeline, SqlPackage connects to the target Azure SQL Database, extracts its current schema, compares it against the DACPAC model, and programmatically generates an ephemeral differential SQL script ( ALTER TABLE , CREATE PROCEDURE ) required to morph the target database into the desired state.
Drift Detection & Guardrails
Drift Detection : Because SqlPackage reads the live catalog of the target database before executing changes, it detects if columns or indexes were added out-of-band. It can reconcile drift or abort the deployment based on configured parameters.
Blocking Data Loss : One of the most important flags on the SqlAzureDacpacDeployment@1 pipeline task is /p:BlockOnPossibleDataLoss=True . If the calculated delta script includes operations that would drop a table, truncate data, or shorten a column data type, SqlPackage terminates the pipeline immediately, protecting production data.
Pre-Deployment & Post-Deployment Scripts : SSDT allows embedding procedural SQL scripts executed immediately before and after the declarative schema synchronization. These scripts handle custom data migrations, seeding lookup tables, or moving data between columns. # Deploying an Azure SQL DACPAC via Azure Pipelines
- task: SqlAzureDacpacDeployment@1
- displayName: 'Deploy Production DACPAC'
inputs:
- azureSubscription: 'ContosoARMConnection'
- AuthenticationType: 'servicePrincipal'
- ServerName: 'sql-contoso-prod.database.windows.net'
- DatabaseName: 'CommerceDB'
- DacpacFile: '$(Pipeline.Workspace)/drop/CommerceDB.dacpac'
- DeployType: 'DacpacTask'
- DeploymentAction: 'Publish'
- AdditionalArguments: '/p:BlockOnPossibleDataLoss=True /p:DropObjectsNotInSource=False'
- 3. Migration-Based Deployments (The Imperative Model)
In a migration-based (imperative) deployment model, source control contains the explicit sequence of historical changes required to transition the database from Version N to Version N+1. Developers write sequential SQL scripts detailing exact ALTER , CREATE , and INSERT commands.
Tooling & Architecture: Flyway, Liquibase, and EF Core
Popular migration frameworks include Flyway , Liquibase , and Entity Framework Core Migrations ( dotnet ef database update ). [Repository Migrations Directory]
- ├── V1.0__initial_schema.sql
- ├── V1.1__add_customer_loyalty.sql
- ├── V1.2__create_orders_index.sql
- └── R__refresh_sales_view.sql
- The Schema History Table & Checksum Verification
Migration tools maintain a dedicated tracking table inside the target database—such as flyway_schema_history or DATABASECHANGELOG :
Version Tracking : When the deployment pipeline executes, the migration engine inspects the history table to determine which migration scripts have already been applied.
Cryptographic Checksums : When a script is executed, the engine calculates a SHA-256 hash (checksum) of the script's contents and stores it in the history table.
Tamper Detection : Before applying new scripts, the engine recalculates checksums for all previously applied migrations. If someone edited an existing, historical script (e.g., modifying V1.0__initial_schema.sql after it ran in staging), the engine throws a Checksum Validation Error and halts the deployment. Historical migration scripts are strictly immutable.
Repeatable Migrations : In addition to versioned migrations ( V... ), frameworks support repeatable migrations ( R... ). Repeatable migrations execute once during initial rollout and re-execute automatically whenever their script contents (checksum) change. They are ideal for stored procedures, views, and functions.
4. State-Based vs. Migration-Based Comparison Matrix
Architectural Vector State-Based (SSDT / DACPAC) Migration-Based (Flyway / Liquibase)
Mental Model Declarative : 'What the database should look like' Imperative : 'How to transition from V(N) to V(N+1)'
- Artifact in Git Single file per object ( Customers.sql ) Immutable sequence of scripts ( V1.1__add_col.sql )
- Delta Generation Automated by tool ( SqlPackage ) at deploy time Manually authored by developer prior to PR
Drift Handling Detects drift and synchronizes or aborts Blind to out-of-band changes (runs only pending scripts)
Complex Data Migration Difficult; requires complex pre/post-deploy scripts Excellent : Full procedural SQL control per version
- Tool Ecosystem SQL Server, Azure SQL, Visual Studio SSDT Universal (PostgreSQL, MySQL, Oracle, Azure SQL)
- Safety Check /p:BlockOnPossibleDataLoss=True guardrail Checksum hashing & transaction wrap per script
- 5. Zero-Downtime Database Deployments: The Expand/Contract Pattern
In modern progressive delivery (Blue-Green deployments, deployment slots, canary rollouts), two different versions of application code access the database concurrently during the release window. If Version 2.0 requires dropping a column, renaming a field, or splitting a table, applying that database change before or during the cutover instantly crashes running Version 1.0 instances!
To achieve true zero downtime, database teams implement the Expand/Contract (Parallel Run) pattern, which decomposes a breaking schema change across multiple, backward-compatible deployment stages. [Phase 1: EXPAND] ──────► [Phase 2: DUAL WRITE] ────► [Phase 3: BACKFILL] ───► [Phase 4: READ SWITCH] ─► [Phase 5: CONTRACT]
- Add new nullable • Deploy App v1.1 • Asynchronous • Deploy App v2.0 • Deploy DB Cleanup
- column or new table • Writes to Old & New background worker • Reads & Writes New • Drop old column
- Old app v1.0 unaffected • Reads from Old syncs historical rows • Decommission App v1.1 • Enforce NOT NULL
- Step-by-Step Anatomy of Expand/Contract
Consider an enterprise requirement: renaming the column CustomerPhone to MobileNumber in the Customers table.
Phase 1: Expand (Database Deployment) :
Add the new column MobileNumber as nullable . Never add a non-null column without a default value!
Do NOT drop CustomerPhone .
Result : Version 1.0 of the application continues running without disruption because CustomerPhone is untouched.
Phase 2: Dual Write (Application Deployment - Version 1.1) :
Deploy application version 1.1. When customers update their profile or register, the application reads from CustomerPhone but writes identical data to both CustomerPhone and MobileNumber .
Result : Incoming transactions populate the new column while preserving compatibility with any running v1.0 instances.
Phase 3: Backfill (Background Data Migration) :
Execute an asynchronous, batched SQL background script to copy data from CustomerPhone to MobileNumber for all historical rows where MobileNumber IS NULL .
Running the backfill in small batches (e.g., 5,000 rows at a time) avoids locking the table.
Phase 4: Read Switch (Application Deployment - Version 2.0) :
Deploy application version 2.0. The application now reads and writes exclusively to MobileNumber .
The deployment slot swap or canary rollout completes, and version 1.1 instances are retired.
Phase 5: Contract (Database Deployment) :
Once production telemetry confirms version 2.0 is completely stable, deploy a final database migration that drops the deprecated CustomerPhone column and applies a NOT NULL constraint to MobileNumber .
6. Handling Rollbacks in Database DevOps
A critical question on the AZ-400 exam is: How do we roll back a failed database deployment?
The Fatal Fallacy of Automated Database Rollbacks
Many migration tools support DOWN scripts designed to undo an UP migration (e.g., DROP COLUMN MobileNumber ). In production environments, running automated down scripts is disastrous:
If Version 2.0 was live for even 10 minutes, hundreds of new customer orders were written to MobileNumber .
Executing a down script that drops MobileNumber permanently obliterates live customer data collected during those 10 minutes!
Restoring a full database backup from prior to the deployment results in data loss for all unaffected tables.
The Roll-Forward (Fix-Forward) Strategy
Enterprise DevOps teams enforce a Roll-Forward discipline:
Design All Changes to be Backward-Compatible : By using the Expand/Contract pattern, database changes never break older application code.
Application Rollback Safety : If the new application version contains bugs, the platform team immediately rolls back the application code (swapping back deployment slots or updating traffic weights). Because the database was only expanded, the old application code continues to operate seamlessly against the expanded schema.
Fix-Forward Migrations : When a database issue occurs, engineers author a new, forward-moving migration script (e.g., V1.3__repair_index.sql ) and push it through the standard CI/CD pipeline rather than executing destructive rollback scripts.
- 7. Realistic Exam Scenario & Common Traps
- Scenario: High-Volume Banking Portal Database Modernization
Organization : First National Trust operates an Azure SQL Database serving millions of online banking customers. The team releases code weekly using Azure App Service deployment slots.
Problem : During a recent release, a developer added an ALTER TABLE Accounts ALTER COLUMN AccountNumber VARCHAR(30) NOT NULL migration directly to production. The migration took 45 minutes to acquire an exclusive lock, causing thousands of checkout failures. Furthermore, when the slot swap failed and the team swapped back to the previous slot, the old application crashed immediately because it could not handle the modified column.
Requirement : Establish a Database DevOps pipeline ensuring: (1) schema changes are declarative and validated against accidental data loss, (2) out-of-band schema drift is automatically detected, and (3) all future schema modifications guarantee continuous backward compatibility during App Service slot swaps.
DevOps Solution :
Convert the database into a SQL Server Database Project ( .sqlproj ) managed in Azure Repos Git.
In Azure Pipelines, build the project into a DACPAC and deploy using SqlAzureDacpacDeployment@1 with /p:BlockOnPossibleDataLoss=True .
Mandate the Expand/Contract pattern for all schema modifications: new fields must be added as nullable in release N, populated via dual writes and backfills in release N+1, and old fields contracted in release N+2, guaranteeing seamless slot swaps.
Common Exam Traps to Avoid
Trap: Assuming DACPAC automatically handles column renames without data loss. By default, if you rename a column in SSDT from OldCol to NewCol , SqlPackage interprets this as 'DROP OldCol' and 'ADD NewCol', leading to complete data loss! To rename a column in SSDT without data loss, engineers must use the RefactorLog ( .refactorlog ) or execute a pre-deployment migration script.
Trap: Recommending automated DOWN migration scripts for production rollbacks. Down scripts that drop tables or columns destroy all transactional data written while the new version was active. The correct enterprise answer is always backward-compatible schema design and rolling forward .
Trap: Running schema changes during slot swap warmup. Schema modifications should precede application deployment or follow the Expand phase. Modifying a schema during a slot swap without backward compatibility breaks the active production slot.