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 |
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 |
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.