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

Azure devops Logo

This post is a continuation of CI \ CD and Source Control SQL Server Using Azure Devops – Part 1 so I advise you to read that before continuing to get the bigger picture.

Applying Migrations and Finishing UP

The next Job is to apply the Migrations, this Job is broken down into Job Steps as shown in the below yaml code snippet:


Validate The Migrations and Apply

Before we can apply the Migrations we need to make sure the Migrations are named correctly, I will not discuss this here as I have already discussed this in the following post Version Control SQL Server and Azure DevOps


Finishing Up

We are now at our final Job in the Build Pipeline, this Job has a few steps that we will go over now:

Job Step – Script Out Migration State Table

The purpose of this Job step is to script out the data of the Migrations state tables, the reason for this is because we need to know about what the last migration(s) that where applied, we need this information for our release pipeline.

the Powershell script for this is shown below (ScriptOutDataFromMigrationStateTables.ps1):

Job Step – Extract Dac Pac

This is an inline command script, that extracts a dacpac from the Build DB that we created, again we will need this in our release pipeline.

Job Step – Create Git Commit Message File

We need to create a Git Commit Message file that we can in our artifact for our release pipeline, the reason for this is because, we need away of knowing what the git commit message was during the check in of the migrations so that we can publish this over to our schema repository.

the contents of this Powershell script is (CreateGitReleaseFile.ps1):

Job Step – Remove Build DB

This is a clean up step that removes the Build DB that we created earlier, the script for this is shown below:

Job Step – Publish Artifacts

The final section of our Build Pipeline is to publish the artifacts that we will need for our release pipeline.

In the next post I will discuss the Release Pipeline process.

Leave a Reply

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