12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

Azure Automation: The Platform for Scheduled Tasks

Where Elastic Jobs handle T-SQL across many databases, Azure Automation is the general-purpose scheduler and orchestrator for any script in Azure. It is the right tool when the automation is not a single T-SQL batch - for example, calling Azure REST APIs, restarting a VM, copying blobs, rotating keys in Key Vault, or orchestrating a sequence of steps that touch more than one service. The DP-300 exam expects you to know what lives in an Automation account, how runbooks authenticate to SQL, and how to alert and troubleshoot.

An Azure Automation account is the container for everything automation-related. Inside it you find:

A runbook is a single script. Runbook types include:

• PowerShell - native PowerShell script; the most common.
• Python - Python 3 script; useful for teams with Python skills.
• Graphical - a visual flow designed in the portal; good for low-code orchestration and approval workflows.
• PowerShell Workflow - the older workflow-based type that supports checkpoints and parallelism; rarely chosen for new runbooks.

Hybrid Runbook Worker

By default, runbooks run on an Azure-hosted sandbox worker, which has outbound internet access but cannot reach on-premises resources or anything behind a private endpoint without extra configuration. A Hybrid Runbook Worker is a VM (Azure VM or on-premises) with the Hybrid Worker extension installed; runbooks targeted to a hybrid worker run on that VM and can reach whatever that VM can reach. The exam scenario that says "run a script that connects to an on-premises SQL Server" or "run a script against a database behind a private endpoint" points to a hybrid worker - the Azure sandbox cannot reach either.

Authenticating a Runbook to Azure SQL

The cleanest modern pattern is a system-assigned managed identity on the Automation account. The runbook acquires a token with Connect-AzAccount -Identity and uses it to run Azure cmdlets or to obtain a SQL access token. The SQL-side work is to provision the managed identity as a database user:

CREATE USER [myAutomationAccount] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [myAutomationAccount];
-- grant whatever the runbook needs

Then in the runbook, authenticate with the managed identity, acquire a token for https://database.windows.net, and connect with Invoke-Sqlcmd (from the SqlServer PowerShell module) using the access token:

Connect-AzAccount -Identity
$token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net').Token
Invoke-Sqlcmd -ServerInstance tcp:sql-prod.database.windows.net,1433 `
  -Database SalesDB -AccessToken $token -Query 'SELECT @@VERSION'

The legacy pattern is an Automation Credential asset (username + password for a SQL login) or a service principal with a stored secret. Both still work, but the exam prefers managed identities because they eliminate secret rotation - a point worth citing explicitly in scenario answers.

Schedules and Variables

A schedule is a recurring or one-time trigger you link to a runbook with Register-AzAutomationScheduledRunbook (PowerShell) or via the portal. You can link multiple runbooks to one schedule, and a runbook can be linked to multiple schedules with different parameter sets. Variables are how you share configuration (server name, database name, retention days) across runbooks without editing scripts; mark sensitive values as encrypted when stored.

Alerts and Notifications on Database Tasks

DP-300 explicitly tests configure alerts and notifications on database tasks. Azure has two alert types that matter for automation:

• Metric alerts fire on numeric thresholds. For Azure SQL Database, useful metrics include cpu_percent, storage_percent, deadlock, connection_successful (and its failed counterpart), and tempdb_data_file_size_percent. You set a threshold (e.g., cpu_percent > 80 for 5 minutes), an evaluation frequency, and an action group.
• Activity log alerts fire on control-plane events - resource write operations, service health, recommendations, and (critically for this objective) Automation job status. You can alert whenever a runbook job fails (Job status = Failed) or whenever a specific runbook completes.

An action group is the reusable delivery layer: a named set of one or more actions that fire when an alert triggers. Supported action types:

The pattern the exam rewards: create one action group per notification persona (e.g., DBA-on-call, ops-team-channel) and reuse it across multiple alerts, rather than defining ad-hoc recipients on every alert. Metric alerts on cpu_percent and storage_percent with a single DBA action group cover most day-to-day Azure SQL alerting.

Monitoring Automation Jobs

Every runbook execution is a job. You inspect jobs through:

• Job streams - PowerShell output, error, warning, and verbose streams captured per job. Get-AzAutomationJobOutput and Get-AzAutomationJobStream return the streams; in the portal, the job page shows them inline.
• Job status - New, Running, Completed, Failed, Stopped, Suspended. Get-AzAutomationJob -Status Failed lists recent failures.
• Activity log - every Automation job write is recorded, so activity-log alerts on job status are the canonical way to alert on automation failures.

A robust pattern: a runbook wraps its real work in try/catch, writes a structured error to the verbose stream, and lets the job status reflect success/failure so the activity-log alert fires only on real failures (not on handled warnings).

Common Runbook Failures and How to Troubleshoot

The exam will show you a failing runbook and ask what is wrong. Most failures fall into five buckets:

A useful troubleshooting order: (1) open the failed job in the portal and read the error stream; (2) if the error is auth, check the identity and SQL user; (3) if the error is connectivity, test from the worker's network; (4) if the error is a missing cmdlet, update modules; (5) if intermittent, check the schedule's parameter set. The job streams and the activity log are your two primary evidence sources.

Module Version Gotchas

Azure Automation ships with a default set of modules, but those versions age. If a runbook calls a cmdlet that exists only in a newer Az.Sql (or SqlServer) module, the runbook fails with a "cmdlet not recognized" error. The fix is to update the module in the Automation account under Modules > Browse Gallery (or via Update-AzAutomationModule). Pinning to a specific module version prevents silent breakage when the platform updates a default module and changes a behavior. The exam tests this as a specific trap: a runbook that worked yesterday fails today after a module auto-update changed a parameter name.

When to Choose Azure Automation vs Elastic Jobs vs SQL Server Agent

The decision comes up repeatedly, so here is the consolidated guidance:

The exam framing: T-SQL across many Azure SQL Databases is the only scenario that always points to Elastic Jobs. MI or VM + SQL Agent job type (T-SQL, SSIS, CmdExec, PowerShell subsystem) points to SQL Server Agent. Anything else - cross-service, non-T-SQL, on-premises, multi-step orchestration - points to Azure Automation.

Putting It Together: A Realistic Automated Task with Alerts

Consider a nightly runbook that backs up each tenant database to blob storage, deletes backups older than 30 days, and pages the on-call DBA if any step fails. The components:

1. Automation account with a system-assigned managed identity granted db_backupoperator on each tenant database and Storage Blob Data Contributor on the storage account.
2. Runbook (PowerShell) that loops over databases, runs BACKUP DATABASE ... TO URL, then deletes blobs older than 30 days using Remove-AzStorageBlob.
3. Schedule linked to the runbook, recurring daily at 02:00.
4. Action group "DBA-on-call" with an email recipient and a Logic App that posts to the on-call Teams channel.
5. Activity log alert on Job Status = Failed for this runbook, using the action group above.

When a backup fails (e.g., a tenant database was dropped mid-loop, or storage throttled), the job stream records the specific error, the job status flips to Failed, and the activity-log alert fires the action group, which emails the DBA and posts to Teams. The next morning, the DBA reads the job stream, fixes the root cause, and either re-runs the runbook on demand or waits for the next scheduled run. This is the end-to-end pattern the exam is really testing: not a single cmdlet, but the integration of Automation, managed identity, schedules, action groups, and activity-log alerts into one reliable, observable automated task.