Database Source Control: Migrations vs State

Talk presented @ SQLPort (22-09-2016)

1. DATABASE SOURCE CONTROL Eduardo Piairo Migrations vs State
2. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE •Eduardo Piairo •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
5. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Why database source control? • First step in your 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
6. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach • Fundamental resource: SQL Script
7. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based approach • State based solutions • Script represents 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
8. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway • Flyway – “Database migrations made easy” • http://flywaydb.org/ • Open source database migration tool • Flyway command-line enabled • Simplicity: easy to setup, no need to install • Zero dependencies (java + jdbc) • Scripts are written in SQL
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 • 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. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Flyway - Version Control System • Two components • Database scripts repository • Flyway – database migrations Example: V20160220.1100__Create_TB_MyTable.sql • Rule 1: Script version (timestamp) • Rule 2: Operation type • Rule 3: Object type • Rule 4: Object name
11. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – Demo
12. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control • SQL Source Control • Add-in for SSMS • Links your database to your source control system • Ability to source control schemas and reference data
13. 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
14. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control – Demo
15. 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
16. 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
17. 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
18. 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
19. 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.
20. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Links • Flyway • https://flywaydb.org/ • Redgate SQL Source Control • http://www.red-gate.com/products/sql-development/sql-source-control/ • Simple Talk - DLM Patterns and Practices • https://www.simple-talk.com/collections/database-lifecycle-management-patterns-practices-library/
21. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Reading Material
22. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/