LLI Wiki — My Digital Knowledge Base

Azure Batch for a DBA: running scheduled PowerShell/SQL jobs

Azure Batch architecture as a DBA job platform: pool, job, task, package, managed identity, Key Vault, logs, and why Batch rather than SQL Agent, Automation or Functions.

For years my scheduled DBA tasks lived in SQL Server Agent: one job, one server, one script. With Azure SQL Database and elastic pools there is no Agent, and the same duties (exports, maintenance, reports) must run against hundreds of databases. I ended up choosing Azure Batch as the execution platform. Here is the architecture and why.

The building blocks

Batch component Role in my platform
Batch account The container: quotas, diagnostic logs, packages
Pool The nodes (VMs) that run tasks; carries the managed identity
Start task Prepares each node when it joins the pool (modules, tools)
Application package The versioned code (ZIP) deployed to the nodes
Job A group of tasks; caps parallelism
Task One execution: a pwsh command line for one database or one duty

Around it: a user-assigned managed identity (UAMI) attached to the pool, Key Vault for secrets, App Configuration or a config file for settings, a storage account for task outputs, and a Log Analytics workspace for diagnostics.

flowchart LR
    A["Azure DevOps (scheduled pipeline)"] --> B["Batch account"]
    B --> C["Job"]
    C --> D["Task : one per database"]
    E["Application package (versioned ZIP)"] --> F["Node pool"]
    D --> F
    F --> G["Managed identity (UAMI)"]
    G --> H["Azure SQL (elastic pool)"]
    G --> I["Key Vault / App Configuration"]
    F --> J["Storage : output files"]
    B --> K["Log Analytics : diagnostics"]
    K --> L["Grafana / alerts"]

What one run looks like

  1. A scheduled pipeline (cron) queries the inventory to find which databases to process.
  2. It creates or reuses a job, then submits one task per database.
  3. A pool node downloads the application package (once per node) and starts the task's entry script.
  4. The script asks the managed identity for a token (the node's metadata service), reads its secrets from Key Vault and connects to Azure SQL without a password.
  5. Standard output and logs go to storage; task events go to Log Analytics.

The job only does throttling: for example MaxParallelTasks = 3 so a night of exports cannot saturate the target elastic pool.

Why not SQL Agent, Automation or Functions?

Need SQL Agent Automation Functions Batch
Available on Azure SQL Database No Yes Yes Yes
Long runs (hours) Yes Limited (runbooks) Limited (Consumption plan) Yes
Heavy tools (sqlpackage, bcp, 7-Zip) Yes Hard Hard Yes, on the node
Massive fan-out, controlled parallelism No Weak Possible, you build it Native (tasks, jobs)
Control of image and tool versions Yes No No Yes

Automation and Functions are great for short, small scripts. As soon as you export multi-GB databases with sqlpackage, or want a hundred tasks queued with three running at once, Batch is the right tool. The price: you own the node image and the job lifecycle.

Identity and secrets

The managed identity is attached to the pool, not to the script. Inside the task the code detects its context (admin workstation, pipeline, Batch node) and picks the matching identity. No password in configuration: external keys (for mail sending, say) are Key Vault secret names resolved at run time.

On the SQL side the identity must exist as a database user (CREATE USER ... FROM EXTERNAL PROVIDER). That is the topic of the fourth article in this series.

Logs

Three levels: the application log written by the script, the task output archived in storage, and Batch events (start, completion, scheduling failure) sent as diagnostics to Log Analytics. The last one feeds my Grafana dashboards.

Lessons learned

Mastodon LLIMastodon SocialFosstodon