Elastic Jobs & Azure Automation
Since Azure SQL Database doesn't have SQL Agent, Microsoft provides two cloud-native automation services: Elastic Jobs (for T-SQL across databases) and Azure Automation (for PowerShell/Python runbooks).
Elastic Jobs — SQL Agent for Azure SQL Database
Elastic Jobs let you run T-SQL scripts across one or more Azure SQL databases on a schedule — the PaaS equivalent of SQL Agent.
Architecture
| Component | What It Is |
|---|---|
| Job Agent | Azure resource that hosts and executes jobs (requires a dedicated "job database") |
| Job Database | An Azure SQL DB that stores job definitions, history, and state |
| Target Group | Which databases to run against (single DB, elastic pool, shard map, or list) |
| Job | Container with steps (T-SQL only) |
| Job Credential | Database-scoped credential for authentication |
When to Use Elastic Jobs
| Scenario | Elastic Jobs? |
|---|---|
| Run T-SQL across multiple Azure SQL DBs | ✅ Best choice |
| Index maintenance across tenant databases | ✅ |
| Schema changes across all databases | ✅ |
| PowerShell automation | ❌ Use Azure Automation |
| Cross-service orchestration | ❌ Use Logic Apps |
| Single database scheduled T-SQL | ✅ (simpler alternatives exist) |
DP-300 key fact: Elastic Jobs require a dedicated Azure SQL Database as the job database. This is an additional cost. The job agent is a separate Azure resource that you deploy alongside it.
Target Group Types
| Target Type | Description |
|---|---|
| Single Database | One specific database |
| Elastic Pool | All databases in a pool |
| Shard Map | All shards in a shard map |
| Database List | Custom list of databases across servers |
| Server (all DBs) | All user databases on a logical server |
Elastic Jobs setup — enable order
The job agent identity (or scoped credential) needs db_owner on every target database before steps run — otherwise jobs report Login failed. With managed identity: also create the MI as a contained Entra user in each target DB. Skipping this is the #1 reason "the job runs but does nothing."
Authentication options for the job agent — what each one buys you
| Auth method | Conditional Access (location-based) | Password rotation needed | Granular Entra RBAC | Recommended for elastic jobs? |
|---|---|---|---|---|
| SQL login (master + DB-scoped credential) | ❌ Not subject to CA | ✅ You own it | ❌ DB-scoped only | ❌ Legacy only |
| Database-scoped credential with SQL login | ❌ | ✅ | ❌ | ❌ |
| System-assigned managed identity on the job agent | ✅ | ❌ Azure-managed | ✅ | ✅ Default choice |
| User-assigned managed identity on the job agent | ✅ | ❌ Azure-managed | ✅ (re-usable across agents) | ✅ When sharing across agents |
Conditional Access trap: Only managed identities (system- or user-assigned) honor Microsoft Entra location-based Conditional Access policies. SQL logins and database-scoped credentials with SQL auth bypass CA entirely — choosing them when the requirement is "block sign-ins from outside the corporate network" is the wrong answer.
Elastic Jobs vs SQL Agent
| Feature | SQL Agent | Elastic Jobs |
|---|---|---|
| Platform | VM + MI | Azure SQL DB |
| Step types | T-SQL, PS, SSIS, CmdExec | T-SQL only |
| Multi-database | Multi-server admin | Target groups |
| Scheduling | Built-in scheduler | Built-in scheduler |
| Cost | Included | Job agent + job DB cost |
| Notifications | Database Mail + operators | Azure Monitor alerts |
| History | msdb | Job database |
Azure Automation — PowerShell/Python in the Cloud
Azure Automation runs PowerShell or Python scripts (runbooks) on a schedule or on-demand — no VM required.
Key Concepts
| Component | What It Does |
|---|---|
| Automation Account | Container for runbooks, schedules, credentials |
| Runbook | PowerShell or Python script |
| Schedule | When the runbook runs |
| Managed Identity | Authentication to Azure resources (no passwords) |
| Hybrid Runbook Worker | Run runbooks on-premises or on specific VMs |
| Webhook | HTTP endpoint to trigger runbooks externally |
Common DBA Runbooks
| Runbook Purpose | What It Does |
|---|---|
| Scale up/down | Change Azure SQL vCores on schedule (scale up for batch, down at night) |
| Pause Serverless | Force auto-pause of serverless databases during off-hours |
| Index maintenance | Run Ola Hallengren scripts across multiple databases |
| Backup to Blob | Copy backups to secondary storage |
| Alert response | Auto-remediate when an alert fires |
| Compliance check | Verify security settings across databases |
Azure Automation uses Managed Identity for authentication — no stored passwords or connection strings. Always use System Assigned Managed Identity and grant it the minimum required Azure RBAC role (e.g., SQL DB Contributor for scaling operations).
Azure Automation vs Elastic Jobs
| Aspect | Elastic Jobs | Azure Automation |
|---|---|---|
| Best for | T-SQL across DBs | PowerShell/Python automation |
| Script types | T-SQL only | PowerShell, Python |
| Azure resource mgmt | ❌ | ✅ (scale, configure, deploy) |
| Cross-service | ❌ | ✅ (any Azure resource) |
| Cost | Job agent + DB | Per-minute execution |
| Authentication | DB credential | Managed Identity |
| On-prem support | ❌ | ✅ (Hybrid Worker) |
Common pattern: Use Elastic Jobs for database-level T-SQL automation (index maintenance, schema sync) and Azure Automation for infrastructure-level PowerShell automation (scaling, alerting, compliance). They complement each other — they're not competitors.
Azure Logic Apps — Visual Workflow Automation
Logic Apps provide visual, low-code workflow automation with 400+ connectors:
| Use Case | Why Logic Apps |
|---|---|
| Email on job failure | Connect SQL → Office 365 → send email |
| Approval workflows | Schema change requires manager approval |
| Multi-service orchestration | SQL → Event Hub → Function → Teams |
| Scheduled data sync | Timer → query SQL → post to API |
Logic Apps vs Azure Automation
| Aspect | Logic Apps | Azure Automation |
|---|---|---|
| Style | Visual designer (low-code) | Script (code-first) |
| Best for | Workflow orchestration | Script execution |
| Connectors | 400+ built-in | Manual REST/SDK calls |
| Cost | Per-action | Per-minute |
| Complex logic | Clunky for complex code | Natural for scripts |
Azure Functions — Timer-Triggered Automation
For developers who prefer code, Azure Functions with a Timer Trigger replace cron jobs:
| Feature | Detail |
|---|---|
| Languages | C#, Python, Java, JavaScript, PowerShell |
| Trigger | CRON expression (e.g., 0 */5 * * * * for every 5 min) |
| Scaling | Auto-scales (or Consumption plan = pay-per-execution) |
| Best for | Custom automation logic that doesn't fit Automation Runbooks |
Anti-Patterns
- "Use Elastic Jobs to send email." No email step type. Surface failure via Azure Monitor metric alert → Action Group instead.
- "Spin up an Elastic Job Agent for one job." The Job Agent + dedicated Job DB carry a fixed monthly cost (S1 ≈ $20). Single-DB / single-job? Use a stored proc + Azure Automation runbook on schedule.
- "Run long T-SQL jobs in Azure Automation." Runbook execution is capped (3 hours fair-share, 9 hours hard limit on Hybrid Worker). A 6-hour CHECKDB will get killed mid-run. Use SQL Agent on MI/VM, or Elastic Jobs with appropriate timeouts.
- "Hardcode SQL credentials in the runbook script." Use Azure Automation managed identity +
Connect-AzAccount -Identity. Never store SQL passwords in code or even Automation variables — use Key Vault secrets pulled at runtime. - "Functions Timer Trigger because we already use Functions." Consumption-plan Functions cold-start (~1–3 s), have 5–10 min execution timeout, and bill per-execution. For 5-minute polls of 100 DBs that's ~9000 invocations/day = surprise bill.
- "Logic Apps for nightly index rebuild." Logic Apps charges per action. A loop over 200 tables = 200+ actions/night. Native SQL Agent (MI) or Elastic Jobs do this for free.
Azure Automation has a 500 free runtime minutes per subscription per month. Any runbook polling more than every ~3 minutes burns the free quota in days. Prefer schedule-driven or webhook-driven runbooks over poll loops.
Migration Between Automation Hosts
| From → To | Path | Cost |
|---|---|---|
| SQL Agent (MI) → Elastic Jobs | Convert each step to T-SQL only; create Job DB + Agent | Rewrite cost; gain cross-DB targeting at scale |
| SQL Agent → Azure Automation | Wrap T-SQL in PowerShell with Invoke-Sqlcmd; managed identity | Auth + retry logic to add; gain cross-service orchestration |
| Windows Task Scheduler → Azure Automation | Port PowerShell as-is into runbook; Hybrid Worker if on-prem deps | Hybrid Worker = a VM + agent; otherwise free for first 500 min |
| Cron (Linux) → Azure Functions Timer Trigger | Rewrite as function; pin runtime version | Cold-start risk; switch to Premium plan if predictable |
| Azure Automation → GitHub Actions / Azure DevOps Pipelines | Move runbook to YAML pipeline; use OIDC for auth | Gain CI/CD ergonomics; lose webhook/watcher patterns |
| Logic Apps for DB ops → Functions or Automation | Rewrite workflow as code | Cuts per-action billing; loses visual designer |
| Elastic Jobs → Azure Automation runbook fanning out | PowerShell loop over server list; managed identity | Removes Job DB cost; gains scripting flexibility |
Most-expensive moves: SQL Agent → Elastic Jobs (T-SQL-only rewrite) and on-prem scheduler → Hybrid Worker (infra).
Real Scenarios
- Azure SQL DB SaaS, per-tenant index maintenance across 500 DBs → Elastic Jobs targeting tenant group, T-SQL only. Driver: only PaaS-native option. Trade-off: extra S0 ≈ $15/mo for the Job control DB.
- MI hosting ERP, nightly CHECKDB + index rebuild + stats update → SQL Agent on MI (not Elastic Jobs). Driver: native, free, T-SQL-rich. Trade-off: per-MI scope.
- Cross-cloud sync: AWS S3 → Azure Blob → SQL DB ingest → Azure Data Factory pipeline + stored proc activity. Driver: built-in connectors + retry. Trade-off: ADF pricing model (per-pipeline-run + per-activity).
- Daily failover-test runbook across 200 SQL VMs → Azure Automation runbook + managed identity, Hybrid Worker for on-prem ones. Driver: cross-resource, scheduled centrally. Trade-off: Hybrid Worker = an agent on a Windows VM you maintain.
- One-off batch: anonymize PII in 50 dev DBs → Azure Function with Timer or HTTP trigger, Premium plan to avoid timeout. Driver: code flexibility, can call internal APIs. Trade-off: Premium plan cost; could have been a Runbook for less.