OPERATIONS FOR DATABASES Eduardo Piairo @EdPiairo #commitporto The DevOps Journey 

ABOUT ME Operations for databases – The DevOps journey @EdPiairo, #commitporto @EdPiairo https://pt.linkedin.com/in/jesuspiairo [email protected] http://www.eduardopiairo.com/ Eduardo Piairo DevOps Coach @ DevOps Porto Founder 

THIS PRESENTATION Agile journey Scrum Kanban DevOps Operations for databases – The DevOps journey Database operations Automation Source Control Continuous Integration Continuous Delivery The definition and the interpretation of the covered concepts are attached to the cultural context of a specific organization on a certain period! “Context is everything” @EdPiairo, #commitporto 

AGILE JOURNEY 1.Before Scrum 2.Scrum 3.Kanban 4.Scrum + Kanban Operations for databases – The DevOps journey @EdPiairo, #commitporto 

AGILE JOURNEY 1.Before Scrum 2.Scrum 3.Kanban 4.Scrum + Kanban Operations for databases – The DevOps journey @EdPiairo, #commitporto 

BEFORE SCRUM Operations for databases – The DevOps journey • Before Scrum - The “real agile” method • Centralized decision mechanism • Growing phase • Size (5 to 15) • Complexity (more and more components) @EdPiairo, #commitporto 

BEFORE SCRUM Operations for databases – The DevOps journey Application #1 Application #2 Application #3 Shared database(s) “Team” #1 “Team” #2 “Team” #3 “Team” #T-SQL @EdPiairo, #commitporto 

AGILE JOURNEY 1.Before Scrum 2.Scrum 3.Kanban 4.Scrum + Kanban Operations for databases – The DevOps journey @EdPiairo, #commitporto 

SCRUM Operations for databases – The DevOps journey • Scrum - The “magic” scrum • Scrum for the masses (>20) • Pure Scrum approach – operations as development team • 2 week sprint, sprint goal • The team was “too reactive” @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE BEGINNING Operations for databases – The DevOps journey Application #1 Application #2 Application #3 Shared database(s) Team #1 Team #2 Team #3 Team #T-SQL @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – BUILDING A DEPLOYMENT PIPELINE Operations for databases – The DevOps journey Source Control Continuous Integration Continuous Delivery Database + Application @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE SPECIAL CASE OF THE DATABASES What’s so special about databases? Operations for databases – The DevOps journey @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – PROBLEMS TO FIX Operations for databases – The DevOps journey • Databases are out of pace with application development • Synchronization between development and DBA teams • No traceability of database changes (changes history) • What changed? Who? When? Why? • Manual databases processes prevent the CI and CD utilization in their full extent • Time consuming and error prone • Releases are less frequent and risky @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – PROBLEMS TO FIX Operations for databases – The DevOps journey • Bugs in production environment • Database related bugs are only discovered after deployment to production • Manual or inexistent tests • Fixes and hotfixes have time cost, what can lead to delay a release • High dependency in a person (database specialist) • Fear of making changes @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – PROBLEMS TO FIX Operations for databases – The DevOps journey Databases became a bottleneck in an agile delivery process @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE SOLUTION Operations for databases – The DevOps journey Source Control Continuous Integration Continuous Delivery Automation + Change control @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE SOLUTION Decision #1 Let’s automate Let’s do source control Operations for databases – The DevOps journey @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE VALUE OF AUTOMATION Operations for databases – The DevOps journey • Enable control over database development • Increase speed of response to change • Keep a versioned “history” of database states • Greater reliability of the release process • Remove/reduce human intervention @EdPiairo, #commitporto From The art of deployment to The engineering of deployment 

DEVOPS FOR DATABASES – THE VALUE OF AUTOMATION Operations for databases – The DevOps journey Fearless database changes @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE 1ST STEP: SOURCE CONTROL Operations for databases – The DevOps journey • 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, #commitporto SQL Script 

DEVOPS FOR DATABASES – THE 1ST STEP: SOURCE CONTROL Decision #2 Database source control: Migrations vs State Operations for databases – The DevOps journey @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – MIGRATIONS VS STATE Operations for databases – The DevOps journey • State based solutions • Script represents the current database state • Your source of truth is how the database should be • Migrations based solutions • Script represents a migration • Your source of truth is how the database should change @EdPiairo, #commitporto Migration Migration State Delta 

DEVOPS FOR DATABASES – COLLABORATION THROUGH A CONTRACT Operations for databases – The DevOps journey • Set of rules and expectations • Define responsibility frontiers • Sets a common language Application #1 Application #2 Application #3 Shared database(s) Team #1 T-SQL Script Team #2 Team #3 Team #T-SQL @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – RULES / GUIDELINES Operations for databases – The DevOps journey • Scripting rules • Rule 1: Script version (timestamp) • Rule 2: Operation type • Rule 3: Object type • Rule 4: Object name Example: V20160220.1100__Create_TB_MyTable.sql @EdPiairo, #commitporto • Small batches (One script, one operation type, one object) • Merge conflicts management • Patterns identification • File system scripts history search 

DEVOPS FOR DATABASES - THE PIPELINE Operations for databases – The DevOps journey • Contract – communication, collaboration mechanism for change management • Don’t lie to the pipeline • Everything is negotiable in the contract, except its application @EdPiairo, #commitporto 

AGILE JOURNEY 1.Before Scrum 2.Scrum 3.Kanban 4.Scrum + Kanban Operations for databases – The DevOps journey @EdPiairo, #commitporto 

KANBAN Operations for databases – The DevOps journey • Kanban 101 • Focus on development teams necessities • 4 week iterations • A bad choice • Different “language” from other teams • Desynchronization between operations and development teams @EdPiairo, #commitporto 

DEVOPS FOR DATABASES – THE PIPELINE Operations for databases – The DevOps journey • Change description (Source Control) • Change validation (Continuous Integration) • Change implementation (Continuous Delivery) @EdPiairo, #commitporto 

AGILE JOURNEY 1.Before Scrum 2.Scrum 3.Kanban 4.Scrum + Kanban Operations for databases – The DevOps journey @EdPiairo, #commitporto 

SCRUM + KANBAN Operations for databases – The DevOps journey • Scrum + Kanban – The best of two worlds • 2 week sprint, sprint goal • Task definition was based on teams needs and our needs • Team capacity < 70% (enough bandwidth to react) • Strong and disciplined team • Integration in solutions design @EdPiairo, #commitporto 

DEVOPS EVERYWHERE Operations for databases – The DevOps journey • Applications • Databases • Infrastructure @EdPiairo, #commitporto 

WHY DEVOPS? Operations for databases – The DevOps journey • Developing software is not enough, you have to deliver it • You can not stop change, but you can control it • Different intervenients need to communication and collaborate • CAMS • We change the culture (with lot of negotiation and evangelization) • We invested in automation (started with 0 automation in a never ending automation journey) • We conquer work flow visibility, so we were able to start measure • We spread the contract across the organization @EdPiairo, #commitporto 

THE DEVOPS WAY Operations for databases – The DevOps journey @EdPiairo, #commitporto • DevOps way • Increase flow visibility • Increase feedback • Increase knowledge base 

SOME METRICS/RESULTS Operations for databases – The DevOps journey • We went from 100% manual databases changes to about 98% automatic changes (100% controlled changes) • On average 200 scripts were created per day • In one year we achieved 10k scripts • We went from supporting only 1costumer to 4 costumers at the same time • Each week, costumer requests were delivered • Fixes were applied whenever necessary @EdPiairo, #commitporto 

Q&A Operations for databases – The DevOps journey @EdPiairo https://pt.linkedin.com/in/jesuspiairo [email protected] http://www.eduardopiairo.com/ @EdPiairo, #commitporto