18
Road to database automation Database Source Control

SQLSaturday #485 Lisbon 2016 Database Source Control

Embed Size (px)

Citation preview

Page 1: SQLSaturday #485 Lisbon 2016 Database Source Control

Road to database automation

Database Source Control

Page 2: SQLSaturday #485 Lisbon 2016 Database Source Control

Nossos principais patrocinadores:

Page 3: SQLSaturday #485 Lisbon 2016 Database Source Control

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.

Page 4: SQLSaturday #485 Lisbon 2016 Database Source Control

Paulo Matos:

Page 5: SQLSaturday #485 Lisbon 2016 Database Source Control

Pedro Simões:

Page 6: SQLSaturday #485 Lisbon 2016 Database Source Control

Niko Neugebauer:

Page 7: SQLSaturday #485 Lisbon 2016 Database Source Control

2 Sponsor Sessions às 15:30

Crossjoin Solutions

XViewer for SQL Server DBA

Rumos

O valor das certificações para o

profissional

Page 8: SQLSaturday #485 Lisbon 2016 Database Source Control

About me

Eduardo Piairo

@Celfinet

Database Administrator

Infrastructure Manager

| 14.06.16 |8 Road to database automation

@EdPiairo

https://pt.linkedin.com/in/jesuspiairo

[email protected]

Page 9: SQLSaturday #485 Lisbon 2016 Database Source Control

Overview

Road to database automation9 | 14.06.16 |

Source

Control

Source

Control

Continuous

Integration

Continuous

Integration

Continuous

Delivery

Continuous

Delivery

How about databases?

Database

Source

Control

Database

Source

Control

Database

Continuous

Integration

Database

Continuous

Integration

Database

Continuous

Delivery

Database

Continuous

Delivery

Page 10: SQLSaturday #485 Lisbon 2016 Database Source Control

Overview

Road to database automation10 | 14.06.16 |

What is so special about databases?

Database

Source

Control

Database

Source

Control

Database

Continuous

Integration

Database

Continuous

Integration

Database

Continuous

Delivery

Database

Continuous

Delivery

You have to retain data!

Page 11: SQLSaturday #485 Lisbon 2016 Database Source Control

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 automation11 | 14.06.16 |

Page 12: SQLSaturday #485 Lisbon 2016 Database Source Control

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 automation12 | 14.06.16 |

Page 13: SQLSaturday #485 Lisbon 2016 Database Source Control

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 automation13 | 14.06.16 |

Page 14: SQLSaturday #485 Lisbon 2016 Database Source Control

Version Control System

• Rule 1: Script version (timestamp)

• Rule 2: Operation type

• Rule 3: Object type

• Rule 4: Object name

Road to database automation14 | 14.06.16 |

• Database scripts repository

• Flyway

Example:

V20160220.1100__Create_TB_MyTable.sql

Page 15: SQLSaturday #485 Lisbon 2016 Database Source Control

Version Control System

Road to database automation15 | 14.06.16 |

• Flyway (http://flywaydb.org/)

• Open source database migration tool

• My two reasons for adopting (Flyway Command-line):

• Simplicity

• Number of dependencies

Page 16: SQLSaturday #485 Lisbon 2016 Database Source Control

Version Control System

Road to database automation16 | 14.06.16 |

Page 17: SQLSaturday #485 Lisbon 2016 Database Source Control

Version Control System

Road to database automation17 | 14.06.16 |

• 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?

Page 18: SQLSaturday #485 Lisbon 2016 Database Source Control

The End…

| 14.06.16 |18 Road to database automation

@EdPiairo

https://pt.linkedin.com/in/jesuspiairo

[email protected]

CI + CD