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