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

Road to database automation: database source control

Road to database automation: database source control

Talk presented @ PortoData (15-12-2015)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

December 15, 2015
Tweet

More Decks by Eduardo Piairo

Other Decks in Technology

Transcript

  1. ROAD TO DATABASE AUTOMATION Eduardo Piairo Database Source Control

  2. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – DATABASE SOURCE CONTROL Overview Source

    Control Continuous Integration Continuous Delivery How about databases?
  4. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 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
  7. 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
  8. 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
  9. 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
  10. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Source control

    systems • TFVC (Visual Studio) • Mercurial (TortoiseHg) • Git (Source Tree)
  11. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 DATABASE AUTOMATION – DATABASE SOURCE CONTROL Flyway –

    How it works? • Flyway commands (6) • migrate • clean • info • validate • baseline • repair
  16. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 DATABASE AUTOMATION – DATABASE SOURCE CONTROL Demo

  19. ROAD TO DATABASE AUTOMATION – DATABASE SOURCE CONTROL Version Control

    System – The system • Two components • Database scripts repository • Flyway – database migrations
  20. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – 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 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
  23. ROAD TO DATABASE AUTOMATION – 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 DATABASE AUTOMATION – DATABASE SOURCE CONTROL Q&A @EdPiairo

    https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/