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.
|
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
resources: repositories: - repository: DB-Schema type: git name: DB-Schema - repository: DB-Scripts type: git name: DB-Scripts trigger: - develop - master variables: ##Setup variables buildid: $(Build.BuildId) pool: name: DevSQL jobs: - job: Create_Build_DB steps: - checkout: self - checkout: DB-Scripts - checkout: DB-Schema - task: PowerShell@2 name: CreateBuildDB displayName: Create Build DB env: DB_BUILD_PASSWORD: $(DB_Build_Password) inputs: filePath: '$(Build.SourcesDirectory)/DB-Scripts/ScriptsToCreateBuildDB/CreateBuildDB.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ScriptOutDatabaseObjects displayName: Script Out Database Objects inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Schema filePath: '$(Build.SourcesDirectory)/DB-Scripts/ScriptsToCreateBuildDB/ScriptOutBuildDB.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: InsertMigrationsStateData displayName: InsertMigrationsStateData inputs: filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/InsertStateMigrationData.ps1' - job: ApplyingMirations dependsOn: Create_Build_DB condition: succeeded('Create_Build_DB') steps: - checkout: self - checkout: DB-Scripts - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ValidateMigrations displayName: Validate Migrations inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Migrations filePath: '$(Build.SourcesDirectory)/DB-Scripts/Validations/MigrationValidationScript.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) displayName: Applying Migrations inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Migrations filePath: '$(Build.SourcesDirectory)/DB-Scripts/Migrations/Build_and_Staging/migrations.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ValidateDataFixMigrations displayName: Validate DataFix Migrations inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Migrations filePath: '$(Build.SourcesDirectory)/DB-Scripts/Validations/DatafixValidationScript.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ApplyingDataFixMigrations displayName: Applying DataFix Migrations inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Migrations filePath: '$(Build.SourcesDirectory)/DB-Scripts/Migrations/Build_and_Staging/DatafixMigrations.ps1' - job: FinalisingTasks dependsOn: ApplyingMirations condition: succeeded('ApplyingMirations') steps: - checkout: self - checkout: DB-Scripts - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ScriptOutMigrationStateTable displayName: Script Out Migration State Table inputs: arguments: -OutputLocationForScriptDataFiles $(System.DefaultWorkingDirectory) filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/ScriptOutDataFromMigrationStateTables.ps1' - task: CmdLine@2 name: ExtractDacPac displayName: Extract Dac Pac inputs: script: sqlpackage.exe /action:Extract /targetfile:$(System.DefaultWorkingDirectory)/BuildDB-$(Build.BuildId).dacpac /SourceUser:$(DB_Build_User) /SourcePassword:$(DB_Build_Password) /SourceServerName:$(DB_ServerInstance) /SourceDatabaseName:DB-Build-$(Build.BuildId)" - task: PowerShell@2 name: CreateTempGitCommitMessageFile displayName: Create Git Commit Message File inputs: arguments: -GitCommitMessageFile $(System.DefaultWorkingDirectory) filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/CreateGitReleaseFile.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: RemoveBuildDB displayName: Remove Build DB inputs: filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/DropBuildDB.ps1' - task: PowerShell@2 name: CopyNeededScriptsForReleasePipeline displayName: Copy Needed Scripts For Release Pipeline inputs: arguments: -fileSourceLocation $(Build.SourcesDirectory)/DB-Scripts -fileOutputLocation $(System.DefaultWorkingDirectory) filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/CopyScriptsIntoArtifactNeededForReleasePipeline.ps1' - task: PublishPipelineArtifact@1 name: PublishArtifacts displayName: Publish Artifacts inputs: targetPath: $(System.DefaultWorkingDirectory) artifactName: DBSourceControl |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
resources: repositories: - repository: DB-Schema type: git name: DB-Schema - repository: DB-Scripts type: git name: DB-Scripts trigger: - develop - master variables: ##Setup variables buildid: $(Build.BuildId) pool: name: DevSQL |
Create Build DB
|
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 |
jobs: - job: Create_Build_DB steps: - checkout: self - checkout: DB-Scripts - checkout: DB-Schema - task: PowerShell@2 name: CreateBuildDB displayName: Create Build DB env: DB_BUILD_PASSWORD: $(DB_Build_Password) inputs: filePath: '$(Build.SourcesDirectory)/DB-Scripts/ScriptsToCreateBuildDB/CreateBuildDB.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: ScriptOutDatabaseObjects displayName: Script Out Database Objects inputs: arguments: -WorkingDirectory $(Build.SourcesDirectory)/DB-Schema filePath: '$(Build.SourcesDirectory)/DB-Scripts/ScriptsToCreateBuildDB/ScriptOutBuildDB.ps1' - task: PowerShell@2 env: DB_BUILD_PASSWORD: $(DB_Build_Password) name: InsertMigrationsStateData displayName: InsertMigrationsStateData inputs: filePath: '$(Build.SourcesDirectory)/DB-Scripts/Utils/InsertStateMigrationData.ps1' |
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):
|
1 2 3 4 5 6 7 8 9 10 11 |
Write-Host "##[section] Creating Build Database: DB-Build-$env:BUILD_BUILDID" $buildid = $env:BUILD_BUILDID $username = $env:DB_BUILD_USER $password = $env:DB_BUILD_PASSWORD $instance = $env:DB_SERVERINSTANCE #Create the database $sql = "CREATE DATABASE [DB-Build-$buildid]" invoke-sqlcmd -Query $sql -u $username -p $password -serverinstance $instance |
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):
|
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
param ( $WorkingDirectory ) #Load the assembly [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null function CreateStagingDB{ $BuildOrderFileForDatabaseObjects = "$WorkingDirectory/Objects.txt" $database = "DB-Build-$env:BUILD_BUILDID" $dbCheckWorkingDirectory = "$WorkingDirectory\Check" $dbForeignKeyWorkingDirectory = "$WorkingDirectory\ForeignKey" $username = $env:DB_BUILD_USER $password = $env:DB_BUILD_PASSWORD $instance = $env:DB_SERVERINSTANCE Write-Host "[DB-Build-$env:BUILD_BUILDID]" #so that we create the objects in order we need to read the lines from: $file_date = Get-Content $BuildOrderFileForDatabaseObjects foreach ($line in $file_date) { Write-Host "The content of the file is: $line" $matchedFile = Get-ChildItem $workingDirectory -Filter $line -Recurse | %{$_.FullName} #if we have a matched file we can create the object if($matchedFile.Count -eq 1) { try { Write-Host "Re-Creating Object: $matchedFile" -ForegroundColor Green invoke-sqlcmd -inputfile $matchedFile -Database $database -u $username -p $password -serverinstance $instance } catch [Exception] { $errorMessage = $_.Exception.Message $line = $_.InvocationInfo.ScriptLineNumber $script_name = $_.InvocationInfo.ScriptName Write-Host "Error: Occurred on line $line in script $script_name." -ForegroundColor Red Write-Host "Error: $ErrorMessage" -ForegroundColor Red } } } #Now we can re-create some of the Ad-Hoc stuff if((Test-Path $dbForeignKeyWorkingDirectory) -eq $true) { Write-Host "`nRecreating The FK(s)" -ForegroundColor Green CreateDatabaseObjects -workingDirectory $dbForeignKeyWorkingDirectory } if((Test-Path $dbCheckWorkingDirectory ) -eq $true) { Write-Host "`nRecreating The Check Constraint(s)" -ForegroundColor Green CreateDatabaseObjects -workingDirectory $dbCheckWorkingDirectory } } function CreateDatabaseObjects{ param([string]$workingDirectory) $objectFullNameFromWorkingDirectory = @() Write-Host "Working direcoty is: $workingDirectory" Get-ChildItem $workingDirectory -Filter *.sql | ForEach-Object { $objectFullNameFromWorkingDirectory += [System.IO.Path]::GetFileName($_.FullName) } foreach($item in $objectFullNameFromWorkingDirectory) { Write-Host "Creating item $item" invoke-sqlcmd -inputfile $workingDirectory\$item -database $database -u $username -p $password -serverinstance $instance } } #Calling Main Function to execute program CreateStagingDB |
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):
|
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 |
function InsertMigrationStateData { #Insert State Data Back if ($env:SYSTEM_HOSTTYPE -eq 'release') { if ((Test-Path "$env:RELEASE_PRIMARYARTIFACTSOURCEALIAS\DBSourceControl\dbo._migrations-data.sql") -eq $true -And (Test-Path "$env:RELEASE_PRIMARYARTIFACTSOURCEALIAS\DBSourceControl\dbo._datafix-migrations-data.sql") -eq $true) { $database = "DB-Staging-$env:RELEASE_RELEASEID" $MigrationsTableDataScript = "$env:RELEASE_PRIMARYARTIFACTSOURCEALIAS\DBSourceControl\dbo._migrations-data.sql" $DataFixMigrationsDataScript = "$env:RELEASE_PRIMARYARTIFACTSOURCEALIAS\DBSourceControl\dbo._datafix-migrations-data.sql" ExecuteInsertStatement -MigrationsTableDataScript $MigrationsTableDataScript -DataFixMigrationsDataScript $DataFixMigrationsDataScript -database $database } else { Write-Host "##[section] Unable to Locate the Migrations State Data Scripts" } } else { if ((Test-Path "$env:BUILD_SOURCESDIRECTORY/DB-Schema/dbo._migrations-data.sql") -eq $true -And (Test-Path "$env:BUILD_SOURCESDIRECTORY/DB-Schema/dbo._datafix-migrations-data.sql") -eq $true ) { $database = "DB-Build-$env:BUILD_BUILDID" $MigrationsTableDataScript = "$env:BUILD_SOURCESDIRECTORY/DB-Schema/dbo._migrations-data.sql" $DataFixMigrationsDataScript = "$env:BUILD_SOURCESDIRECTORY/DB-Schema/dbo._datafix-migrations-data.sql" ExecuteInsertStatement -MigrationsTableDataScript $MigrationsTableDataScript -DataFixMigrationsDataScript $DataFixMigrationsDataScript -database $database } else { Write-Host "##[section] Unable to Locate the Migrations State Data Scripts" Write-Host "$env:BUILD_SOURCESDIRECTORY/DB-Schema" } } } function ExecuteInsertStatement { param([string]$MigrationsTableDataScript, [string]$DataFixMigrationsDataScript, [string]$database) #Variable $username = $env:DB_BUILD_USER $password = $env:DB_BUILD_PASSWORD $instance = $env:DB_SERVERINSTANCE Write-Host $MigrationsTableDataScript Write-Host $DataFixMigrationsDataScript invoke-sqlcmd -InputFile $MigrationsTableDataScript -database $database -u $username -p $password -serverinstance $instance invoke-sqlcmd -InputFile $DataFixMigrationsDataScript -database $database -u $username -p $password -serverinstance $instance } InsertMigrationStateData |
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
