Azure Pipeline for SQL Server database

db.ci.yaml

trigger: none

pool:
  #vmImage: 'windows-latest'
  name: SQLPoc

variables:
  gitBranchName: migration-script/$(build.buildId)
  SqlScriptsFolder: $(Build.SourcesDirectory)\db_poc\Db-Migration-Scripts\sql
  migrationScriptNumber: ''
  migrationScriptPath: '$(Build.SourcesDirectory)\db_poc\Db-Migration-Scripts\sql\$(migrationScriptNumber).sql'
  migrationReportPath: $(Build.ArtifactStagingDirectory)\migration.report.xml
  sqlServerName: localhost
  sqlServerPort: 1433
  databaseName: poc
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'
  DbUp.Server: localhost
  DbUp.Database: poc_dbup
  DbUp.UserId: sa
stages:

  - stage: CI_And_MigrationScript
    pool: 
     name: SQLPoc
    displayName: CI
    jobs:
      - job:
        steps:
          - task: UpdateDatabaseWithDbUp@2
            displayName: Create and validate db from migration scripts
            inputs:
              ConnectionString: 'Server=$(DbUp.Server);Database=$(DbUp.Database);User ID=$(DbUp.UserId);Password=$(DbUp.Password);Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;'
              ScriptPath: '$(Build.SourcesDirectory)\db_poc\Db-Migration-Scripts\sql\'
              JournalSchemaName: 'dbo'
              JournalTableName: '_SchemaVersions'
              LogScriptOutput: true
          

          - task: NuGetToolInstaller@1

          - task: MSBuild@1
            displayName: Database DACPAC build
            inputs:
              solution: 'db_poc/db_poc/db_poc.sqlproj'
              msbuildArguments: '/p:OutDir=$(Build.ArtifactStagingDirectory)'
          
          - task: SqlDacpacDeploymentOnMachineGroup@0
            displayName: Create DB from SQL Project
            inputs:
              TaskType: 'dacpac'
              DacpacFile: '$(Build.ArtifactStagingDirectory)\db_poc.dacpac'
              TargetMethod: 'server'
              ServerName: 'localhost'
              DatabaseName: 'poc_dacpac'
              AuthScheme: 'sqlServerAuthentication'
              SqlUsername: 'sa'
              SqlPassword: 'DatabaseDevops123'
              PublishProfile: '$(Build.SourcesDirectory)\db_poc\publish_testenv.xml'

          - task: PowerShell@2
            displayName: Calculate migration script name
            inputs:
              targetType: 'inline'
              script: |
                $sqlName = Get-ChildItem -Filter *.sql -Path $(SqlScriptsFolder) | Sort-Object | Select-Object -Last 1 -ExpandProperty Name
                Write-Host "Last scritp found: $sqlName."
                $sqlName = $sqlName.Replace(".sql", "")
                $sqlName = [string]([math]::floor(([double]$sqlName + 10) / 10 ) * 10)
                $sqlName = $sqlName.PadLeft(9, '0') 

                Write-Output $sqlName
                Write-Host "##vso[task.setvariable variable=migrationScriptNumber]$sqlName"
                
          - task: PowerShell@2
            displayName: Create new migration script
            inputs:
              targetType: 'inline'
              script: |
                Push-location "${Env:ProgramFiles(x86)}\Microsoft Visual Studio\Installer\";
                $sqlpackexepath = .\vswhere.exe -latest -find **\sqlpackage.exe | Select-Object -Last 1;
                              
                & $sqlpackexepath /Action:Script /SourceFile:"$(Build.ArtifactStagingDirectory)\db_poc.dacpac" /TargetServerName:"$(DbUp.Server)" /TargetDatabaseName:"$(DbUp.Database)" /DeployScriptPath:"$(migrationScriptPath)" /DeployReportPath:"$(migrationReportPath)" /Profile:"$(Build.SourcesDirectory)\db_poc\publish_testenv.xml"

                Pop-location ;

          - task: PowerShell@2
            displayName: Check if there are differences between dbs
            inputs:
              targetType: 'inline'
              script: |
                [xml]$fileContents = Get-Content -Path "$(migrationReportPath)"
                $operations = $fileContents.DeploymentReport.GetElementsByTagName("Operation")
                
                if ($operations.Count -gt 0)
                {
                    Write-Host "Needs update."
                    Write-Host "##vso[task.setvariable variable=db.needsUpdate]1"
                }
                else
                {
                    Write-Host "No update needed."
                    Write-Host "##vso[task.setvariable variable=db.needsUpdate]0"
                    Remove-Item "$(migrationScriptPath)"
                    Remove-Item "$(migrationReportPath)"
                }



          - task: PowerShell@2
            displayName: Script cleanup
            condition: and(succeeded(), eq(variables['db.needsUpdate'], 1))
            inputs:
              targetType: 'inline'
              script: |
                Write-Host "Script cleanup"
                $filePath = "$(migrationScriptPath)"
                $file = Get-Content -Path $filePath -Raw
                
                $regEx = [regex]'USE \[*.*\];'
                $match = $regEx.Match($file)
                $index = $match.Index + $match.Length
                
                $file = $file.Remove(0, $index)
                
                $file = $file.Insert(0, "SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
                SET NUMERIC_ROUNDABORT OFF;")
                
                $file = $file -replace $regEx, ''
                
                Set-Content -Path $filePath -Value $file
            
          - task: CopyFiles@2
            displayName: Migration script into artifacts folder
            inputs:
              SourceFolder: '$(SqlScriptsFolder)'
              Contents: '*.sql'
              TargetFolder: '$(Build.ArtifactStagingDirectory)\db-migration-scripts'

          - task: PowerShell@2
            displayName: Push git
            enabled: false
            condition: and(succeeded(), eq(variables['db.needsUpdate'], 1))
            env:
              PUSH_PAT: $(pushPAT) # the recommended way to map to an env variable
            inputs:
              targetType: 'inline'
              script: |
                Write-Host "Git push on new branch"
                git checkout -b $(gitBranchName)
                git config --global user.name "Azure DevOps"
                git config --global user.email noreply@azuredevops.com
                git add $(migrationScriptPath)
                git commit -m Adds migration script $(build.buildId) ***NO_CI***"
                
                Write-Host "Configure git header"
                # https://docs.microsoft.com/en-us/azure/devops/organizations/accounts/use-personal-access-tokens-to-authenticate?view=azure-devops&tabs=preview-page#use-a-pat
                $MyPat = ${env:PUSH_PAT}
                $B64Pat = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("$MyPat"))
                git -c http.extraHeader="Authorization: Basic $B64Pat"  push --set-upstream origin $(gitBranchName)
          
                
          - task: PowerShell@2
            displayName: Open Pull-Request
            enabled: false
            condition: and(succeeded(), eq(variables['db.needsUpdate'], 1))
            inputs:
              targetType: 'inline'
              script: |
                Write-Host "ApOpen Pull-Request"
                # Configure env variable PAT for AZ CLI
                $env:AZURE_DEVOPS_EXT_PAT = "$(System.AccessToken)"
                az devops configure --defaults organization=$(System.CollectionUri) project=$(System.TeamProject)
                az repos pr create --repository $(Build.Repository.Name) --description "From build $(Build.BuildId)" --title "Migration script from build $(Build.BuildId) --target-branch main"


          - task: CopyFiles@2
            displayName: Publish profiles into artifacts folder
            inputs:
              SourceFolder: 'db_poc'
              Contents: 'publish_*.xml'
              TargetFolder: '$(Build.ArtifactStagingDirectory)'

          - task: PublishBuildArtifacts@1
            displayName: Publish artifact
            inputs:
              PathtoPublish: '$(Build.ArtifactStagingDirectory)'
              ArtifactName: 'drop'
              publishLocation: 'Container'