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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [dbo].[_migrations]( [id] [int] IDENTITY(1,1) NOT NULL, [migration_index] [nvarchar](10) NOT NULL, [migration_filename] [nvarchar](70) NOT NULL, [migration_datetime] [datetime] NOT NULL CONSTRAINT [uq_unique_migration_index] UNIQUE NONCLUSTERED ( [migration_index] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[_migrations] ADD CONSTRAINT [dft_getdate_migrationdatetime] DEFAULT (getdate()) FOR [migration_datetime] GO |
Data Fix Migrations Table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[_datafix-migrations]( [id] [int] IDENTITY(1,1) NOT NULL, [datafix_migration_index] [nvarchar](16) NOT NULL, [datafix_migration_filename] [nvarchar](70) NOT NULL, [datafix_migration_datetime] [datetime] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[_datafix-migrations] ADD CONSTRAINT [dft_getdate_datafix-migrationdatetime] DEFAULT (getdate()) FOR [datafix_migration_datetime] GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
# Starter pipeline # Start with a minimal pipeline that you can customize to build and deploy your code. # Add steps that build, run tests, deploy, and more: # https://aka.ms/yaml trigger: - master variables: #Setup variables #database password i needed to escape $ with backtick databaseUsername: username databasePassword: 'password' databaseServerName: devsql pool: name: DevSQL jobs: - job: RunMigrationValidationsScript variables: MigrationsFolder: 'ProjectFiles\Migrations' steps: - task: PowerShell@2 inputs: arguments: -MigrationsFolder $(MigrationsFolder) filePath: '$(System.DefaultWorkingDirectory)/ProjectFiles/validationScript.ps1' - job: RunDataFixValidationsScript variables: DataFixMigrationsFolder: 'ProjectFiles\DataFixMigrations' steps: - task: PowerShell@2 inputs: arguments: -DataFixMigrationsFolder $(DataFixMigrationsFolder) filePath: '$(System.DefaultWorkingDirectory)/ProjectFiles/datafix_validationScript.ps1' - job: RunMigrationsScript variables: MigrationsFolder: 'ProjectFiles\Migrations' dependsOn: RunMigrationValidationsScript condition: succeeded('RunMigrationValidationsScript') steps: - task: PowerShell@2 inputs: arguments: -databaseUsername $(databaseUsername) -databasePassword $(databasePassword) -databaseServerName $(databaseServerName) -MigrationsFolder $(MigrationsFolder) filePath: '$(System.DefaultWorkingDirectory)/ProjectFiles/migrations.ps1' - job: RunDataFixMigrationsScript variables: DataFixMigrationsFolder: 'ProjectFiles\DataFixMigrations' dependsOn: RunDataFixValidationsScript condition: succeeded('RunDataFixValidationsScript') steps: - task: PowerShell@2 inputs: arguments: -databaseUsername $(databaseUsername) -databasePassword $(databasePassword) -databaseServerName $(databaseServerName) -DataFixMigrationsFolder $(DataFixMigrationsFolder) filePath: '$(System.DefaultWorkingDirectory)/ProjectFiles/datafix_migrations.ps1' - job: BuildMigrationArtifacts dependsOn: RunMigrationsScript condition: succeeded('RunMigrationsScript') steps: - script: echo $(System.DefaultWorkingDirectory)! - task: PublishBuildArtifacts@1 inputs: PathtoPublish: '$(System.DefaultWorkingDirectory)/ProjectFiles' ArtifactName: 'AzureDB-Dev Migrations' publishLocation: 'Container' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
param( $MigrationsFolder ) #get migrations files $migrationScripts = Get-ChildItem "$MigrationsFolder" -Filter *.sql $scriptCount = $migrationScripts.count $lastIndex = 0 $migrationScripts | ForEach-Object { $fileName = [System.IO.Path]::GetFileName($_.FullName) #lets check to make sure that the file starts with 5 numbers followed by an underscore followed by capital M and then dash: 00005_M- #if not fail the build #Test 1 - Do we have a correct FileName format? #^([0-9]{5})_([MBU])-(.*)\.sql$ -> This will match 00003_M-sdfsdf.sql but not 8494jjd8j4thisisM.sql $return = $fileName -match '^([0-9]{5})_([M])-(.*)\.sql$' #Write-Output $return if($return) { #Test 2 - Is the file sequential named? $migrationName = $fileName.Split("_") $migrationIndex = $migrationName[0] $indextest = $migrationIndex.trimstart('0') -as [int] if($lastIndex -gt 0) { if($indextest -eq $lastindex ) { #exit writeout duplicate error here Write-Output "##vso[task.LogIssue type=error;]There is already a filename with this name: $fileName" exit 1 } if($indextest -gt ($lastindex + 1) ) { #exit writeout gap error here Write-Output "##vso[task.LogIssue type=error;]Mismatch in sequntial file we have $scriptCount migration scripts but one file was named grater than the count the file is $fileName" exit 1 } } $lastIndex = $indextest } else { Write-Output "##vso[task.LogIssue type=error;]Incorrect file naming format: $fileName the format should be as an example 00005_M-filename.sql" exit 1 } } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
param( $databaseUsername, $databasePassword, $databaseServerName, $MigrationsFolder ) Get-ChildItem "$MigrationsFolder" -Filter *.sql | ForEach-Object { $fileName = [System.IO.Path]::GetFileName($_.FullName) $migrationName = $fileName.Split("-") $migrationIndex = $migrationName[0] #Write-Host = "The database server is $databaseServerName using the username $databaseUsername and the password of $databasePassword " #Write-Host = $migrationIndex #Check migrations table for Migration Index, if exists dont execute the migration #if not execute the migration #'$migrationIndex' $results = Invoke-Sqlcmd -ServerInstance $databaseServerName -query "SELECT migration_index FROM _migrations WHERE migration_index = '$migrationIndex'" -u $databaseUsername -p $databasePassword -Database BarsDB-Dev -ErrorAction 'Stop' if ($results.count -eq 0) { Write-Host = "No Migration Found with Migration Index: $migrationIndex" Write-Host = "Added Migration to _Migrations table" #Execute the SQL Script in question Invoke-Sqlcmd -ServerInstance $databaseServerName -InputFile "$MigrationsFolder\$fileName" -u $databaseUsername -p $databasePassword -Database BarsDB-Dev -ErrorAction 'Stop' #Insert SQL statement $insertQuery =" INSERT INTO [dbo].[_migrations] ([migration_index], [migration_filename]) VALUES('$migrationIndex', '$fileName') GO " #Write-Host = $insertQuery Invoke-Sqlcmd -ServerInstance $databaseServerName -query $insertQuery -u $databaseUsername -p $databasePassword -Database BarsDB-Dev -ErrorAction 'Stop' } } |
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.
