Save 37% off PRO during our Black Friday Sale! »

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

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/