Database Deployment Automation with Azure CLI & PowerShell — Deep Dive

The April 24, 2026 DP-300 outline split out two automation skills under Domain 4 that aren't covered by ARM/Bicep alone:

"Deploy database resources by using Azure PowerShell"
"Deploy database resources by using Azure CLI"
"Monitor and troubleshoot a deployment"

This page focuses on the imperative + declarative deployment patterns with Azure CLI and Azure PowerShell, deployment monitoring, and how to triage failed deployments.

When to use what

Tool	Strength	Typical use
Azure CLI (az ...)	Cross-platform (Linux/macOS/Windows), bash-friendly, JMESPath queries	CI/CD pipelines on Linux runners, ad-hoc shell scripts, learning paths
Azure PowerShell (Az.* modules)	Object pipeline, type safety, deep Windows/SQL integration	Automation runbooks, Windows admins, script libraries that pipe results
ARM template / Bicep	Declarative, idempotent, what-if support, rollback	Repeatable infra deployments, source-controlled IaC
Terraform	Multi-cloud, state file, module ecosystem	Multi-cloud orgs already standardized on Terraform
DACPAC / SqlPackage	Schema-as-code for the contents of a database	App database schema deployments inside a CI pipeline

🎯 Exam Focus

The exam differentiates resource deployment (creating the SQL DB, MI, server, firewall) from schema deployment (creating tables/procs inside a DB). For the former → CLI / PowerShell / ARM / Bicep. For the latter → DACPAC, SqlPackage, or T-SQL. :::

Part 1 — Azure CLI

Authenticate

# Interactive (opens browser; default for human use)
az login --tenant <your-tenant-id>

# Service principal (for CI/CD)
az login --service-principal -u <appId> -p <secret> --tenant <tenantId>

# Managed identity (when running on an Azure VM / Cloud Shell / GitHub Actions OIDC)
az login --identity

# Pick the active subscription
az account set --subscription "<your-subscription-name>"

Deploy an Azure SQL Server + Database (single command sequence)

RG=rg-sqldb-prod
LOC=westeurope
SRV=ehab-sqlsrv01
DB=salesprod
ADMIN=sqladminuser
PWD='V3ry$trongP@ss!'

# Resource group
az group create -n $RG -l $LOC

# Logical SQL server with Entra-only authentication
az sql server create \
  -n $SRV -g $RG -l $LOC \
  -u $ADMIN -p "$PWD" \
  --enable-ad-only-auth false   # toggle true for Entra-only

# Configure Microsoft Entra admin
az sql server ad-admin create \
  -g $RG --server $SRV \
  --display-name "DBA Group" \
  --object-id <group-object-id>

# Allow Azure services + a specific client IP
az sql server firewall-rule create -g $RG -s $SRV \
  -n AllowAzureServices --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
az sql server firewall-rule create -g $RG -s $SRV \
  -n AllowMyIP --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10

# Database — Hyperscale, 2 vCores, GP-equivalent compute, ZRS backup
az sql db create -g $RG -s $SRV -n $DB \
  --edition Hyperscale --family Gen5 --capacity 2 \
  --backup-storage-redundancy Zone \
  --read-scale Enabled --high-availability-replica-count 1

# Enable Microsoft Defender for SQL on the server
az sql server advanced-threat-protection-setting update \
  -g $RG --server $SRV --state Enabled

# Verify
az sql db show -g $RG -s $SRV -n $DB --query "{name:name, edition:edition, slo:currentServiceObjectiveName}"

Common SQL deployment commands

Action	Command
Create elastic pool	az sql elastic-pool create -g $RG -s $SRV -n pool1 --edition GeneralPurpose --family Gen5 --capacity 4
Move DB into pool	az sql db update -g $RG -s $SRV -n $DB --elastic-pool pool1
Configure long-term retention	az sql db ltr-policy set -g $RG -s $SRV -n $DB --weekly P12W --monthly P12M --yearly P5Y --week-of-year 1
Configure short-term backup retention (PITR window)	az sql db str-policy set -g $RG -s $SRV -n $DB --retention-days 35 --diffbackup-hours 24
Restore a DB to a point in time	az sql db restore -g $RG -s $SRV -n $DB --dest-name $DB-restored -t 2026-04-15T03:30:00Z
Failover a failover group	az sql failover-group set-primary -g $RG -s $SRV-secondary -n fog-prod

Deploy a SQL Managed Instance (with mandatory subnet)

SUBNET_ID=/subscriptions/<sub>/resourceGroups/rg-net/providers/Microsoft.Network/virtualNetworks/vnet-sqlmi/subnets/snet-sqlmi

az sql mi create -g $RG -n ehab-mi-prod -l $LOC \
  --subnet $SUBNET_ID \
  --capacity 8 --storage 256GB \
  --edition GeneralPurpose --family Gen5 \
  --license-type LicenseIncluded \
  --admin-user $ADMIN --admin-password "$PWD" \
  --public-data-endpoint-enabled false

MI provisioning takes ~4–6 hours for the first instance in a subnet (virtual cluster build); subsequent instances in the same subnet are minutes.

Deploy a SQL Server VM via the SQL VM Image

az vm create -g $RG -n sqlvm01 -l $LOC \
  --image MicrosoftSQLServer:sql2022-ws2022:enterprise:latest \
  --size Standard_E8ds_v5 \
  --admin-username azureuser --generate-ssh-keys

# Register with the SQL IaaS Agent extension (Full mode) for portal management
az sql vm create -g $RG -n sqlvm01 -l $LOC \
  --license-type PAYG --sql-mgmt-type Full

Deploy via ARM/Bicep template from CLI

# What-if first (shows planned changes — never deploys)
az deployment group what-if -g $RG \
  --template-file ./sqldb.bicep \
  --parameters serverName=$SRV dbName=$DB

# Apply
az deployment group create -g $RG \
  --name sqldb-deploy-2026-04-28 \
  --template-file ./sqldb.bicep \
  --parameters serverName=$SRV dbName=$DB

Part 2 — Azure PowerShell

Authenticate

# Interactive
Connect-AzAccount -Tenant <your-tenant-id>

# Service principal
$sp = Get-Credential   # username = appId, password = client secret
Connect-AzAccount -ServicePrincipal -Credential $sp -Tenant <tenantId>

# Managed identity
Connect-AzAccount -Identity

# Pick subscription
Set-AzContext -Subscription "<your-subscription-name>"

Deploy an Azure SQL Server + Database

$rg    = 'rg-sqldb-prod'
$loc   = 'westeurope'
$srv   = 'ehab-sqlsrv01'
$db    = 'salesprod'
$cred  = Get-Credential -UserName 'sqladminuser' -Message 'SQL admin password'

New-AzResourceGroup -Name $rg -Location $loc

$server = New-AzSqlServer -ResourceGroupName $rg -Location $loc `
    -ServerName $srv -SqlAdministratorCredentials $cred `
    -EnableActiveDirectoryOnlyAuthentication:$false

# Microsoft Entra admin
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $rg `
    -ServerName $srv -DisplayName 'DBA Group' -ObjectId '<group-object-id>'

# Firewall
New-AzSqlServerFirewallRule -ResourceGroupName $rg -ServerName $srv `
    -FirewallRuleName 'AllowAzureServices' `
    -StartIpAddress '0.0.0.0' -EndIpAddress '0.0.0.0'

# Database — Hyperscale 2 vCore + ZRS backup + 1 read replica
New-AzSqlDatabase -ResourceGroupName $rg -ServerName $srv -DatabaseName $db `
    -Edition Hyperscale -VCore 2 -ComputeGeneration Gen5 `
    -BackupStorageRedundancy Zone `
    -ReadScale Enabled -HighAvailabilityReplicaCount 1

# Defender for SQL
Update-AzSqlServerAdvancedThreatProtectionSetting -ResourceGroupName $rg `
    -ServerName $srv -State Enabled

# Get current state — pipeline-friendly object
Get-AzSqlDatabase -ResourceGroupName $rg -ServerName $srv -DatabaseName $db |
    Select-Object DatabaseName, Edition, CurrentServiceObjectiveName, Status

Common cmdlets

Action	Cmdlet
Create elastic pool	New-AzSqlElasticPool -ResourceGroupName $rg -ServerName $srv -ElasticPoolName 'pool1' -Edition GeneralPurpose -Vcore 4 -ComputeGeneration Gen5
Configure LTR	Set-AzSqlDatabaseLongTermRetentionPolicy -... -WeeklyRetention P12W -MonthlyRetention P12M -YearlyRetention P5Y -WeekOfYear 1
Configure PITR window	Set-AzSqlDatabaseBackupShortTermRetentionPolicy -... -RetentionDays 35 -DiffBackupIntervalInHours 24
Restore point-in-time	$src = Get-AzSqlDatabase ...; Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime <utc> -ResourceId $src.ResourceId -ServerName $srv -TargetDatabaseName 'salesprod-restored'
Failover failover group	Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $rg -ServerName $srvSecondary -FailoverGroupName 'fog-prod'
Deploy ARM/Bicep template	New-AzResourceGroupDeployment -ResourceGroupName $rg -TemplateFile .\sqldb.bicep -serverName $srv -dbName $db
What-if deployment	New-AzResourceGroupDeployment -... -WhatIf

Pipeline-style scripting (the PowerShell strength)

# Disable Long-Term Retention on every DB in a server (compliance reset)
Get-AzSqlDatabase -ResourceGroupName $rg -ServerName $srv |
    Where-Object { $_.DatabaseName -ne 'master' } |
    ForEach-Object {
        Set-AzSqlDatabaseLongTermRetentionPolicy `
            -ResourceGroupName $rg -ServerName $srv `
            -DatabaseName $_.DatabaseName `
            -WeeklyRetention 'PT0S' -MonthlyRetention 'PT0S' -YearlyRetention 'PT0S' -WeekOfYear 0
    }

Part 3 — Monitor & troubleshoot a deployment

Where deployment state lives

Every ARM/Bicep deployment (whether triggered by Portal, CLI, PowerShell, GitHub Actions, or Terraform's azurerm_resource_group_template_deployment) is recorded as a deployment object under the resource group (or subscription / management group, depending on scope).

# List recent deployments at the RG scope
az deployment group list -g $RG --query "[].{name:name,state:properties.provisioningState,timestamp:properties.timestamp}" -o table

# Drill into a specific deployment
az deployment group show -g $RG -n sqldb-deploy-2026-04-28 \
    --query "properties.{state:provisioningState,duration:duration,outputs:outputs}"

# Show the operations (one entry per resource action) — gold for failure analysis
az deployment operation group list -g $RG -n sqldb-deploy-2026-04-28 \
    --query "[?properties.provisioningState=='Failed'].{resource:properties.targetResource.id,error:properties.statusMessage.error}" -o jsonc

PowerShell equivalents:

Get-AzResourceGroupDeployment -ResourceGroupName $rg | Select-Object DeploymentName, ProvisioningState, Timestamp
Get-AzResourceGroupDeployment -ResourceGroupName $rg -Name 'sqldb-deploy-2026-04-28'
Get-AzResourceGroupDeploymentOperation -ResourceGroupName $rg -DeploymentName 'sqldb-deploy-2026-04-28' |
    Where-Object ProvisioningState -eq 'Failed'

Activity Log — the platform's audit trail

az monitor activity-log list \
  --resource-group $RG \
  --start-time 2026-04-28T00:00:00Z \
  --offset 2h \
  --query "[?status.value=='Failed'].{op:operationName.value,resource:resourceId,reason:properties.statusMessage}"

Activity Log captures everything the control plane did, including the identity that fired the request — essential for "who deleted my server?" investigations.

Resource Health & Service Health

# Resource Health (per resource — is it up right now?)
az resource show --ids /subscriptions/.../servers/$SRV/databases/$DB \
  --query "properties.zoneRedundant"   # example — full RH is in the Portal blade

# Service Health (Azure-wide incidents)
az rest --method GET \
  --url "https://management.azure.com/subscriptions/$(az account show --query id -o tsv)/providers/Microsoft.ResourceHealth/events?api-version=2022-10-01"

Common failure patterns & fixes

Error code / message	Likely cause	Fix
LocationNotAvailableForResourceType	DB SKU not available in the chosen region	az sql db list-editions -l <loc> to find supported tiers
RequestQuotaExceeded: Subscription does not have enough vCore quota	Subscription-level vCore cap	Raise quota via support request / az quota
InvalidServerName	Name already taken globally	DNS namespace *.database.windows.net is global — choose a unique name
IpAddressIsNotInListedRange	Connecting from outside the firewall allow-list	Add a firewall rule or use a private endpoint
BackupStorageRedundancyNotSupported	Tier doesn't support the requested redundancy	E.g., Hyperscale supports Local/Zone/Geo-zone but not all combinations in all regions
SubscriptionNotRegistered: Microsoft.Sql	RP not registered	az provider register --namespace Microsoft.Sql
MI: LongRunningOperationFailed after several hours	Subnet doesn't meet MI requirements (NSG, route table, delegation)	Validate with az network vnet subnet show; ensure delegation Microsoft.Sql/managedInstances

Rollback / what-if

az deployment group what-if and New-AzResourceGroupDeployment -WhatIf show planned changes without applying them — always run before a production deployment. ARM does not auto-rollback on failure (deployments are partial). To "rollback" you re-run a previous good deployment with the same name in complete mode (be careful — complete mode deletes resources not in the template).

End-to-end pattern: GitHub Actions with OIDC + az CLI

name: Deploy SQL DB
on: { push: { branches: [main] } }

permissions:
  id-token: write
  contents: read

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - name: What-if
        run: |
          az deployment group what-if \
            -g rg-sqldb-prod \
            --template-file ./infra/sqldb.bicep \
            --parameters @./infra/sqldb.params.json

      - name: Deploy
        run: |
          az deployment group create \
            -g rg-sqldb-prod \
            --name sqldb-${{ github.run_id }} \
            --template-file ./infra/sqldb.bicep \
            --parameters @./infra/sqldb.params.json

This pattern uses federated identity (OIDC) so no secrets are stored in GitHub.

🏢 Real-World DBA Note

Oracle DBA parallel: Azure CLI / PowerShell deployments are conceptually similar to OCI CLI + Terraform OCI provider. The Azure-specific Deployments object (with operations & history) is the equivalent of OCI's Resource Manager Stack runs — a record of every IaC apply with success/failure attributable to a specific identity. :::

---

Anti-Patterns

• "az login then deploy from a developer laptop to prod." Auditable only via tenant sign-in logs; no change record on the resource. Use service principal / managed identity / OIDC in a pipeline.
• "Hardcode subscription ID in scripts." Breaks across tenants and forks. Use az account set --subscription <id> and source from CI variables.
• "Mix az and Get-Az* in one script." Two auth contexts, two telemetry models, two error-handling shapes. Pick one per script.
• "Use --no-wait and never check." --no-wait is great for fan-out, but the script must follow up with az resource wait or poll the operation. Otherwise failures are silent.
• "Skip what-if for ‘simple’ changes." Bicep / ARM what-if is the cheapest insurance against accidental delete. Always run pre-deploy.
• "Use Az module 5.x because that's what we have installed." Old Az versions miss new resource types and have known auth bugs. Pin a recent LTS version in CI.

⚠️ Watch Out

az deployment group create returns success when the deployment succeeded, not when each resource is healthy. A SQL DB deployment may succeed but the firewall rule may silently fail to apply if a downstream property conflict exists. Always check az deployment operation group list.

---

Migration Between Deployment Approaches

From → To	Path	Cost
Portal click-ops → CLI / PS scripts	Use az ... --output json to capture commands	Reproducible; not yet declarative
CLI scripts → ARM templates	Export ARM from existing resource; refactor	Declarative; verbose JSON
ARM → Bicep	az bicep decompile	Cleaner DSL; same engine
Bicep → Bicep modules + AVM	Adopt br/public:avm/... modules	Less custom code; AVM-tested
Bicep → Terraform	Rewrite in HCL; import existing state	Multi-cloud option; new state file to manage
Local CLI → GitHub Actions / Azure DevOps	Wire SP / OIDC; commit pipeline YAML	Auditable; rollback via revert
Service principal secret → OIDC federated	Configure federated credential on app	Removes secret rotation; pipeline-only
azd for greenfield → hand-written Bicep at scale	Adopt azd for new apps; legacy stays Bicep	Lower scaffold time; learning curve

Most expensive moves: Bicep → Terraform (HCL rewrite + state migration) and click-ops → IaC (cataloging current state).

---

Real Scenarios

1. One-off prod hotfix to add a firewall rule → az sql server firewall-rule create from a pipeline triggered manually with approval. Driver: traceable change record. Trade-off: pipeline overhead vs portal click.
2. Standing up 50 dev DBs in parallel → az sql db create --no-wait in a loop + az resource wait. Driver: parallel fan-out. Trade-off: error handling needs explicit polling.
3. GitOps-style infra changes → Bicep + GitHub Actions with OIDC + what-if on PR + create on merge to main. Driver: every change is an auditable commit. Trade-off: branch protection + reviewer required.
4. Multi-tenant ISV deploying customer-tenant resources → Multi-tenant Entra app + delegated consent + az deployment sub create. Driver: customer isolation. Trade-off: per-tenant consent flow.
5. Migration from legacy PS scripts to Bicep → Wrap existing scripts in pipeline tasks, then incrementally replace with Bicep modules. Driver: low-risk modernization. Trade-off: temporary mixed estate.

---

Flashcards

🧠 Smart order (weak cards first)

What command shows planned changes without applying them?

Click to reveal answer

`az deployment group what-if` (CLI) or `New-AzResourceGroupDeployment -WhatIf` (PowerShell).

1 / 6

Practice quiz

Q1/5

0 correct

You deployed a Bicep template that failed halfway through. Which command lists the per-resource operations (with their error messages) for that deployment?

Sources & further reading

• Create a single database — Azure CLI quickstart
• Create a single database — Azure PowerShell quickstart
• az sql reference
• Az.Sql PowerShell module reference
• What-if for ARM/Bicep deployments
• Troubleshoot common Azure deployment errors
• Azure Activity Log overview
• DP-300 study guide (April 24, 2026)

Last verified against MS Learn: April 28, 2026.