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

Azure devops Logo

The Release Pipeline

The release pipeline is only trigger when a successful build has been completed on the master branch, our release pipeline has 5 stages:

The first Stage is to create a staging schema only DB, and apply our migrations to it.  We need to create a staging schema only DB to be used as a kinda luanchpad to script out any new/updated databases objects and then apply them to source control using git.

the next stage is to apply the migrations to our QA environment, after this stage has been approved, we then apply to live production environment and apply to source control, the final stage is to perform a clean up and remove the staging db that we created in the first stage.

to make use of Variables I created them at the release pipeline level, these variables are called:

  • DB_Build_Password
  • DB_Build_User
  • DB_ServerInstance
  • DataFixMigrationsFolder
  • MigrationsFolder

 

Stage 1 – Apply To Staging

This stage imports the DacPac that we created during our Build pipeline process and created a Staging DB called DB-Staging-123 where 123 is the release id:

after the DacPac has been imported we then insert the Migration tables data back so that we know where to start the migration process of.

Stage 2 – Apply To QA

We then apply the migrations to our QA environment – you can we more about this process in the following post

Stage 3 – Apply To Production

This process is exactly the same as Stage 2.

Stage 4 – Stage Changes To Git Repo

This stage is the main workhorse of the release pipeline, this stage as the name suggests deals with pushing any changes made to the database into the schema source control repo.

it starts with first cloning the repo, this is an inline Powershell script:

The next task is to script out all the database objects:

This script looks complicated but essential what it’s doing is scripting out the database objects from the staging DB that was created,  it then writes the objects in correct order to Objects.txt file this is so that when re-creating a datbase from source control the objects are created in dependency order,  we then script our the Foreign Keys and check constraints separately, so that we can apply them back later on.  The reasons for doing this is I was getting conflict errors when re-creating the database so separating them out worked for me.

 

The next task is to copy this Objects.txt into our local git repo

next we script out the data in our Migrations state tables, we then copy this to our git repo, the script for copying this is as shown below:

Finally we then publish are DB-Schema repo back to Git:

 

That’s all, there is to it.  If you need help in moving your database systems over to Azure then get in contact with me.

 

Leave a Reply

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