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

Database Source Control: Migrations vs State

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=47 Eduardo Piairo
September 22, 2016

Database Source Control: Migrations vs State

Talk presented @ SQLPort (22-09-2016)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

September 22, 2016
Tweet

Transcript

  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/