Automation & Scheduling
Automation Landscape — Know Which Tool When
Decision Matrix (Exam Favorite)
| Feature | SQL Agent | Elastic Jobs | Azure Automation | Azure Functions | Logic Apps |
|---|---|---|---|---|---|
| Available on SQL DB | ❌ | ✅ | ✅ | ✅ | ✅ |
| Available on MI | ✅ | ❌ | ✅ | ✅ | ✅ |
| Available on VM | ✅ | ❌ | ✅ | ✅ | ✅ |
| Runs T-SQL | ✅ | ✅ | Via script | Via SDK | Via connector |
| Multi-DB targeting | ❌ | ✅ | Via script | Via code | Via connector |
| PowerShell | ✅ | ❌ | ✅ | ✅ | ❌ |
| Schedule | ✅ | ✅ | ✅ | ✅ (Timer) | ✅ (Recurrence) |
| Event-driven | ❌ | ❌ | Via webhook | ✅ | ✅ |
| No code | ❌ | ❌ | ❌ | ❌ | ✅ |
The simplest exam rule: SQL Agent → MI & VM. Elastic Jobs → Azure SQL Database. If the question says "Azure SQL Database" + "schedule T-SQL" → Elastic Jobs. If it says "scale the database tier on a schedule" → Azure Automation.
SQL Agent Jobs (MI & VM)
The direct equivalent of Oracle DBMS_SCHEDULER. Fully supported on MI and VM.
Architecture
Key exam facts:
- Jobs, steps, schedules, alerts stored in msdb database
- Jobs can have multiple steps with success/failure flow control
- Steps support: T-SQL, PowerShell, SSIS, SSAS, Replication
- MI Failover Groups don't replicate msdb → agent jobs must be manually synced to secondary
Oracle DBA parallel: SQL Agent = DBMS_SCHEDULER + DBMS_JOB. The concepts are identical: jobs, steps (programs), schedules, chains (multi-step with flow control), notifications (alerts). The main difference: SQL Agent uses msdb to store metadata; Oracle uses SYS.SCHEDULER$_* tables.
Elastic Jobs (Azure SQL Database)
Since SQL Agent isn't available in Azure SQL Database, Elastic Jobs fills the gap.
Architecture
Key concepts:
- Job Agent — manages jobs, requires its own database
- Target Group — defines which databases to run against (server, pool, or individual DBs)
- Job Step — T-SQL command to execute
- Credential — authentication to connect to target databases
Elastic Jobs need a dedicated database for the job agent. Target groups can be: an entire server (all DBs), an elastic pool, or specific individual databases. The job agent connects to each target and runs the T-SQL step.
Azure Automation — Infrastructure Scheduling
Best for PowerShell/Python operations on Azure resources (not T-SQL on databases).
Common Use Cases
| Scenario | Oracle Equivalent | How It Works |
|---|---|---|
| Scale DB up for business hours, down at night | cron + shell script | Scheduled runbook calls Set-AzSqlDatabase |
| Patch management | OPatch scripts | Update Management built-in |
| Automated failover testing | Data Guard switchover script | Runbook calls failover group commands |
| Resource cleanup | Custom cleanup scripts | Scheduled runbook deletes old resources |
Key Architecture
Azure Automation runbooks use Managed Identity (or Run-As accounts, now deprecated). Always use System-assigned Managed Identity with RBAC roles. Never store secrets in the runbook code.
Azure Functions — Event-Driven Automation
Best for code-based tasks triggered by events or timers.
| Trigger | Use Case | Example |
|---|---|---|
| Timer | Scheduled maintenance | Update stats daily at 2am |
| HTTP | On-demand action | Trigger database refresh via API |
| Queue | Async processing | Process new data import messages |
| Event Grid | React to Azure events | Alert when SQL threat detected |
Oracle DBA parallel: Azure Functions (Timer Trigger) = the modern replacement for cron + shell scripts. Serverless (no VM to manage), auto-scaling, pay-per-execution. Your AzureDigest project already uses this pattern!
Logic Apps — No-Code Workflows
Best for multi-step workflows with approvals and integrations.
When to use Logic Apps over Functions:
- You need visual workflow design (non-developers in the team)
- You need built-in connectors (Email, Teams, ServiceNow, SAP)
- You need approval workflows (human-in-the-loop)
- You DON'T need custom code logic
Anti-Patterns
- "Try to use SQL Agent on Azure SQL DB." Doesn't exist there. Pick Elastic Jobs (T-SQL) or Azure Automation (PowerShell) for Azure SQL DB scheduling.
- "Logic Apps for nightly index rebuild." Logic Apps charges per action. A loop over 200 tables = 200+ actions/night. Use SQL Agent (MI/VM) or Elastic Jobs.
- "Functions Consumption plan for cron jobs every minute." Cold-start + per-execution billing makes high-frequency cron expensive. Use Functions Premium plan or move to a Runbook on schedule.
- "Webhook URL in source control." Webhook URLs include the auth token. Treat them like secrets — store in Key Vault, fetch at runtime.
- "Hardcode SQL credentials in Logic App / Function / Runbook." Use managed identity + Entra-auth on the SQL side. Removes the rotation problem entirely.
- "One mega-runbook for all DBA tasks." Hard to debug, hard to schedule selectively, single point of failure. Split per-task; orchestrate with a parent runbook if needed.
Azure Automation runbook execution time fair-share is 3 hours. A 6-hour CHECKDB script on a Hybrid Worker survives, but on a regular Automation worker gets killed mid-run. Validate runtime requirements before choosing the host.
Migration Between Schedulers
| From → To | Path | Cost |
|---|---|---|
| Windows Task Scheduler → Azure Automation | Port PowerShell into runbook; Hybrid Worker if on-prem deps | Hybrid Worker = a VM + agent; otherwise free for first 500 min/sub/month |
| cron (Linux) → Azure Functions Timer Trigger | Rewrite as function; Premium plan for predictable runs | Cold-start risk on Consumption |
| SQL Agent (on-prem) → SQL Agent (MI) | Script jobs, rewrite SSIS → ADF, CmdExec → Automation | T-SQL ports cleanly; non-T-SQL needs rewrite |
| SQL Agent (MI) → Elastic Jobs | Convert to T-SQL only; create Job DB | Significant rewrite; gain cross-DB targeting |
| Logic App scheduled trigger → Function Timer Trigger | Rewrite as code; remove per-action billing | Cuts cost; loses visual designer |
| Per-resource schedules → ADF / Synapse pipelines | Define triggers in ADF; activities call SQL | Adds ADF cost; gains end-to-end orchestration |
| Manual on-call runbooks → Webhook + Teams adaptive card | Wire Action Group → webhook → runbook | One-time wiring; gains chat-ops |
| Automation runbook on schedule → GitHub Actions workflow | Move to GH Actions YAML; OIDC for Azure auth | CI/CD ergonomics; loses webhook patterns |
Most expensive moves: SQL Agent → Elastic Jobs (T-SQL-only rewrite) and on-prem TS → Azure Automation (Hybrid Worker infra).
Real Scenarios
- MI hosting ERP — nightly maintenance jobs → SQL Agent on MI (no Automation needed). Driver: native, free, T-SQL-rich. Trade-off: per-MI scope.
- SaaS with 200 Azure SQL DBs — per-tenant index rebuild → Elastic Jobs targeting tenant group. Driver: only PaaS-native option. Trade-off: extra Job control DB cost.
- Cross-cloud sync (S3 → Blob → SQL) → ADF pipeline with stored proc activity, scheduled trigger. Driver: built-in connectors + retry + monitoring. Trade-off: ADF pricing model.
- Daily failover-test across 200 SQL VMs (some on-prem) → Azure Automation runbook + Hybrid Worker for the on-prem ones. Driver: cross-resource, scheduled centrally. Trade-off: Hybrid Worker is an agent on a Windows VM you maintain.
- Approval-gated DB schema migration → Logic App with Approval action → SQL connector → Teams notification. Driver: human-in-the-loop required. Trade-off: per-action billing acceptable for low-frequency releases.