Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Enabling Continuous Delivery with Database Practices - Pramod Sadalage - Agile SG 2016

Enabling Continuous Delivery with Database Practices - Pramod Sadalage - Agile SG 2016

Presented in Agile Singapore 2016 conference

66a1bb94b08fe5dcd07635a59681626c?s=128

Agile Singapore

October 06, 2016
Tweet

Transcript

  1. Enabling Continuous Delivery with Database Practices Pramod Sadalage @pramodsadalage ThoughtWorks

    Inc.
  2. Agenda Why Continuous Delivery Collaboration with the data team Automation

    for data Refactoring databases Deploying database changes
  3. Why Continuous Delivery?

  4. To get fast feedback from users, release frequently

  5. Reduce risk of releasing

  6. Learning and responding to customer needs is critical

  7. Achieve Continuous Delivery

  8. Close collaboration between everyone involved in delivery

  9. Extensive automation of all parts involved in delivery process

  10. Reduce the gap between development and operations

  11. CD should involve the database team

  12. How does continuous delivery apply to databases?

  13. Collaboration techniques

  14. Pair the DBA’s and Developers

  15. Version control all database scripts and artifacts

  16. Automation techniques

  17. Let developers provision application database

  18. None
  19. None
  20. √ × Jai Andy Laptop/Desktop Application Database

  21. Continuously Integrate database changes

  22. Integration environment Local dev environment ANT Maven Gradle Rake CI

    Server Source Control Dev Database PROD UAT QA Dev DB PROD UAT QA Environment Check in application code and database migration scripts Update and build Migration scripts (Dev/DBA) Apply migration scripts Migration scripts Artifacts Package Migration scripts War Jar Apply Migration scripts War Jar Integration Database Database changes applied by DBA Team
  23. Benefits of CI with databases Test application code and database

    at one place Generate code and database artifacts Integrate application and database changes in an independent environment Show current state of application and database to all
  24. DB artifacts in CI application artifact for build <<n>> database

    artifact for build <<n>>
  25. Tracking Changes Each change is a delta/migration script Migration scripts

    are development time activity not deployment time tasks Package migration scripts for automated deployment Same scripts for: developers, QA, UAT and Production
  26. Tracking Changes

  27. Deployment Database migration/upgrade should be a development time task not

    deployment time task Package all the migration scripts, during Continuous Integration cycle Apply the migration scripts Deploy frequently to reduce risk
  28. Database refactoring http://databaserefactoring.com/

  29. A database refactoring improves its design while retaining both its

    behavioral and informational semantics. A database refactoring is a small change to your database schema (the DDL, data, and DB code) which improves its design without changing its semantics.
  30. None
  31. Timeline of Change Transition Transition Period (old and new) {

    Start Implement the refactoring { Deploy new changes, migrate data, put in scaffolding code Expand End Refactoring completed { Remove old schema, scaffolding code Contract
  32. Keeping Old and New alive DB Should be able to

    handle multiple versions of the application Create Interfaces in the database Wrap tables with views Create calculated columns Create triggers to sync data
  33. Expand Contract an example Start name = “Pramod Sadalage” Expand

    name = “Pramod Sadalage” firstname = “Pramod” lastname = “Sadalage” Contract firstname = “Pramod” lastname = “Sadalage”
  34. Expand More on expand contract Start name = “Pramod Sadalage”

    Contract firstname = “Pramod” lastname = “Sadalage” Without data migration name = “Pramod Sadalage” firstname = null lastname = null With data migration name = “Pramod Sadalage” firstname = “Pramod” lastname = “Sadalage”
  35. Simple scenario - DBDeploy ALTER TABLE Customer ADD firstname VARCHAR2(60);

    ALTER TABLE Customer ADD lastname VARCHAR2(60); --//@UNDO ALTER TABLE Customer DROP COLUMN firstname VARCHAR2(60); ALTER TABLE Customer DROP COLUMN lastname VARCHAR2(60);
  36. With synchronized data ALTER TABLE Customer ADD firstname VARCHAR2(60); ALTER

    TABLE Customer ADD lastname VARCHAR2(60); CREATE OR REPLACE TRIGGER SynchronizeName BEFORE INSERT OR UPDATE ON Customer REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.Name IS NULL THEN :NEW.Name := :NEW.firstname||' '||:NEW.lastname; END IF; IF :NEW.name IS NOT NULL THEN :NEW.firstname := extractfirstname(:NEW.name); :NEW.lastname := extractlastname(:NEW.name); END IF; END; / —//@UNDO ……
  37. Migrate and Synchronize data ALTER TABLE Customer ADD firstname VARCHAR2(60);

    ALTER TABLE Customer ADD lastname VARCHAR2(60); UPDATE Customer set firstname = extractfirstname (name); UPDATE Customer set lastname = extractlastname (name); CREATE OR REPLACE TRIGGER SynchronizeName BEFORE INSERT OR UPDATE …. —//@UNDO …… UPDATE Customer set name = firstname ||’ ‘||lastname WHERE name IS NULL; ALTER TABLE Customer DROP COLUMN firstname; ALTER TABLE Customer DROP COLUMN lastname;
  38. Contract ALTER TABLE Customer DROP COLUMN name; —//@UNDO ALTER TABLE

    Customer ADD name VARCHAR2(120); UPDATE Customer set name = firstname ||’ ‘||lastname WHERE name IS NULL; SET UNUSED name; When drop takes forever
  39. Keep legacy apps happy ALTER TABLE Customer DROP COLUMN name;

    ALTER TABLE CUSTOMER ADD (name AS (generatename (firstname,lastname)) ); —//@UNDO ALTER TABEL Customer DROP COLUMN name; ALTER TABLE Customer ADD name VARCHAR2(120); UPDATE Customer set name = firstname ||’ ‘||lastname WHERE name IS NULL; Virtual column in Oracle, Generated Column in MySQL.
  40. Another example

  41. Migration script ALTER TABLE custordr rename to customerorder; CREATE OR

    REPLACE VIEW custordr AS SELECT custordrid, ponumber, ordrdt, shipdate, sptoadid FROM customerorder ; --//@UNDO DROP VIEW custordr; ALTER TABLE customerorder RENAME TO custordr;
  42. data in migrations INSERT INTO businessunit ( businessunitid, name, regionid

    ) VALUES ( 22, 'John Doe Services', 1 ); INSERT INTO businessunit ( businessunitid, name, regionid ) VALUES ( 23, 'Bob Products', 1 ); INSERT INTO businessunit ( businessunitid, name, regionid ) VALUES ( 24, 'Carr Machinery', 1 ); INSERT INTO currency ( currencyid, name, code ) VALUES ( 2, 'Canadian Dollar', 'CAD' ); INSERT INTO currency ( currencyid, name, code ) VALUES ( 3, 'Australian Dollar', 'AUD' ); INSERT INTO currency ( currencyid, name, code ) VALUES ( 4, 'EMU Euro', 'EUR' ); DELETE FROM contact ct WHERE NOT EXISTS (SELECT 1 FROM customer p WHERE ct.contactid=p.contactid) and EXISTS (SELECT 1 FROM (SELECT customerid, COUNT(*) FROM contact WHERE customerid IS NOT NULL GROUP BY customerid HAVING COUNT(*) >1) ct2 WHERE ct.customerid=ct2.customerid);
  43. Tips Large refactorings are risky Sequence of many small refactorings

    can create the desired change Migration scripts should be checked in and run on local dev/ci/qa/uat/prod etc. Changes to data are also migrations
  44. Continuous Delivery ant -propertyfile qa.properties upgrade ant -propertyfile live.properties upgrade

    Deployment should be easy
  45. Thanks @pramodsadalage sadalage.com databaserefactoring.com devopsfordba.com