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