Applying CI/CD & DevOps to SQL database development Implementing the Migrations Approach and the Pipeline-as-Code paradigm with Azure DevOps and Flyway Migration 

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

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” 

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 

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. 

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

Problem: how should these migrations be generated and applied? 

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 

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

No content

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

Problem: how to automate these migrations? 

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

NOT talking about the classic GUI editor 

Talking about the new YAML functionality, 100% code 

So what are we trying to build? 

No content

No content

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

Root file : “azure-pipelines.yml” 

“build-job.yml” 

What about Quality? 

“quality-steps.yml” 

“test-steps.yml” 

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 

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

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

Questions? 

See you next time, and thanks for playing