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 @ PortoData (20-04-2016)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

April 20, 2016
Tweet

Transcript

  1. DATABASE SOURCE CONTROL Eduardo Piairo Migrations vs State

  2. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE • Eduardo Piairo •

    @ Celfinet • 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 DLM – Database Lifecycle Management • Data management and data migration • Data monitoring • Data recovery
  5. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Why database source control?

    • First step for database automation • Share code (SQL scripts) • Keep history of each change • Your best communication system
  6. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Migrations-based approach vs State-based

    approach • Fundamental resource - SQL Script • State based solutions • Script represents the current database state • Migrations based solutions • Script represents a migration • Migration represents how to transition to the next database version State: Your source of truth is how the database should be Migrations: Your source of truth is how the database should change Alex Yates, Pre-sales engineer at Redgate
  7. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway • Flyway –

    “Database migrations made easy” • http://flywaydb.org/ • Open source database migration tool • My reasons for adopting Flyway (command-line) • Simplicity: easy to setup, no need to install • Zero dependencies (java + jdbc) • Scripts are written in SQL
  8. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – Why database

    migrations? • Migrations • Recreate a database from scratch • Make it clear at all time what state a database is in • Migrate in a deterministic way from your current version of the database to a newer one
  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 • Default name: “schema_version” • 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. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Flyway – Demo

  11. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control •

    SQL Source Control • Add-in for Management Studio that links your database to your source control system • Ability to source control schemas and reference data • Source control systems: Git, Mercurial, TFS , Subversion
  12. 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
  13. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE SQL Source Control –

    Demo
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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.
  19. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com

    http://www.eduardopiairo.com/