Road to database automation: database source control

Talk presented @ TugaIT 2016 (21-05-2016)

Transcript

1. TUGA IT 2016 LISBON, PORTUGAL
2. THANK YOU TO OUR SPONSORS
3. THANK YOU TO OUR TEAM ANDRÉ BATISTA ANDRÉ MELANCIA ANDRÉ VALA ANTÓNIO LOURENÇO BRUNO LOPES CLÁUDIO SILVA NIKO NEUGEBAUER RUI REIS RICARDO CABRAL NUNO CANCELO PAULO MATOS PEDRO SIMÕES SANDRA MORGADO SANDRO PEREIRA RUI BASTOS NUNO ÁRIAS SILVA
4. ROAD TO DATABASE AUTOMATION Eduardo Piairo Database Source Control
5. 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/
6. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Overview Source Control Continuous Integration Continuous Delivery Database + Application
7. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE What’s so special about databases? Overview DLM – Database Lifecycle Management • Data management and data migration • Data monitoring • Data recovery
8. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Motivation • Database are out of pace with application development • Need of synchronization between development and DBA teams • No traceability of database changes (changes history) • What changed? Why? When? Who? • Manual databases processes prevent the CI and CD utilization in their full extent • Database release process is manual • Time consuming and error prone • Releases are less frequent and risky
9. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Motivation • Tests are manual or do not exists • Bugs in production environment • Database related bugs are only discovered after deployment to production • Fixes and hotfixes have time cost, what can lead to delay a release • Inability to roll back to the previous version of a database • Database setup time of a new environment • Databases become a bottleneck in agile delivery processes
10. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Why database automation? • Enable control over database development • Keep a versioned “history” of database sates • Increase release frequency though repeatability of processes • Greater predictability over releases • Efficiency by eliminating the repetition of a range of manual activities • Reduce time spent fixing bugs • More control, less exposure to error
11. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Why database automation? • Faster speed of response to change • Automated deployment of smaller units of change • Remove/reduce human intervention in the release process • The build step is automatic triggered by a “push” into source control repository • The deploy step is automatic triggered by a successfully build process • Greater reliability of the release process • Providing a series of automated test stages prior to deployment • Maintain an agile development environment
12. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Why database source control? • First step for database automation • Share code (SQL scripts) • Keep history of each change • Your best comunication system • Enforce standards
13. 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 • 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
14. 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
15. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Demo
16. 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
17. 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
18. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Version Control System – The 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
19. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Demo
20. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Version Control System – Challenges • Challenge 1 – manage multiple branches • In other words, is necessary to mange merge/interests conflicts • The default branch is the main/master branch and all the merges for this branch must be done with caution • Challenge 2 – script expiration date • Should the migration script have expiration date? • Sometimes, in the next day the migration script makes no sense anymore
21. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Version Control System – Challenges • Challenge 3 – migration scripts stack • How can I manage the dependencies between different databases? • And how about dependencies between applications/components • Challenge 4 – developer sandbox • How to supply a disposable development environment where the database is included?
22. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL What’s next?
23. DATABASE SOURCE CONTROL– MIGRATIONS VS STATE Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/