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

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 •

    @ 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/