Delivering changes for databases and applications

Talk presented @ PortoData (28-07-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
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/