Database Source Control: Migrations vs State

Talk presented @ PortoData (20-04-2016)

1. DATABASE SOURCE CONTROL Eduardo Piairo Migrations vs State
2. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE • Eduardo Piairo • @ Celfinet • DBA • DevOps About me @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/
3. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Introduction Source Control Continuous Integration Continuous Delivery Database + Application
4. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE What’s so special about databases? Introduction DLM – Database Lifecycle Management • Data management and data migration • Data monitoring • Data recovery
5. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Why database source control? • First step for database automation • Share code (SQL scripts) • Keep history of each change • Your best communication system
6. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach • Fundamental resource - SQL Script • State based solutions • Script represents the current database state • Migrations based solutions • Script represents a migration • Migration represents how to transition to the next database version State: Your source of truth is how the database should be Migrations: Your source of truth is how the database should change Alex Yates, Pre-sales engineer at Redgate
7. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway • Flyway – “Database migrations made easy” • http://flywaydb.org/ • Open source database migration tool • My reasons for adopting Flyway (command-line) • Simplicity: easy to setup, no need to install • Zero dependencies (java + jdbc) • Scripts are written in SQL
8. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – Why database migrations? • Migrations • Recreate a database from scratch • Make it clear at all time what state a database is in • Migrate in a deterministic way from your current version of the database to a newer one
9. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – How it works? • Flyway commands • Migrate, clean info, validate, baseline repair • Metadata table • Used to track the state of the database • Default name: “schema_version” • 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
10. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – Demo
11. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control • SQL Source Control • Add-in for Management Studio that links your database to your source control system • Ability to source control schemas and reference data • Source control systems: Git, Mercurial, TFS , Subversion
12. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control – How it works • SQL Compare • 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. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control – Demo
14. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach • Scripts are imperative • Each script/change is applied, in order, on the target database • High level of granularity • SQL developers define change scripts upfront and have full control over how changes are deployed Flyway + git • Scripts are declarative • Compares two states (source and target) and generates a change script • The big picture • Dependencies management • Comparison engine – SQL Compare (schema and reference data) SQL Source Control + git
15. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach Flyway + git SQL Source Control + git • Scripts must be idempotent and immutable (runs 1 time only) • “Changes will only be made through migration tool itself?” • Tracking changes • Enables continuous integration and continuous delivery • SQL Server Management Studio • Drop and recreate advantage • Tracking changes • Enables continuous integration and continuous delivery
16. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach – HOTFIX challenge Flyway + git SQL Source Control + git • Ignore the hotfix • Deployment can fail or pass • You will never find out, until you have a problem • Roll back the hotfix • Target database would look like the version in development that did not have the fix
17. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach – Tools Migrations State Flyway ReadyRoll SQL Source Control SSDT Database Projects DbUp
18. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach • Which one is better? • Size of the database • Complexity of the database • Team’s capabilities or preferences • Team’s development processes Both approaches may be needed for different parts of the system, or at different times in the development process.
19. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/