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

Deployment pipeline for databases (Azure SQL Database, SQL Server)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=47 Eduardo Piairo
September 21, 2017

Deployment pipeline for databases (Azure SQL Database, SQL Server)

Talk presented @ WinOps London 2017 (21-09-2017)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

September 21, 2017
Tweet

Transcript

  1. DEPLOYMENT PIPELINE FOR DATABASES Eduardo Piairo @EdPiairo #WinOps Azure SQL

    Database, SQL Server
  2. ABOUT ME Deployment pipeline for databases @EdPiairo, #WinOps @EdPiairo https://pt.linkedin.com/in/jesuspiairo

    eduardopiairo@gmail.com http://www.eduardopiairo.com/ Eduardo Piairo Friend of Redgate Operations Engineer DevOps Porto Founder
  3. INDEX 1. Deployment pipeline 2. Database challenges 3. Deployment pipeline

    stages 4. Deployment pipeline scenarios 5. Final thoughts Deployment pipeline for databases @EdPiairo, #WinOps
  4. PIPELINE DEFINITION In software engineering, a pipeline consists of a

    chain of processing elements (processes, threads, coroutines, functions, etc.), arranged so that the output of each element is the input of the next. by Wikipedia @ https://en.wikipedia.org/wiki/Pipeline_(software) Deployment pipeline for databases @EdPiairo, #WinOps
  5. DEPLOYMENT PIPELINE DEFINITION A deployment pipeline should enable collaboration between

    the various groups involved in delivering software and provide everyone visibility about the flow of changes in the system, together with a thorough audit trail. by Martin Fowler @ https://martinfowler.com/bliki/DeploymentPipeline.html Deployment pipeline for databases @EdPiairo, #WinOps
  6. DEPLOYMENT PIPELINE STAGES Deployment pipeline for databases Stage 1 –

    Change description (Source Control) Stage 2 – Change validation (Continuous Integration) Stage 3 – Change implementation (Continuous Delivery) Source Control Continuous Integration Continuous Delivery @EdPiairo, #WinOps
  7. DATABASE & DEPLOYMENT PIPELINE Deployment pipeline for databases Source Control

    Continuous Integration Continuous Delivery Database + Application @EdPiairo, #WinOps
  8. WHAT’S SO SPECIAL ABOUT DATABASES? Deployment pipeline for databases @EdPiairo,

    #WinOps
  9. DATABASE CHALLENGES Deployment pipeline for databases • Database and application

    changes (most of the time) are handled differently • Different deployment pipeline • Synchronization needed • Database is not included (manual work) @EdPiairo, #WinOps
  10. DATABASE CHALLENGES Deployment pipeline for databases • Manual work …

    • Lack of traceability of database changes (changes history) • It’s the most expensive and risky work type • Prevent CI and CD utilization in their full extent • Promote the fear of changes @EdPiairo, #WinOps
  11. DATABASE CHALLENGES Deployment pipeline for databases Databases become a bottleneck

    in an agile delivery process @EdPiairo, #WinOps
  12. AUTOMATION Deployment pipeline for databases @EdPiairo, #WinOps

  13. THE VALUE OF AUTOMATION Deployment pipeline for databases • Enable

    control over database development • Increase speed of response to change • Greater reliability of the release process • Remove/reduce human intervention in the release process From (Re)Learn and forget it to Improve and forget it @EdPiairo, #WinOps
  14. THE VALUE OF AUTOMATION Deployment pipeline for databases Fearless database

    changes @EdPiairo, #WinOps
  15. SOURCE CONTROL Deployment pipeline for databases @EdPiairo, #WinOps SQL Script

    Fundamental resource
  16. SOURCE CONTROL Deployment pipeline for databases • First step in

    your database deployment pipeline • Traceability through change history • SQL as documentation • Shared code-base and shared process • Enforceable standards to reduce conflicts @EdPiairo, #WinOps
  17. • State based solutions • How the database should be

    • Migrations based solutions • How the database should change MIGRATIONS VS STATE Deployment pipeline for databases SQL Source Control @EdPiairo, #WinOps Migration Migration State Delta
  18. SCRIPTING GUIDELINES Deployment pipeline for databases • One script, one

    operation type, one object (small batches) • Merge conflicts management • Patterns identification • File system scripts history search @EdPiairo, #WinOps
  19. CONTINOUS INTEGRATION Deployment pipeline for databases • Integrate and validate

    changes • Tests: Unit, Integration • Small batches, less risk • Considerations: • What should be tested? And When? • Can the application help on this? @EdPiairo, #WinOps
  20. CONTINUOUS DELIVERY Deployment pipeline for databases • Delivering change(s) in

    the target environment • Considerations • Downtime • Time to recover • Small batches – low risk • Affected applications @EdPiairo, #WinOps
  21. FAILED DEPLOYMENT Deployment pipeline for databases • Rollback/roll forward scripts

    • Very fast • Please, do not make mistakes • It’s like working backwards • Backups (before deploy) • Safer but slower • Keep databases small • Avoid share databases between applications @EdPiairo, #WinOps
  22. SCENARIO #1 Deployment pipeline for databases Source Control Continuous Integration

    Continuous Delivery APP DB @EdPiairo, #WinOps • Independent deployment pipelines • High need of synchronization between DBA and development team
  23. SCENARIO #2 Deployment pipeline for databases Source Control Continuous Integration

    Continuous Delivery APP DB @EdPiairo, #WinOps • Different code repositories • Independent CI process • The contact point will be the deployment moment
  24. SCENARIO #3 Deployment pipeline for databases Source Control Continuous Integration

    Continuous Delivery APP DB @EdPiairo, #WinOps • Different code repositories • Connected/dependent CI process
  25. SCENARIO #4 Deployment pipeline for databases Source Control Continuous Integration

    Continuous Delivery APP DB @EdPiairo, #WinOps • Share the some code repository, CI process and CD process • No need of synchronization • Promotes learning within teams (database code reviews)
  26. DEMO Deployment pipeline for databases @EdPiairo, #WinOps

  27. MY DEPLOYMENT PIPELINE DEFINITION Pipeline: Cultural and technical tool for

    managing changes in the software development process (should contemplate databases, applications and infrastructure). by me ;) Show me your pipeline and I will tell you the way you work! Deployment pipeline for databases @EdPiairo, #WinOps
  28. DEVOPS AND DATABASES Deployment pipeline for databases • DevOps way

    • Increase flow visibility • Increase feedback • Increase knowledge base @EdPiairo, #WinOps
  29. READING MATERIAL Deployment pipeline for databases @EdPiairo, #WinOps

  30. Q&A Deployment pipeline for databases @EdPiairo, #WinOps @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com

    http://www.eduardopiairo.com/