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

Delivering changes for databases and applications

Delivering changes for databases and applications

Talk presented @ PortoData (28-07-2016)


Eduardo Piairo

July 28, 2016


  1. Delivering changes for applications and databases “The beginning of a

    beautiful friendship” Eduardo Piairo Miguel Alho
  2. Delivering changes for applications and databases @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/

    Eduardo Piairo, DBA & DevOps @ Celfinet
  3. Delivering changes for applications and databases @MytyMyky https://pt.linkedin.com/in/miguelalho-982331 alho@miguelalho.com http://www.miguelalho.pt/

    Miguel Alho, Software Engineer @ Faro Europe
  4. @DevOpsPorto Is Coming

  5. Delivering changes for applications and databases Source Control Continuous Integration

    Continuous Delivery Database + Application
  6. What’s so special about the database?

  7. What’s so special about the database?

  8. DLM – Database Lifecycle Management Data management and data migration

    Data monitoring Data recovery http://assets.red-gate.com/products/dlm/DLM_tools_and_processes_diagram.pdf
  9. Typical problems we want to fix • Databases are out

    of pace with application development • Lack of traceability of database changes (changes history) • Manual databases processes prevent the CI and CD utilization • Lack of testability • Database setup time for new environments • Bugs in production
  10. Typical problems we want to fix Databases becoming a bottleneck

    in an agile delivery process
  11. Typical problems we want to fix Databases becoming an easy

    target to blame
  12. None
  13. Why database automation? • Enable control over database development •

    Increase speed of response to change • Keep a versioned “history” of database states and changes • Reliability of the release process • Increase release frequency though repeatability of processes • Reduce time spent fixing bugs - automated tests • Remove/reduce human intervention in the release process
  14. Why database automation? Fearless database changes

  15. 1st step: Source Control • First step in your database

    deployment pipeline • Traceability through change history • SQL as documentation • Shared code-base and shared process • Enforceable standards to reduce conflicts
  16. 1st Step: Source Control Fundamental resource: SQL Script

  17. 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
  18. 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
  19. Flyway – How does it work? • 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
  20. Version Control System Two components: Database scripts repository Flyway –

    database migrations Rule 1: Script version (timestamp) Rule 2: Operation type Rule 3: Object type Rule 4: Object name Example: V20160728.1100__Create_TB_MyTable.sql
  21. Demo

  22. How about applications? It takes two to tango

  23. Database and Application change alignment • Database schemas should evolve

    along with application evolution ◦ deploy app => deploy changes / migrations • (Integration) Test app and db changes together ◦ should pass tests while developing, ◦ should pass tests while promoting to new environments • Ideally, one application per database • Pipeline deployment only!
  24. Integration test at the database layer • Use TDD techniques

    • Limit database integration tests to data layer ◦ faster tests! ◦ validate sql and mappings • Always test with the latest schema Database Application Database Application
  25. Demo https://github.com/MiguelAlho/Purchases-DbMigration-sample

  26. What we just saw http://assets.red-gate.com/products/dlm/DLM_tools_and_processes_diagram.pdf

  27. Database and Application change alignment Fearless database changes

  28. Challenges • Challenge #1: managing multiple branches • Manage merge

    conflicts • The default branch is the main/master branch and all the merges for this branch must be done with caution
  29. Challenges • Challenge #2: Script expiration date • Should the

    migration script have an expiration date? • Sometimes, in the next day, the migration script no longer makes sense
  30. Challenges • Challenge #3: migration scripts stack • How can

    I manage the dependencies between different databases?
  31. Challenges • Challenge #4: developer sandbox • How to supply

    a disposable development environment where the database is included?
  32. What’s Next?

  33. Links • Demo application “Purchases-DbMigration-sample” • https://github.com/MiguelAlho/Purchases-DbMigration-sample • Flyway •

    https://flywaydb.org/ • DbUp • http://dbup.github.io/ • Octopus Deploy • https://octopus.com/ • http://docs.octopusdeploy.com/display/OD/Deploying+ASP.NET+Core+Web+Applications • Visual Studio • https://www.visualstudio.com/
  34. Links • Redgate • http://www.red-gate.com/ • http://www.red-gate.com/products/sql-development/sql-source-control/ • http://www.red-gate.com/products/sql-development/sql-compare/ •

    http://www.red-gate.com/products/sql-development/sql-data-compare/ • http://www.red-gate.com/products/dlm/dlm-automation/ • http://www.red-gate.com/products/dlm/dlm-dashboard • http://www.red-gate.com/solutions/dlm/ • Simple Talk - DLM Patterns and Practices • https://www.simple-talk.com/collections/database-lifecycle-management-patterns-practices-library/
  35. Reading material

  36. Delivering changes for applications and databases @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/

    @MytyMyky https://pt.linkedin.com/in/miguelalho-982331 alho@miguelalho.com http://www.miguelalho.pt/