Automation Landscape: What Runs Where
Understand the automation tools available for Azure SQL — SQL Agent, elastic jobs, Azure Automation, and Infrastructure as Code. Know which tool fits each platform.
The automation decision matrix
Think of automation like hiring help for different jobs:
SQL Server Agent is your on-site building manager — lives inside the SQL Server, runs scheduled T-SQL jobs, sends alerts. Only available on MI and VMs.
Elastic jobs are the district manager — one controller that runs T-SQL across MANY Azure SQL databases. Perfect for multi-database maintenance.
Azure Automation is the corporate operations team — runs PowerShell runbooks to manage Azure resources (scale DBs, start/stop VMs, orchestrate workflows).
IaC (ARM/Bicep/CLI) is the architect’s blueprint — defines what resources should exist and deploys them consistently.
Which tool for which platform?
| Tool | SQL Database | Managed Instance | SQL on VMs |
|---|---|---|---|
| SQL Server Agent | No | Yes | Yes |
| Elastic jobs | Yes (target) | Yes (target) | No |
| Azure Automation (runbooks) | Yes (manage resources) | Yes (manage resources) | Yes (manage resources) |
| ARM / Bicep templates | Yes (deploy/configure) | Yes (deploy/configure) | Yes (deploy/configure) |
| Azure PowerShell / CLI | Yes | Yes | Yes |
| Logic Apps (scheduled) | Yes | Yes | Yes |
Decision matrix: which tool for the task?
| Task | Best Tool | Why |
|---|---|---|
| Run T-SQL maintenance on a single MI/VM database | SQL Server Agent | Built-in scheduler, lives on the instance |
| Run the same T-SQL across 50 Azure SQL databases | Elastic jobs | Designed for multi-database T-SQL execution |
| Scale a database up during business hours, down at night | Azure Automation + runbook | PowerShell manages Azure resources on a schedule |
| Deploy 10 identical databases across regions | Bicep / ARM template | Declarative IaC, idempotent, version-controlled |
| Orchestrate a complex workflow (backup + validate + notify) | Azure Automation or Logic Apps | Multi-step workflow orchestration |
| Monitor and alert on database metrics | Azure Monitor alerts | Not automation per se, but triggers actions |
Exam tip: SQL Agent is NOT on SQL Database
This is a high-frequency exam question. SQL Server Agent is a Windows service that runs on the SQL Server instance. Azure SQL Database is a PaaS service with no instance-level access → no Agent.
If the scenario says “Azure SQL Database” and needs scheduled T-SQL jobs, the answer is elastic jobs, NOT Agent. If the scenario says “Managed Instance” or “SQL on VMs”, SQL Server Agent is available.
Kenji’s automation strategy at NorthStar:
- SQL Agent on MI: nightly index maintenance, weekly CHECKDB, statistics updates
- Elastic jobs: monthly data purge across 15 reporting databases
- Azure Automation: Auto-scale MI vCores before month-end processing
- Bicep templates: Deploy new MI instances for test environments
Priya needs to run a nightly index maintenance script across all 30 tenant databases in Azure SQL Database (not MI). Which tool should she use?
Next up: SQL Server Agent Jobs — create and manage scheduled jobs with alerts and notifications.