Road to database automation: database source control

Talk presented @ SQLSaturday Porto 2016 (01-10-2016)

Transcript

1. Road To Database Automation Database Source Control Eduardo Piairo
2. Our Main Sponsors:
3. 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
4. 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
5. Database Source Control Eduardo Piairo DBA & DevOps About me @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/
6. Database Source Control Overview Source Control Continuous Integration Continuous Delivery Database + Application
7. Database Source Control What’s so special about databases? Overview
8. 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. 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. 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. 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. 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-based approach vs State-based approach Fundamental resource: SQL Script
14. 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
15. 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
16. Database Source Control Demo
17. 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
18. 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
19. Database Source Control Demo
20. 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. 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. Database Source Control What’s next?
23. Database Source Control Q&A @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com http://www.eduardopiairo.com/