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

More Decks by Eduardo Piairo

Other Decks in Technology

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/