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

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.


August 08, 2019

More Decks by DevOpsPorto

Other Decks in Technology


  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. 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”
  3. 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
  4. 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.
  5. 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
  6. “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/
  7. Available migration handlers that allow SQL developers to write in

    SQL Open-source Commercial Redgate SQL Change Automation
  8. Azure DevOps YAML Templates Allows a pipeline to be written

    in YAML modules, possibly applying traditional software development practices and patterns
  9. 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
  10. Verdict: Review it in 2020, lots of potential. Until then,

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

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