Road to database automation: database source control

Talk presented @ SQLSaturday Lisbon 2016 (20-02-2016)

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