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 @ SQLSaturday Porto 2016 (01-10-2016)

Eduardo Piairo

October 01, 2016

More Decks by Eduardo Piairo

Other Decks in Technology


  1. Say Thank you to Volunteers: ▪ They spend their FREE

    time to give you this event. ▪ Because they are crazy. ☺ ▪ Because they want YOU to learn from the BEST IN THE WORLD. João Sarmento Eduardo Piairo Diamantino Falcão Nuno Rafael Filipe Coelho
  2. Sponsor Sessions at 15:05 ▪ Don’t miss them, they might

    be getting distributing some awesome prizes! Auditório Magno Auditório 1 Exciting certification changes with SQL Server 2016 Culto de BI Successful projects
  3. Database Source Control Eduardo Piairo DBA & DevOps About me

    @EdPiairo https://pt.linkedin.com/in/jesuspiairo [email protected] http://www.eduardopiairo.com/
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. Database Source Control 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
  10. Database Source Control Flyway Flyway – “Database migrations made easy”

    http://flywaydb.org/ Open source database migration tool Flyway command-line Simplicity: easy to setup, no need to install Zero dependencies (java + jdbc) Scripts are written in SQL
  11. Database Source Control Flyway – How it works? 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
  12. Database Source Control Version Control System – The system Two

    components: Database scripts repository Flyway – database migrations Example: V20161001.1100__Create_TB_MyTable.sql Rule 1: Script version (timestamp) Rule 2: Operation type Rule 3: Object type Rule 4: Object name
  13. 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
  14. 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?