Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database Source Control: Migrations vs State

Database Source Control: Migrations vs State

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

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

November 18, 2016
Tweet

Transcript

  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