Download as PDF, PPTX
Road to database automation - Database source control
- 1. ROAD TO DATABASEAUTOMATION Eduardo Piairo Database Source Control
- 2. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL • Eduardo Piairo • @ Celfinet • DBA • DevOps About me @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/
- 3. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Overview Source Control Continuous Integration Continuous Delivery How about databases?
- 4. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL What’s so special about databases? Overview DLM – Database Lifecycle Management • Data management and data migration • Data monitoring • Data recovery
- 5. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Motivation • Database are out of pace with application development • No traceability of database changes (changes history) • 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
- 6. ROAD TO DATABASEAUTOMATION – 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
- 7. ROAD TO DATABASEAUTOMATION – 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
- 8. ROAD TO DATABASEAUTOMATION – 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
- 9. ROAD TO DATABASEAUTOMATION – 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
- 10. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Source control systems • TFVC (Visual Studio) • Mercurial (TortoiseHg) • Git (Source Tree)
- 11. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL 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 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
- 12. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Flyway • Flyway – “Database migrations made easy” • http://flywaydb.org/ • Open source database migration tool • My two reasons for adopting Flyway (command-line) • Simplicity • Dependencies
- 13. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Flyway – Why database migrations? • The code side • Version control is now universal with better tools everyday • We have reproducible builds and continuous integration • We have well defined release and deployment processes • The database side • What state is the database in on this machine? • Has this script already been applied or not? • Has the quick fix in production been applied in test afterwards • How do you set up a new database instance?
- 14. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL 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
- 15. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Flyway – How it works? • Flyway commands (6) • migrate • clean • info • validate • baseline • repair
- 16. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Flyway – How it works? • 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
- 17. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Flyway – How it works? • Metadata table • Flyway scans the migrations directory (java/sql) • Migrations are checked against the metadata table • Migrations are sorted based on their version number and applied in order
- 18. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Demo
- 19. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Version Control System – The system • Two components • Database scripts repository • Flyway – database migrations
- 20. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Version Control System – Scripting rules • Rule 1: Script version (timestamp) • Rule 2: Operation type • Rule 3: Object type • Rule 4: Object name • Advice: database objects naming convention Vyyyymmdd.hhmm__OperationType_ObjectType_ObjectName.sql • Example • V20151215.1900__Create_TB_MyTable
- 21. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Version Control System – Refactoring databases • Simple change to a database schema that improves its design while retaining its behavioral and informational semantics • Transition Period (Deprecation Period) • Both schemas (original and new) are supported • Refactoring categories • Structural refactoring • Data quality refactoring • Architectural refactoring • Etc. How good are you doing T-SQL code?
- 22. ROAD TO DATABASEAUTOMATION – 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
- 23. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Version Control System – Challenges • Challenge 3 – migration scripts stack • In other words, is necessary to mange merge/interests conflicts • 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?
- 24. ROAD TO DATABASEAUTOMATION – DATABASE SOURCE CONTROL Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/