SQL Server Agent Jobs
SQL Server Agent is the built-in job scheduler for SQL Server. It's available on SQL Server on VMs and SQL Managed Instance — but NOT on Azure SQL Database.
Where SQL Agent Works
| Platform | SQL Agent Available? | Alternative |
|---|---|---|
| SQL Server on VM | ✅ Full access | — |
| SQL Managed Instance | ✅ Full access | — |
| Azure SQL Database | ❌ Not available | Elastic Jobs, Azure Automation |
How SQL Agent Works
SQL Agent uses a 3-component model:
| Component | What It Does |
|---|---|
| Job | A container with one or more steps that execute in order |
| Step | A single unit of work (T-SQL, PowerShell, SSIS, OS command) |
| Schedule | When the job runs (recurring, one-time, on-demand) |
Job Step Types
| Step Type | Use Case | Example |
|---|---|---|
| T-SQL | Database operations | Index rebuild, statistics update, data cleanup |
| PowerShell | OS + Azure operations | Send email, call REST API, file operations |
| SSIS Package | ETL workflows | Data import/export between systems |
| OS Command (CmdExec) | Shell scripts | Run batch files, external programs |
| Replication | Log reader, snapshot, distribution | Transactional replication maintenance |
🎯 Exam Focus
DP-300 key fact: SQL Agent jobs run under the context of the SQL Server Agent service account unless you configure a proxy with specific credentials. For security, always use proxies with least-privilege credentials for non-T-SQL steps.
Job Scheduling Options
| Schedule Type | Example |
|---|---|
| Recurring | Every day at 2:00 AM, every Sunday at midnight |
| One-time | Run once on March 15 at 10:00 AM |
| CPU Idle | Run when CPU is idle (useful for low-priority maintenance) |
| On-demand | Manual execution only |
| On Agent Start | Run when SQL Agent service starts |
Notifications and Alerts
SQL Agent can notify you when jobs succeed or fail:
| Notification Method | How It Works |
|---|---|
| Database Mail | Send email via SMTP (requires Database Mail profile) |
| Operator | A named recipient (email, pager, net send) |
| Event Log | Write to Windows Event Log |
| SQL Agent Alert | Trigger another job or notification on error/severity |
Alert Types
| Alert Type | Triggers On | Example |
|---|---|---|
| SQL Server Error | Specific error number | Error 823 (I/O error) |
| Severity Level | Error severity 17-25 | Severity 19+ (fatal resource errors) |
| Performance Condition | Counter threshold | CPU > 90% for 5 minutes |
| WMI Event | WMI query match | Deadlock detected |
🏢 Real-World DBA Note
Best practice: Always create alerts for severity 17-25 errors. These are fatal errors (resource issues, hardware errors, corruption) that need immediate DBA attention. Configure Database Mail + an operator to get email notifications.
Common Maintenance Jobs Every DBA Should Create
| Job | Frequency | What It Does |
|---|---|---|
| Index Rebuild/Reorganize | Weekly | Defragment indexes (rebuild > 30%, reorganize 10-30%) |
| Update Statistics | Daily | Keep query optimizer statistics current |
| DBCC CHECKDB | Weekly | Check database integrity for corruption |
| Backup Cleanup | Daily | Delete old backup files beyond retention |
| Cycle Error Log | Weekly | sp_cycle_errorlog — prevents log from growing too large |
| Purge Job History | Monthly | Clean old job history to prevent msdb growth |
🎯 Exam Focus
Ola Hallengren's Maintenance Solution is the industry standard for SQL Server maintenance jobs. It creates optimized index, statistics, backup, and integrity check jobs. Know this exists for the exam — it's referenced in Microsoft documentation.
SQL Agent on Managed Instance — Differences
MI supports SQL Agent but with some differences from on-VM:
| Feature | SQL on VM | SQL MI |
|---|---|---|
| T-SQL steps | ✅ | ✅ |
| PowerShell steps | ✅ | ❌ (use Azure Automation) |
| SSIS steps | ✅ | ❌ (use ADF SSIS IR) |
| CmdExec steps | ✅ | ❌ |
| Database Mail | ✅ | ✅ |
| Operators | ✅ | ✅ |
| Proxies | ✅ | Limited |
| Multi-server jobs | ✅ | ❌ |
⚠️ Watch Out
MI limitation: SQL Agent on MI only supports T-SQL job steps. No PowerShell, CmdExec, or SSIS steps. For non-T-SQL automation on MI, use Azure Automation Runbooks or Azure Logic Apps.
Job Monitoring
| Method | What It Shows |
|---|---|
| Job Activity Monitor | Current status of all jobs (SSMS) |
msdb.dbo.sysjobhistory | Historical job execution results |
sp_help_jobactivity | Currently running jobs |
| SQL Agent Error Log | Agent-level errors and warnings |
| Azure Monitor (MI) | Agent job metrics in Azure Portal |
Anti-Patterns
- "Use SQL Agent on Azure SQL Database." Doesn't exist. The exam plants this as a wrong answer. Azure SQL DB → Elastic Jobs (T-SQL) or Azure Automation (PowerShell).
- "CmdExec step type for everything." CmdExec runs as the Agent service account = full OS privileges. One bad job script = lateral movement. Prefer T-SQL or PowerShell with constrained service accounts.
- "Email notification via Database Mail on MI." Not supported on MI. Use Azure Monitor alert on job failure → Action Group → email/Teams/SMS instead.
- "Job history forever." Default
msdbretention can fill the system DB. SetEXEC sp_purge_jobhistoryon a schedule and cap rows insysjobhistory. - "Schedule jobs at the same minute across servers." All-at-once job spikes saturate shared infra (backup target, log shipping target, monitoring). Stagger by ~5 minutes.
- "On-call gets paged from
sp_notify_operator." Operator emails go to whatever address was set when the job was created. Move alerting to Azure Monitor + Action Group so the on-call rotation lives in PagerDuty / Teams / ITSM, not inmsdb.
⚠️ Watch Out
SQL Agent on MI does not support PowerShell, ActiveX, or SSIS step types. Only T-SQL and OS (limited). Migrating from on-prem? Audit step types first — anything non-T-SQL needs a rewrite (often to Azure Automation runbook + Webhook step calling it).
Migration Between Job Hosts
| From → To | Path | Cost |
|---|---|---|
| SQL Agent (on-prem) → SQL Agent (SQL VM) | Script jobs from msdb; deploy on VM | Almost line-for-line; SSIS/CmdExec carries over |
| SQL Agent (on-prem) → SQL Agent (MI) | Script jobs; rewrite SSIS → ADF, CmdExec → Automation | T-SQL steps port; non-T-SQL steps need rewrite |
| SQL Agent (MI) → Elastic Jobs (Azure SQL DB) | Convert to T-SQL-only steps; create Job DB | Significant rewrite; gain cross-DB targeting |
| SQL Agent → Azure Automation runbook | Wrap T-SQL in Invoke-Sqlcmd; managed identity for auth | Adds auth work; gain cross-service orchestration |
| Database Mail → Azure Monitor + Action Group | Replace sp_notify_operator with metric alert | One-time wiring; gain on-call routing + retry |
| Manually scheduled jobs → Maintenance Plans | Use SSMS UI; wraps SQL Agent jobs underneath | Easier to author; harder to source-control |
msdb job export → source control | Generate Scripts for jobs; commit .sql files | Gains diff/review; pair with idempotent CREATE/DROP |
The expensive moves are MI → Elastic Jobs (rewrite to T-SQL only) and on-prem → MI when CmdExec/PowerShell steps exist.
Real Scenarios
- MI hosting 30 maintenance jobs ported from SQL 2019 → SQL Agent on MI for the 27 T-SQL jobs + 3 Azure Automation runbooks for the CmdExec ones. Driver: 90 % port, 10 % rewrite. Trade-off: two job catalogs to monitor.
- SQL VM in DR pair, identical job set on both → SQL Agent jobs scripted in IaC (Bicep + post-deploy
.sql). Driver: jobs survive AG failover (live on each replica). Trade-off: must guard against double-execution — usesys.fn_hadr_is_primary_replica()in step 1. - Nightly CHECKDB across 50 SQL VMs → SQL Agent local on each VM, staggered start times every 3 minutes. Driver: keeps work local, no central orchestrator dependency. Trade-off: harder to see fleet status — pair with Azure Monitor.
- Compliance: jobs must alert ITSM on failure → SQL Agent failure → Azure Monitor metric alert on
sql_agent_job_failed→ Action Group → ServiceNow connector. Driver: native SLA tracking. Trade-off: extra hop adds ~30 s detection latency. - Per-tenant maintenance across 200 Azure SQL DBs → Not SQL Agent (unsupported); use Elastic Jobs with target group of all DBs. Driver: only PaaS option. Trade-off: extra Azure SQL DB for the Job control DB.
Flashcards
Is SQL Server Agent available on Azure SQL Database?
Click to reveal answer
No. SQL Agent is only available on SQL Server on VMs and SQL Managed Instance. For Azure SQL DB, use Elastic Jobs or Azure Automation.
1 / 5
Quiz
You need to schedule a nightly index maintenance job on Azure SQL Managed Instance. Which step type should you use?