FireDAC SchemaUpdate vs Manual Migration Scripts: Zero‑Downtime Upgrade Trade‑off

Goal

Upgrade a small Delphi application while keeping the database online and avoiding any service interruption.

Constraints and Uncertainty

FireDAC’s SchemaUpdate property can align the database schema with the in‑memory data model at runtime, but the operation is not fully transactional and may silently alter or drop objects if the model diverges from the actual database. Manual SQL migration scripts, executed via ExecuteDirect, provide deterministic, versioned changes and allow scheduling during maintenance windows, yet they require additional build steps and thorough testing to prevent deployment delays.

Unresolved Decision

Which approach best balances rapid deployment against controlled, auditable schema evolution for a zero‑downtime upgrade scenario?

Key Questions

• Under what database conditions does FireDAC’s SchemaUpdate guarantee atomic DDL application without leaving the schema in an inconsistent state?
• What mechanisms can be employed to detect and mitigate silent data loss when the in‑memory model differs from the live database schema during automatic updates?
• When using manual migration scripts, what safeguards should be in place to avoid overlapping DDL operations with concurrent application traffic?