10.3 Database DevOps: State-Based & Migration-Based Strategies

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):

1. SQL Project (.sqlproj): A Visual Studio project containing individual .sql files for every table, view, stored procedure, and constraint in the database.
2. 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.
3. 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:

1. Version Tracking: When the deployment pipeline executes, the migration engine inspects the history table to determine which migration scripts have already been applied.
2. 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.
3. 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.
4. 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

---

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.

1. 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.
2. 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.
3. 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.
4. 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.
5. 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:

1. Design All Changes to be Backward-Compatible: By using the Expand/Contract pattern, database changes never break older application code.
2. 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.
3. 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:

1. Convert the database into a SQL Server Database Project (.sqlproj) managed in Azure Repos Git.
2. In Azure Pipelines, build the project into a DACPAC and deploy using SqlAzureDacpacDeployment@1 with /p:BlockOnPossibleDataLoss=True.
3. 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.