LLI Wiki — My Digital Knowledge Base

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

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"]
  1. A pipeline whose YAML contains schedules starts firing as soon as it exists. So I create it disabled.
  2. I disable the old one before enabling the new one, never the other way round.
  3. 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.
  4. 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

Mastodon LLIMastodon SocialFosstodon