Database Source Control: Migrations vs State

Talk presented @ 24HOP Portuguese 2016 (18-11-2016)

1. Database source control: Migrations vs State Eduardo Piairo
2. 2 About me Eduardo Piairo, DevOps Engineer @ Basecone 2 @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/
3. 3 Introduction 3 Source Control Continuous Integration Continuous Delivery Database + Application
4. 4 Introduction What’s so special about database? 4
5. 5 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 5
6. 6 Migrations vs State Fundamental resource: SQL Script 6
7. 7 Migrations vs State 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 7
8. 8 Flyway Open source database migration tool ▪ http://flywaydb.org/ Flyway command-line enabled ▪ Simplicity: easy to setup, no need to install ▪ Zero dependencies (java + jdbc) ▪ Scripts are written in SQL 8
9. 9 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 9
10. 10 Flyway – Version Control System Two components ▪ Database scripts repository ▪ Flyway – database migrations 10 ▪ Rule 1: Script version (timestamp) ▪ Rule 2: Operation type ▪ Rule 3: Object type ▪ Rule 4: Object name Example: V20160220.1100__Create_TB_MyTable.sql
11. 11 Flyway – Demo 11
12. 12 SQL Source Control Add-in for SSMS ▪ Links your database to your source control system Ability to source control schemas and reference data 12
13. 13 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
14. 14 SQL Source Control – Demo 14
15. 15 Migrations vs State Flyway + git ▪ Scripts are 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
16. 16 Migrations vs State Flyway + git ▪ SQL developers 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
17. 17 Migrations vs State Flyway + git ▪ Tracking changes ▪ Enables continuous integration and continuous delivery 17 SQL Source Control + git ▪ Drop and recreate advantage ▪ Tracking changes ▪ Enables continuous integration and continuous delivery
18. 18 Migrations vs State – Hotfix challenge Flyway + git ▪ 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
19. 19 Migrations vs State – Tools 19 Migrations State Flyway ReadyRoll SQL Source Control SSDT Database Projects DbUp
20. 20 Migrations vs State Which one is better? ▪ Size 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.
21. 21 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
22. 22 Reading material 22
23. Questions?
24. Thank You for Attending