Applying DevOps to SQL Database development by Rodrigo Soares

Applying DevOps to SQL Database development by Rodrigo Soares

This talk will briefly cover:
- building a CI/CD pipeline with Azure DevOps according to the Pipeline-As-Code principle, taking advantage of some Azure features to break down the pipeline into separate code modules with clearly defined responsibilities;
- integrating Flyway Migrations in the said pipeline in order to automate the development & release process of SQL Server databases according to the Migrations Approach (as opposed to the State Approach);
- incorporating SonarCloud (code quality inspector) and tSQLt (unit test framework for SQL databases) in the pipeline, in order to explore the "QA" aspect of the traditional "DevOps = Dev + Ops + QA" relationship.

A2c14a1c4e16aa337c7d36abe7d1cf8f?s=128

DevOpsPorto

August 08, 2019
Tweet

Transcript

  1. Applying CI/CD & DevOps to SQL database development Implementing the

    Migrations Approach and the Pipeline-as-Code paradigm with Azure DevOps and Flyway Migration
  2. Overview CI-CD server/platform Database Automation Azure DevOps YAML Pipelines Unit

    Testing Code Quality
  3. Internship Problem Statement • “Automate the release process of our

    SQL Server databases, applying CI/CD processes. You have full freedom to propose the tools and technologies to use”
  4. Context Fintech company Microsoft SQL Server databases 2 dedicated SQL

    developers. Possibly more devs will contribute in the near future; Databases have a lot of business logic and programmable objects – Stored Procedures, Triggers, Functions, etc
  5. Problem: applying CI/CD to databases is not the same as

    apps Apps • Delete old version and install new version, for every environment in the CI/CD pipeline • Apps are like a set of chairs in a room – just replace with a new set Databases • Can’t simply delete old version and create a new one, for EVERY environment, would take too long • Databases are like buildings – can’t rebuild one every time you want to change something.
  6. Solution: apply migrations to the database, so it changes incrementally

  7. Problem: how should these migrations be generated and applied?

  8. Solution: choose between two approaches: “State” and “Migrations” State •

    a set of declarative SQL scripts describe how the database should be, with 1 script per database entity (e.g. Person.sql, Customer.sql, Account.sql, Address.sql) • A migration generator takes care of generating migrations for every change and for every environment, through state comparison Migrations • a set of imperative SQL scripts describe how the database should change • Migrations are hand-written by human developers, and a migration handler applies them across every environment, usually through meta-tables that keep track of already-applied migrations
  9. “State” vs “Migrations” State + Higher-level abstraction, making it friendlier

    for new developers and larger teams - Generator lacks context awareness, might generate an unexpected migration - No guarantee the databases across different environments will be in the same state Migrations + 100% reproducible deployments; every database is always in a known state, regardless of environment - Requires more communication and coordination between team members - Very inefficient for programmable objects (e.g. Stored Procedures, Functions, Triggers) http://workingwithdevs.com/delivering-databases-migrations-vs-state/
  10. None
  11. Available migration handlers that allow SQL developers to write in

    SQL Open-source Commercial Redgate SQL Change Automation
  12. Problem: how to automate these migrations?

  13. (One) Solution: Azure DevOps YAML Pipelines Classic GUI editor YAML

    Pipelines
  14. NOT talking about the classic GUI editor

  15. Talking about the new YAML functionality, 100% code

  16. So what are we trying to build?

  17. None
  18. None
  19. Azure DevOps YAML Templates Allows a pipeline to be written

    in YAML modules, possibly applying traditional software development practices and patterns
  20. Root file : “azure-pipelines.yml”

  21. “build-job.yml”

  22. What about Quality?

  23. “quality-steps.yml”

  24. “test-steps.yml”

  25. Issues with Azure DevOps’ YAML Pipelines • Very immature compared

    to more established competitors like Travis CI, Jenkins, or TeamCity, with critical features either missing or not consolidated in YAML version (most available on Classic Editor) • No Deployment Groups • No Manual Approvals (especially before deploying to Production) • No IDE, except for the web-based one, which only works for the root YAML file • YAML templates paths always start at the root of the repository. This severely compromises modularity https://devblogs.microsoft.com/devops/azure-devops-roadmap-update-for-2019-q3/ https://docs.microsoft.com/en-us/azure/devops/release-notes/features-timeline
  26. Verdict: Review it in 2020, lots of potential. Until then,

    either use Azure DevOps’ Classic Editor OR go with more established competitors
  27. Wanna see more? Play around with this at your own

    pace? • https://github.com/rodfontessoares/flyway-tsqlt-azure-demo
  28. Questions?

  29. See you next time, and thanks for playing