Scheduled pipelines for SQL maintenance and exports: cron, UTC and a single scheduler
Cron schedules, always false, UTC time zones, avoiding two schedulers on the same job, retiring an old pipeline safely and the release process.
A good part of my SQL maintenance and nightly exports runs on Azure DevOps scheduled pipelines: a report every morning, an hourly job that creates Batch tasks, a night-time export. It is simple until two schedulers start the same work. Here are my rules.
A minimal scheduled pipeline
trigger: none # never triggered by a push
schedules:
- cron: "35 18 * * *"
displayName: "Nightly export at 18:35 UTC"
branches:
include:
- main
always: true
pool:
name: pool-dba-agents
jobs:
- job: Run
steps:
- pwsh: ./Invoke-NightlyExport.ps1 -Environment prd
trigger: none matters: otherwise a simple push would start a production export.
Cron and time zone
Cron syntax has five fields: minute, hour, day of month, month, day of week. In YAML, the time is always UTC. There is no local time zone and no daylight-saving handling: a job at 0 5 * * * runs at 05:00 UTC, which is 06:00 or 07:00 in Brussels depending on the season. I put the UTC time in the displayName so nobody misreads the UI.
| Need | Cron (UTC) |
|---|---|
| Daily report before the team arrives | 0 5 * * * |
| Hourly scheduler | 30 * * * * |
| Nightly export | 35 18 * * * |
always: false vs always: true
always: true: the pipeline runs at every occurrence, even if nothing changed. That is what I want for a report or an export.always: false(default): the pipeline only runs if the branch changed since the last scheduled run. Handy for a periodic code validation, wrong for an operational task: a report that is skipped because "nothing changed" is a false silence.
A scheduler that creates Batch tasks
For SQL maintenance, an hourly pipeline queries a schedule table, then creates Azure Batch jobs and tasks for the databases whose time has come. The pipeline does not do the maintenance: it orchestrates. Benefits: parallelism is bounded on the Batch side (max concurrent tasks per job), failures are isolated per database, and a "specific database" parameter lets me rerun one case by hand.
For exports, I compute the timestamp once, before the retry loop: a retry reuses the same task ids instead of exporting twice.
Never run two schedulers
This is the most expensive pitfall. When I replace an old pipeline with a new one (for example moving from a standalone repository into the monorepo):
flowchart TD
A["New pipeline created"] --> B["Created DISABLED"]
B --> C["Manual test on staging"]
C --> D["Old pipeline disabled"]
D --> E["New pipeline enabled"]
E --> F["Check the first scheduled run"]
F --> G["Old one moved to a retired folder"]
- A pipeline whose YAML contains
schedulesstarts firing as soon as it exists. So I create it disabled. - I disable the old one before enabling the new one, never the other way round.
- I write the rule at the top of the YAML ("never re-enable the old one") and move the old definition to an explicit folder.
- Azure DevOps has no pipeline archive: "disabled + moved + commented" stands in for it.
Real consequences of double scheduling: two exports of the same customer deleting each other's procedures, or two jobs wiping the files a downstream tool reads.
Release process
I separate two flows: the build on main (targets "current") and the release on a tag (versioned targets). An application release happens after the merge: changelog, YYYY.M.N tag, package deployment, and only then the documentation update. Pipelines that write to an external system or to production keep a dry-run default; the write is an explicit parameter.
Lessons learned
- A schedule defined in the YAML overrides the one in the UI: do not look for the time in two places.
- A manual-run parameter needs safe defaults, because the same definition serves the cron and the "Run" button.
- Set a
timeoutInMinutes: a stuck job must not hold an agent all night. - Publish logs as an artifact with
condition: always()so you can diagnose a failure the next day.