Database Deployment Automation — ARM, Bicep, PowerShell & CLI
Automating database deployment ensures consistency, repeatability, and auditability. No manual portal clicks in production — everything as code.
Infrastructure as Code (IaC) Options
| Tool | Type | Best For | Learning Curve |
|---|---|---|---|
| ARM Templates | JSON declarative | Legacy deployments, complex dependencies | High |
| Bicep | DSL (compiles to ARM) | All new Azure deployments | Low-Medium |
| Terraform | HCL declarative | Multi-cloud, team standardization | Medium |
| PowerShell (Az module) | Imperative scripting | Quick tasks, automation runbooks | Low |
| Azure CLI | Imperative CLI | Bash-friendly, CI/CD pipelines | Low |
DP-300 focus: You need to understand ARM/Bicep for declarative deployments, and PowerShell/CLI for imperative operations. Know the difference: declarative = "this is what I want" (ARM/Bicep/Terraform), imperative = "do this step by step" (PowerShell/CLI).
ARM Templates vs Bicep
| Aspect | ARM Template | Bicep |
|---|---|---|
| Format | JSON | DSL (cleaner syntax) |
| Readability | Verbose, nested | Clean, concise |
| Modules | Nested templates | Native module support |
| Parameters | JSON param files | .bicepparam files |
| Intellisense | Limited | Full VS Code support |
| Output | JSON ARM template | Compiles to ARM JSON |
| Microsoft recommendation | Legacy | ✅ Recommended |
Always use Bicep for new deployments. ARM JSON templates are still supported but Bicep is Microsoft's recommended approach. Bicep files are 60-80% shorter than equivalent ARM JSON and have full VS Code IntelliSense.
What Can You Deploy?
| Resource | Bicep/ARM | PowerShell/CLI |
|---|---|---|
| Logical SQL Server | ✅ | ✅ |
| Azure SQL Database | ✅ | ✅ |
| Elastic Pool | ✅ | ✅ |
| Managed Instance | ✅ | ✅ |
| Failover Group | ✅ | ✅ |
| Firewall Rules | ✅ | ✅ |
| Private Endpoint | ✅ | ✅ |
| SQL VM (IaaS) | ✅ | ✅ |
| VNet + NSG | ✅ | ✅ |
| Auditing + ATP | ✅ | ✅ |
Deployment Patterns
Pattern 1: Full Environment (Bicep)
Deploy a complete environment in one command:
- VNet + Subnet + NSG
- Logical SQL Server + Databases
- Firewall rules + Private Endpoint
- Auditing → Log Analytics
- Failover Group (cross-region)
This is the recommended production pattern — everything defined in code, version-controlled, repeatable.
Pattern 2: Database-Level Changes (PowerShell/CLI)
Day-to-day operations after initial deployment:
- Scale vCores up/down
- Change backup retention
- Add/remove databases from elastic pool
- Configure auto-tuning
- Manage firewall rules
Pattern 3: CI/CD Pipeline (Azure DevOps / GitHub Actions)
Automated deployment on every code push:
- Developer commits Bicep/SQL script to Git
- Pipeline validates (what-if / plan)
- Approval gate for production
- Deploy infrastructure changes
- Deploy schema changes (DACPAC/SSDT)
Schema Deployment Tools
| Tool | What It Does | Best For |
|---|---|---|
| SSDT (SQL Server Data Tools) | Visual Studio project for database schema | Schema version control, diff-based deploy |
| DACPAC | Compiled schema package | CI/CD pipeline schema deployment |
| SqlPackage.exe | CLI tool to deploy DACPACs | Automation, cross-platform |
| dbup / Flyway / Liquibase | Migration-based schema changes | Incremental migration scripts |
| EF Core Migrations | ORM-based schema management | .NET application teams |
DACPAC (Data-tier Application Package) is a key DP-300 concept. It contains a database schema definition and can be used to deploy schema changes or extract/import database structures. SqlPackage.exe is the CLI tool for DACPAC operations.
Azure DevOps Pipeline for SQL
A typical pipeline:
| Stage | What Happens |
|---|---|
| Build | Compile SSDT project → produce DACPAC |
| Validate | Run what-if against target DB (dry run) |
| Deploy to Dev | Auto-deploy DACPAC to dev environment |
| Test | Run integration tests against dev DB |
| Deploy to Staging | Deploy to staging with approval gate |
| Deploy to Prod | Manual approval → deploy to production |
Key Deployment Concepts
Idempotency
Declarative deployments (Bicep/ARM) are idempotent — running the same template twice produces the same result without errors. This is critical for reliable automation.
What-If / Preview
Always preview changes before deploying:
- Bicep/ARM:
az deployment group what-if - Terraform:
terraform plan - DACPAC:
SqlPackage /Action:DeployReport
State Management
| Tool | State |
|---|---|
| ARM/Bicep | Azure manages state (resource-level) |
| Terraform | .tfstate file (you manage — use remote backend) |
| PowerShell/CLI | No state (imperative = you track manually) |
Never deploy infrastructure changes from a developer's laptop to production. Always use a CI/CD pipeline with approval gates, audit logging, and rollback capability. Treat database deployments with the same rigor as application deployments.
Anti-Patterns
- "Deploy infra and schema in the same pipeline stage." Couples infra failures to schema rollbacks. Separate stages: infra (Bicep) → schema (DACPAC / migrations) → app (release).
- "DACPAC ‘Drop objects not in source’ enabled in prod." Will silently delete tables that have data but aren't in the project. Disable in prod publish profiles; allow only in dev/test.
- "Run schema migrations from the deploying user's account." Should run as a scoped pipeline identity with
db_ddladminonly. Don't reuse the dev's account. - "Roll back schema by re-running an old DACPAC." Schema is one-way for many changes (dropped columns can't be recovered without backup). Always backup before deploy and test rollback.
- "Single Bicep file = whole production." Monolithic templates hit the deployment depth/size limits. Break into modules + subscription-scope deployments.
- "Trust
--no-prompton every change." DACPAC andazdflags that suppress prompts also suppress useful warnings. Read the pre-deploy report before merging the PR.
azd up runs azd provision + azd deploy — it provisions infra AND publishes app code. For infra-only changes use azd provision. For app-only deploys use azd deploy. Mixing them in CI without intent re-deploys both unnecessarily.
Migration Between Deployment Pipelines
| From → To | Path | Cost |
|---|---|---|
| Manual portal deploy → GitHub Actions | Wire OIDC + what-if on PR | One-time setup; gains audit trail |
| Imperative scripts → Bicep + modules | Refactor; adopt AVM where possible | Declarative; smaller diff per change |
| Bicep → Terraform | Rewrite in HCL; migrate state | Multi-cloud option; state file mgmt |
Hand-written pipelines → azd template | Adopt azd init + azure.yaml | Lower boilerplate; opinionated |
| Schema in app pipeline → separate schema pipeline | Split DACPAC stage; gate behind approval | Clearer ownership; longer release window |
| DACPAC → EF Core migrations | Adopt EF migrations; deprecate DACPAC | Code-driven; less schema drift |
| Direct prod deploy → ring deployment (canary) | Stage → 5 % → 50 % → 100 % | Catches regressions early; pipeline complexity |
| Push deploy → GitOps (Flux/Argo) | Reconciler watches repo → cluster | Self-healing; setup cost |
Most expensive moves: DACPAC → EF migrations (schema philosophy change) and Bicep → Terraform (HCL rewrite + state).
Real Scenarios
- Greenfield SaaS, single team →
azdtemplate + Bicep + GitHub Actions + DACPAC for schema. Driver: lowest scaffold time. Trade-off: opinionated structure. - Enterprise with platform team → product teams → Platform team owns Bicep modules in private registry; product teams consume via
br:references. Driver: standardization + autonomy. Trade-off: registry maintenance. - Multi-region SQL DB schema change → Pipeline runs DACPAC on primary; FOG replicates to secondary. Driver: avoid divergence. Trade-off: replication lag visible during deploy.
- Compliance: every change auditable → Branch protection + reviewer required + signed commits + Bicep
what-ifartifact stored in pipeline. Driver: regulator evidence. Trade-off: PR cycle time. - High-velocity dev, low-blast-radius env → PR → ephemeral environment via
azd up→ auto-delete after merge. Driver: testing in isolation. Trade-off: cost per PR (mitigate with TTL).