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.

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