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
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/
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?
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