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 Lisbon 2016 (20-02-2016)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

February 20, 2016
Tweet

Transcript

  1. Road to database automation Database Source Control

  2. Nossos principais patrocinadores:

  3. Agradeçam aos Voluntários: ▪ Eles gastam o seu tempo livre

    para organizarem este evento. ▪ Porque eles são simplesmente loucos. ☺ ▪ Eles querem que TU aprendas com os melhores oradores a nível nacional.
  4. Paulo Matos:

  5. Pedro Simões:

  6. Niko Neugebauer:

  7. 2 Sponsor Sessions às 15:30 ▪ Crossjoin Solutions ▪ XViewer

    for SQL Server DBA ▪ Rumos ▪ O valor das certificações para o profissional
  8. About me ▪ Eduardo Piairo ▪ @Celfinet ▪ Database Administrator

    ▪ Infrastructure Manager | 17.11.17 | 8 Road to database automation @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com
  9. Overview Road to database automation 9 | 17.11.17 | Source

    Control Continuous Integration Continuous Delivery How about databases? Database Source Control Database Continuous Integration Database Continuous Delivery
  10. Overview Road to database automation 10 | 17.11.17 | What

    is so special about databases? Database Source Control Database Continuous Integration Database Continuous Delivery You have to retain data!
  11. Database automation ▪ 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? Who? When? When? ▪ Manual database processes prevent the CI and CD in their full extent ▪ Your process has the strength of your weakest step ▪ Time consuming and error prone ▪ Bugs in production environment ▪ Manual tests or inexistent tests Road to database automation 11 | 17.11.17 |
  12. Database source control • First step for database automation •

    Database deployment pipeline • Keep history of each change • Traceability • Your best comunication system • Share code (Sql Scripts) • Enforce standards • Eliminate/reduce conflicts Road to database automation 12 | 17.11.17 |
  13. Migrations vs State • Fundamental resource: SQL script • Two

    aproaches • Migrations-based • Script represents a migration • Migration represents the next database version • State-based • Script represents the current database state Road to database automation 13 | 17.11.17 |
  14. Version Control System • Rule 1: Script version (timestamp) •

    Rule 2: Operation type • Rule 3: Object type • Rule 4: Object name Road to database automation 14 | 17.11.17 | • Database scripts repository • Flyway Example: V20160220.1100__Create_TB_MyTable.sql
  15. Version Control System Road to database automation 15 | 17.11.17

    | • Flyway (http://flywaydb.org/) • Open source database migration tool • My two reasons for adopting (Flyway Command-line): • Simplicity • Number of dependencies
  16. Version Control System Road to database automation 16 | 17.11.17

    |
  17. Version Control System Road to database automation 17 | 17.11.17

    | • Challenges: 1. Manager multiple branches ▪ Manage merge/interests conflicts 2. Script expiration date ▪ Sometimes, in the next day the migration script makes no sense anymore 3. Transition period (deprecation period) ▪ Both schemas, original and new, are supported 4. Migration scripts stack ▪ Dependencies between different databases ▪ Dependencies between applications/components 5. Developer sandbox ▪ How to supply a disposable development environment where the database is included?
  18. The End… | 17.11.17 | 18 Road to database automation

    @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@outlook.com CI + CD