$30 off During Our Annual Pro Sale. View Details »

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.

DevOpsPorto

August 08, 2019
Tweet

More Decks by DevOpsPorto

Other Decks in Technology

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

    View Slide

  2. Overview
    CI-CD server/platform Database Automation
    Azure DevOps
    YAML Pipelines
    Unit Testing Code Quality

    View Slide

  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”

    View Slide

  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

    View Slide

  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.

    View Slide

  6. Solution: apply migrations to the database, so
    it changes incrementally

    View Slide

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

    View Slide

  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

    View Slide

  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/

    View Slide

  10. View Slide

  11. Available migration handlers that allow SQL
    developers to write in SQL
    Open-source Commercial
    Redgate SQL Change Automation

    View Slide

  12. Problem: how to automate these
    migrations?

    View Slide

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

    View Slide

  14. NOT talking about the classic GUI editor

    View Slide

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

    View Slide

  16. So what are we trying to build?

    View Slide

  17. View Slide

  18. View Slide

  19. Azure DevOps YAML Templates
    Allows a pipeline to be written in YAML modules, possibly applying traditional
    software development practices and patterns

    View Slide

  20. Root file : “azure-pipelines.yml”

    View Slide

  21. “build-job.yml”

    View Slide

  22. What about Quality?

    View Slide

  23. “quality-steps.yml”

    View Slide

  24. “test-steps.yml”

    View Slide

  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

    View Slide

  26. Verdict:
    Review it in 2020, lots of potential. Until then, either use Azure
    DevOps’ Classic Editor OR go with more established competitors

    View Slide

  27. Wanna see more? Play around with this at
    your own pace?
    • https://github.com/rodfontessoares/flyway-tsqlt-azure-demo

    View Slide

  28. Questions?

    View Slide

  29. See you next time, and thanks for
    playing

    View Slide