Version Control SQL Server and Azure DevOps

SQL Server Logo

Version Control SQL Server

This is going to be a rather lengthy post, it’s purpose is going to explain how I went about creating my own SQL server migration scripts using Powershell and Azure DevOps.  I needed a poor man’s solution without having to spend large amount of money on the industry leading software.   My solution is by no means perfect and there is probably/is a few flaws and bugs, however my intention is to show a concept that can be adapted and improved for production use cases.

I recommend you have knowledge of Azure DevOps and Powershell scripting.

Some Background

We have a small team of developers (4) and we all work for one company, currently we have no way of tracking SQL changes and data updates.   This was not a problem for us when we were small, however as the business has grown and more database development tasks arise we needed a way to track the changes from staging databases to our production databases.

Getting Started

I started to plan how this new system was going to work, I know that there would be at least two forms of migrations one for schema and the other for data fix and that each database would need two tables to keep track of  the different migrations.

File Naming conventions

 

I would create two folders/directories called DataFixMigrations and Migrations respectively, these folders would contain .sql files and would have the following naming format for a schema migration it would be 00001_M-NameOfMigration.sql.  Let us discuss this further the first part of the file name consists of 5 digits and is represented as a number so the next filename will be 00002, 00003 and so on.  ­_M- this represents that this file is a schema modification the M simply means migration. The last part of the file is simply the name of the migration for example you could have the name CreatedNewViewForSalesCalledSalesByYear2020.

For the Data Fix migrations the naming convention is similar except that these files have ­_DFM-  and should a data fix only apply to a certain company brand such as an update statement the format is this _DFM[BRAND-ID] BRAND-ID would be replaced with that companies brand initials.

 

Database Tables

 

For this system to work correctly each database needs to have two tables to keep track of the migrations and data fix migrations.  I used the following T-SQL to create both tables onto each database in question.

Migrations Table:

Data Fix Migrations Table:

Git Source Control

I proceeded to create a new Git project in Azure DevOps and initialized the new repository and set out to pull this down to my local development PC. Once done I added the two folders that I created (DataFixMigrations and Migrations) into the folder under source control.

Azure Pipelines

For this to work with Azure CI I needed to create a build pipeline and the code with this is shown below, most of it is self-explanatory.

The Powershell Scripts

Now the fun part creating the scripts that will be used by Azure DevOps CI, the first script that I created a validation script for the migrations. A validation script will be needed for the data fix migrations as well.  The validation script purpose is to ensure that the files are named correctly, are in sequential order and that we have no duplicate filenames.  Please don’t copy and paste my code it will not work for you again the purpose of this post is to explain how I achieved such a system and the code that I show can be modified, used to work with your setup.

Migrations Validation Script:

Once the validation script has past it’s test, we can then execute the script and insert the migration script into the migrations table.  The script that will achieve this is shown below.

Migrations Script:

The process is the same for the DataFix Migrations, the code can be downloaded from https://github.com/packet3/sqlSourceControl

That’s all,  if you need my help then please get in touch.

Leave a Reply

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