database deployment pipeline ▪ Traceability through change history ▪ Shared code-base and shared process ▪ SQL as documentation ▪ Enforce standards to reduce conflits ▪ Your best communication system 5
the current database state ▪ Your source of truth is how the database should be Migrations based solutions ▪ Script represents a migration ▪ Migration represents how to transition to the next database version ▪ Your source of truth is how the database should change 7
clean info, validate, baseline repair Metadata table ▪ Used to track the state of the database ▪ If the database is empty, flyway won’t find it and will create it ▪ Flyway scans the migrations directory and check migrations against the metadata table ▪ Migrations are sorted based on their version number and applied in order 9
▪ Compares Git repository (your source of truth) against the target database Schema and data reference ▪ Database objects ▪ Static data ▪ Referential integrity (keys and constraints) Roll back changes Lock objects Apply filters to database objects 13
imperative ▪ Each script/change is applied, in order, on the target database ▪ High level of granularity 15 SQL Source Control + git ▪ Scripts are declarative ▪ Compares two states (source and target) and generates a change script ▪ The big picture
define change scripts upfront and have full control over how changes are deployed ▪ Scripts must be idempotent and immutable (runs 1 time only) ▪ “Changes will only be made through migration tool itself?” 16 SQL Source Control + git ▪ Comparison engine – SQL Compare (schema and reference data) ▪ Dependencies management ▪ SQL Server Management Studio
▪ Ignore the hotfix ▪ Deployment can fail or pass ▪ You will never find out, until you have a problem 18 SQL Source Control + git ▪ Roll back the hotfix ▪ Target database would look like the version in development that did not have the fix
of the database ▪ Complexity of the database ▪ Team’s capabilities or preferences ▪ Team’s development processes 20 Both approaches may be needed for different parts of the system, or at different times in the development process.