DB Orchestrator
Internal tool built at work. Details are kept general.
Moving a production tenant database from an on-prem SQL Server 2019 VM to Azure SQL Database is a long chain of steps, and any of them can fail halfway. DB Orchestrator turns that chain into a resumable state machine stored in a database, with an operator console so every migration is visible, auditable and restartable from the step that failed.
The pipeline
Created → StoppingWrites → BackingUpSource → RestoringToMiddleman → PrepScripts → ExportingBacpac → Importing → CreatingLoginsAndUsers → Verifying → ScalingDown → ReadyForCutover → Completed
Any step can drop into AwaitingMitigation, get fixed and resume from where it stopped. Cutover is always a deliberate manual step.
Design choices
- Write-stop gate: nothing runs until three independent checks agree the source database is safe to move: an external signal, a machine check that the database is write-free, and an operator’s approval.
- Selectable prep scripts: a manifest of prep scripts with dependency rules, validation for each script, and resume at the exact script that failed.
- Middleman fleet: the heavy lifting runs on dedicated VMs found by tag, each in its own Hybrid Worker group, one active job per machine. The steps are idempotent PowerShell 7 runbooks dispatched through Azure Automation.
- Zero stored credentials: managed identities, Key Vault and private endpoints throughout, in an EU region because the data includes personal information covered by GDPR.
- Operator-only access: sign-in through Microsoft Entra ID with a dedicated operator role.
- Runs without Azure: a full simulation mode runs the whole pipeline on mocks, including forced failures and resumes, so the logic can be tested with no cloud access.
Built in Python with a web console, backed by a signed-off acceptance contract and an architecture decision record.