CI \ CD and Source Control SQL Server Using Azure Devops – Part 1

Azure devops Logo

Hi All,

It’s been a while since my last post (I seem to keep saying that rather often!) I have been very busy with other projects and work.  Anyhow without further ado I would like to explain the process of how I went about using Azure Devops with a self-hosted agent to have a medium sized SQL Server  database to be under source control using Git and to allow for the use of CI \ CD (Continuous Integration and Continuous Delivery).  This post carries on from Version Control SQL Server and Azure DevOps with improvements, I urge you to read that one before continuing with this as I will not be going over the background in this one.

What I am going to be covering in this post is the following:

  • The Azure Build Pipeline Code
  • The Release Pipeline Code
  • The Different Git Repos
  • The Updated and Improved Powershell Scripts
  • The Workflow Process

The Workflow Process

Project Objectives

This is what the project objective needed to achieve (Summarized overview):

  • The Developer Using VSCode creates T-SQL scripts that contain either DDL or DML statements or both, They then check these into the Develop Branch.
  • The Develop branch will initiate the Build Pipeline, this Build Pipeline will have a number of steps that will spin up a schema only database, apply the developers sql scripts, then drop this schema only database.
  • If the Develop build was successful the developer can then merge into master where the process in the above step will start again.
  • The Release pipeline will trigger, spin up a schema only staging database apply the developers sql changes, then apply changes to QA database, await approval then script out changes from the staging schema only database to check into Git Source Control and then  apply to production DB’s.

The 3 amigos… (3 Git repositories)

This project used 3 Git repos one is used for the developers to work from and this repository holds the Migrations (DB-Migrations), the second repository is the schema repository this holds all the scripted out database objects  of the database that we want to be under source control (DB-Schema), the final repository holds all of the Powershell scripts needed (DB-Scripts).

The Build Pipeline Process

Lets start our journey by starting with a discussion of the Build Pipeline, below is the Yaml script that I used.

Build Pipeline Header

Let me break this down and explain each area so you get a better idea as to what is going on. The below section shows the header of the pipeline, I am asking Devops to prepare to checkout two additional git repos that will be needed later on in the pipeline process.  The trigger means to only run this when a check in occurs on the develop branch or master branch, finally I am declaring a single variable called buildid, and for this build pipeline to use the self-hosted agent on a server called DevSQL.


Create Build DB

The first Job is to Create the Build DB which is a schema only DB and has the naming format: “DB-Build-123  where 123 is the build id of this build. the content of this Powershell script is (CreateBuildDB.ps1):

Once the build db has been created we now need to script out the Objects into this database, the Powershell script for this looks like (ScriptOutBuildDB.ps1):

Although the script looks complicated what is essentially happening here is using an Objects.txt file recreate all the database objects in order and apply these changes to are current Build DB that we created earlier and then apply some ad-hoc tasks near the end of the script.


The next Job Step is InsertMigrationsStateData what this step does is to re-insert the Migrations state table with what ever current Migrations have already been applied, we need this because lets say a 1000 migrations have already been applied previously.  We don’t want to have to re-apply these all again to the build DB we only want to start from the point where current Migrations for this build need to apply. The below shows the content of this Powershell script (InsertStateMigrationData.ps1):

To Avoid this post getting lengthy I have created a Part 2 (possibly many other parts to come ) to continue our discussion, Part 2 Is Here

Leave a Reply

Your email address will not be published. Required fields are marked *