Automation Decision Matrix
This page is your quick reference for choosing the right automation tool for any DBA task across all Azure SQL platforms.
The Decision Matrix
| Task | SQL on VM | SQL MI | Azure SQL DB | Recommended Tool |
|---|---|---|---|---|
| Index maintenance | SQL Agent | SQL Agent | Elastic Jobs | T-SQL-based scheduling |
| Statistics update | SQL Agent | SQL Agent | Elastic Jobs | T-SQL-based scheduling |
| DBCC CHECKDB | SQL Agent | SQL Agent | N/A (PaaS) | SQL Agent |
| Backup management | SQL Agent | Automated | Automated | Platform-managed for PaaS |
| Schema deployment | Azure DevOps | Azure DevOps | Azure DevOps | CI/CD pipeline + DACPAC |
| Scale vCores | N/A (VM resize) | Automation Runbook | Automation Runbook | PowerShell w/ Managed Identity |
| Failover testing | SQL Agent | Automation Runbook | Automation Runbook | Scheduled failover script |
| Send alerts | Database Mail | Azure Monitor | Azure Monitor | Platform alerting |
| Cross-DB T-SQL | Multi-server admin | Linked Servers | Elastic Jobs | Per-platform best tool |
| Compliance checks | Automation Runbook | Automation Runbook | Automation Runbook | PowerShell scanning |
| Report generation | SSIS / SQL Agent | ADF / Automation | ADF / Automation | Cloud-native ETL |
| Workflow orchestration | SSIS | Logic Apps | Logic Apps | Visual workflow designer |
| Custom code automation | Task Scheduler | Azure Functions | Azure Functions | Timer-triggered functions |
Platform Availability Quick Reference
| Tool | SQL on VM | SQL MI | Azure SQL DB |
|---|---|---|---|
| SQL Server Agent | ✅ Full | ✅ T-SQL only | ❌ |
| Elastic Jobs | ❌ | ❌ | ✅ |
| Azure Automation | ✅ | ✅ | ✅ |
| Logic Apps | ✅ | ✅ | ✅ |
| Azure Functions | ✅ | ✅ | ✅ |
| Azure DevOps | ✅ | ✅ | ✅ |
| Bicep / ARM | ✅ | ✅ | ✅ |
| Database Mail | ✅ | ✅ | ❌ |
Decision Flowchart (Text)
What do you need to automate?
-
T-SQL on a single database?
- VM/MI → SQL Agent job
- SQL DB → Elastic Job (or Azure Automation with
Invoke-Sqlcmd)
-
T-SQL across multiple databases?
- SQL DB → Elastic Jobs with target group
- VM → Multi-server administration
- MI → SQL Agent on each MI (no cross-MI jobs)
-
PowerShell / Azure resource management?
- All platforms → Azure Automation Runbook with Managed Identity
-
Complex workflow with multiple services?
- All platforms → Logic Apps (visual) or Azure Functions (code)
-
Infrastructure deployment?
- All platforms → Bicep + Azure DevOps/GitHub Actions pipeline
-
Schema changes?
- All platforms → SSDT + DACPAC + CI/CD pipeline
Cost Comparison
| Tool | Cost Model | Monthly Estimate (typical DBA use) |
|---|---|---|
| SQL Agent | Included with SQL license | $0 |
| Elastic Jobs | Job agent + S1 job DB | ~$30-50/month |
| Azure Automation | 500 free min/month, then per-min | ~$0-10/month |
| Logic Apps (Consumption) | Per action (~$0.000125/action) | ~$1-5/month |
| Azure Functions (Consumption) | 1M free executions/month | ~$0/month |
| Azure DevOps | Free tier (1 agent, 1800 min/month) | $0 |
🏢 Real-World DBA Note
Cost-effective pattern for most DBAs: SQL Agent for VM/MI jobs (free), Azure Automation for PowerShell (500 free min), Azure Monitor alerts (basic metrics free), Azure DevOps free tier for CI/CD. Total additional cost: ~$0-10/month.
Migration Path: On-Prem → Cloud Automation
| On-Prem Tool | Cloud Equivalent |
|---|---|
| SQL Agent (on-prem) | SQL Agent (VM/MI) or Elastic Jobs (SQL DB) |
| Windows Task Scheduler | Azure Automation or Azure Functions |
| SSIS | Azure Data Factory SSIS IR or ADF pipelines |
| PowerShell scripts | Azure Automation Runbooks |
| cron jobs (Linux) | Azure Functions Timer Trigger |
| Nagios / Zabbix alerts | Azure Monitor + Action Groups |
| ServiceNow integration | Azure Monitor → ITSM connector |
🎯 Exam Focus
DP-300 commonly tests: "Which automation tool for Azure SQL Database?" → Elastic Jobs for T-SQL, Azure Automation for PowerShell. "Which for MI?" → SQL Agent (T-SQL only) + Azure Automation. "Which for VM?" → SQL Agent (full) + Azure Automation.
Anti-Patterns
- "Use Azure Automation for everything because it's centralized." Job-control state lives outside SQL. SQL Agent on MI/VM has native job history in
msdb, retry semantics tied to T-SQL errors, and zero auth hop. Don't trade native ergonomics for centralization unless you actually need cross-resource orchestration. - "Replace SQL Agent with Logic Apps." Logic Apps charges per action. A nightly index-rebuild loop with 200 tables = 200 actions × 365 days. SQL Agent on MI is free with the license. Pick Logic Apps only when the workflow spans services (Teams notify, ServiceNow ticket, blob copy).
- "Elastic Jobs is a drop-in for SQL Agent on SQL DB." No CLR, no SSIS step type, no PowerShell step, no email step (use Action Groups instead), and the job DB is a separate billable Azure SQL DB. Plan for the rewrite, not a port.
- "Functions Timer Trigger for DBA tasks." Functions are stateless and tuned for short execution. A 4-hour CHECKDB on a multi-TB DB will hit the consumption-plan timeout. Use Premium plan or, better, SQL Agent / Azure Automation.
- "One Automation account for the whole org." Runbook concurrency is per-account. A single noisy job stalls the rest. Split by environment (prod / non-prod) at minimum.
⚠️ Watch Out
Azure Automation has 500 free runtime minutes per subscription per month. After that, billing is per-minute. A runbook that polls every 5 minutes burns the free quota in 1.7 days. Use webhooks or schedules with longer intervals, not poll loops.
Migration Between Automation Tools
| From → To | Path | Cost |
|---|---|---|
| SQL Agent (on-prem) → SQL Agent (MI) | Script jobs from msdb; deploy on MI | Almost line-for-line; some steps (CmdExec, SSIS) need rewrite |
| SQL Agent (on-prem) → Elastic Jobs | Convert each step to T-SQL; create Job DB; re-author schedule | Step-by-step rewrite; lose CLR/PowerShell steps |
| SQL Agent → Azure Automation | Wrap T-SQL in PowerShell using Invoke-Sqlcmd; managed identity for auth | Auth + retry logic to add; gain cross-service orchestration |
| Task Scheduler → Azure Automation | Port PowerShell as-is into a runbook; use a Hybrid Worker if on-prem dependencies | Hybrid Worker = VM + agent; otherwise free for first 500 min/mo |
| Cron → Functions Timer Trigger | Rewrite as function; pin runtime version | Cold-start risk; switch to Premium plan if predictable latency matters |
| Azure Automation → GitHub Actions / Azure DevOps Pipelines | Move runbook to YAML pipeline; use OIDC federation for auth | Gain CI/CD ergonomics; lose webhook + watcher patterns |
The expensive moves are SQL Agent → Elastic Jobs (rewrite) and on-prem → Hybrid Worker (infra). Everything else is mostly script porting.
Real Scenarios
- MI with 40 maintenance jobs from on-prem SQL 2019 → SQL Agent on MI, scripted from
msdb. Driver: 90 % work line-for-line. Trade-off: 3 jobs using CmdExec rewritten as Azure Automation runbooks. - Azure SQL DB SaaS, per-tenant index maintenance → Elastic Jobs targeting a tenant group. Driver: only T-SQL, runs across hundreds of DBs in one job. Trade-off: extra Azure SQL DB for the job control DB (~ S0 ≈ $15/mo).
- Cross-cloud sync (S3 → Azure Blob → SQL DB ingest) → Azure Data Factory pipeline + SQL stored procedure activity. Driver: visual orchestration + connectors. Trade-off: ADF pricing model (per-pipeline-run + per-activity).
- Alert pipeline: SQL fires error → Teams notify + ServiceNow ticket → Azure Monitor alert → Action Group → Logic App. Driver: native ITSM connectors. Trade-off: Logic Apps actions billed; keep workflow short.
- Daily backup verification across 200 SQL VMs → Azure Automation runbook with PowerShell DSC + managed identity. Driver: cross-VM, cross-subscription scope, scheduled centrally. Trade-off: auth setup with managed identity is the longest part of the build.
Flashcards
What automation tool replaces SQL Agent for Azure SQL Database?
Click to reveal answer
Elastic Jobs for T-SQL scheduling, Azure Automation for PowerShell. SQL Agent is not available on Azure SQL DB.
1 / 5
Quiz
A customer has 100 Azure SQL databases and needs to run weekly index maintenance T-SQL across all of them. What should they use?