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.