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:
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 |
- 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' |
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:
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 |
- 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 |
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):
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 |
param( $OutputLocationForScriptDataFiles ) function ScriptoutMigrationsData{ #script out data from migrations table #Load the assembly [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #Variables if ($env:SYSTEM_HOSTTYPE -eq 'release') { $DBScaffolding = "$env:SYSTEM_DEFAULTWORKINGDIRECTORY/DBScaffoldingArea" $buildid = $env:RELEASE_RELEASEID $database = "DB-Staging-$buildid" } else { $DBScaffolding = "$OutputLocationForScriptDataFiles" $buildid = $env:BUILD_BUILDID $database = "DB-Build-$buildid" } Write-Host "Outputlocation $DBScaffolding, BuildId: $buildid, Database: $database" # Make a connection to the database $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "devsql,1433" $server.ConnectionContext.LoginSecure = $false $server.ConnectionContext.Login=$env:DB_Build_User $server.ConnectionContext.Password=$env:DB_Build_Password #Create the scripter object $scripter = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") $server #"$inst,$port" # Set general Scripter options $scripter.Options.AppendToFile = $false $scripter.Options.ScriptSchema = $false $scripter.Options.ScriptData = $true #Get Tables in question Write-Host "##[section] Retrieving Data From Migrations Table" $MigrationsStateTable += $server.Databases[$database].Tables | where {($_.Name -like "_migrations" -or $_.Name -like "_datafix-migrations")} # #Write-Host "##[section] Retrieving Data From Data Fix Migrations Table" # $MigrationsStateTable += $server.Databases['DatabaseName_QA'].Tables | where {($_.Name -like "_datafix-migrations")} Write-Host "The state of the Migrations table: $MigrationsStateTable " foreach ($table in $MigrationsStateTable) { #Script Migrations Table data to file ScriptoutMigrationsDatasqlFile -tableObject $table -OutputLocation $DBScaffolding } } function ScriptoutMigrationsDatasqlFile{ param([object]$tableObject ,[string]$OutputLocation) $filename = $tableObject -replace "\[|\]" Write-Host "inside script out function filename is: $filename" if((Test-Path "$OutputLocation\$filename.sql") -eq $false) { #Setup the output file for the item $scripter.Options.FileName = "$OutputLocation\$filename-data.sql" # Script out the object Write-Host "##[command] Scripting out $tableObject" $scripter.EnumScript($tableObject) } } ScriptoutMigrationsData |
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):
1 2 3 4 5 6 7 8 9 10 |
param ( $GitCommitMessageFile ) New-Item -ItemType File -Name "CommitDetails.txt" -Path $GitCommitMessageFile | Out-Null #add now write to it Set-Content -Path "$GitCommitMessageFile\CommitDetails.txt" -Value $env:BUILD_SOURCEVERSIONMESSAGE Write-Host "File was Created at: $GitCommitMessageFile\CommitDetails.txt" Write-Host "With Message: $env:BUILD_SOURCEVERSIONMESSAGE" |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Write-Host "##[section] Removing 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 = "use master" $sql += "`ngo" $sql += "`nalter database [DB-Build-$buildid] set single_user with rollback immediate;" $sql += "`ngo" $sql += "`ndrop database [DB-Build-$buildid]" invoke-sqlcmd -Query $sql -u $username -p $password -serverinstance $instance |
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.