Articles in the series
About the series
Perhaps the hardest part of your software stack to integrate into a CI/CD (Continuous Integration/Continuous Deploy) chain is your database objects. Now Microsoft has made some great strides in this, and its actually not that bad at all if you happen to use all the Microsoft best practices, and have a simple use case, ie:
- You’re using Agile, Scrum, Kanban, etc. on a closed source project with a single production instance.
- A private git repo with a .NET Core MVC App and an OLTP database preferably hosted on GitHub or Azure DevOps (formerly Visual Studio Team Services online).
- Schema managed by a SSDT Project in Visual Studio
- Any ORMs are not generating schema, and developers are manually handling creating models (C# classes that map to the RDBMS tables).
- All code coverage and unit tests reports will be viewed in Visual Studio or from the Azure DevOps Dashboard.
- Build Pipeline Creating a DACPAC in Azure DevOps
- Release Pipeline does a full DB backup and Deploys a DACPAC in Azure DevOps
- Release pipeline failure plan is to restore DB backup and rerun last good release
- Every merged PR gets deployed to dev
- Manual gates for Production Deploys
- Database deployed to Azure SQL DB PaaS
If you stay within these lines, and you don’t have any schema or data changes that require a specific migration strategy that present a problem for SSDT, then things are easy.
However, reality doesn’t always match this ideal, especially if the DBAs were brought in after the fact. Even if DBAs were brought in you could be in a shop that doesn’t bleed Microsoft Blue. Or you could simple be overruled on something. Heck, you could even be a DBA that doesn’t want to do some of the things on that list.
Perhaps your reality, for whatever reason looks like the following:
- This is an open source project.
- This project is a demo for training, conference talks, user group talks, and blog articles, so you need many different artefacts produced.
- Your Git repo is a complex mono repo or contains submodule with projects in different languages
- You’re main web app is .NET 4.6.1 with MVC 5 and EF 6.2
- You’re using a third party CI/CD tool such as AppVeyor
- You’re using other third party services in your CI/CD chain such as codecov.io.
- Part of your schema is from a Code First EF6 application
- Your build pipelines is creating several artifacts
- Website deploy zip file
- Nuget Packages for some of the DLLs
- Noget Packages of Powershell Modules
- SQL DACPAC
- SQL Schema Generation Script
- Backup of database Unit test were run against.
- Code Coverage and unit test reports in HTML format and junit
- Merges to master trigger a github release and all generated artifacts are sent there.
I happen to have such a project, the AlwaysEncrypted sample application I wrote around the release of SQL Server 2016 that I’ve recently dusted off to improve my DevOps skills. I’ve decided to document some of the steps in my journey along the way as a series of articles on this blog.
About the sample project
While I’m no fan of code first ORMs, I wanted this app to demonstrate AlwaysEncrypted “just worked” even if your app wasn’t built around it. Instead of Azure DevOps, I went with AppVeyor because its free, and since its more simple than Azure DevOps build pipelines, and configured by YAML, I can illustrate the how and why better than “here is a GUI step in Azure DevOps”
zippy1981inexilium 