Generate SQL Server schema change script on Azure DevOps pipeline

Question

I am trying to allow a pipeline to publish a schema change to an on-premises SQL Server 2017 instance, but I want to do that in two steps:

• Generate schema change script action
• After approval, publish

I know that can be achieved by publishing to SQL Azure by setting deploymentAction: 'Script' and then deploymentAction: 'Publish'

Is there a way to publish to an on-premises SQL Server in a similar way? I have tried the SqlDacpacDeploymentOnMachineGroup task, but it does not seem possible to do it in two steps with this task

Answer 1

I have finally managed to implement SQL schema generation with database changes, followed by publishing those changes (after approval). Some remarks:

• This won't work if the changes will cause data loss.
• The path for the sqlpackage is only correct when Visual Studio 2019 is installed, like in windows-2019 images.
• A previous package.dacpac was previously generated by building the .sqlproj project(s).
• I passed the following variables through a group variable (more information on how to create group variables here):
  • targetDBConnectionString
  • servername
  • databasename
  • adminlogin
  • adminPassword
• I have added an approval to the ApplyChanges stage (within Pipelines menu, choose environments, then the ApplyChanges environment, and then approvals and checks from the three dots button, on the top right corner). This way the changes are not applied to the database before manual approval takes place.

---

stage: VerifyScript
      displayName: 'Script database schema changes'
      dependsOn: 
        - Build
      jobs:
      - deployment: VerifyScript
        pool:
          vmImage: 'windows-2019'
        variables:
        - group: 'Timeline CV - Release'
        environment: 'scriptverification'
        strategy:
          runOnce:
            deploy:
              steps:
              - download: current
                artifact: dropDacpac
                patterns: '**/*'

          - task: CmdLine@2
            displayName: 'Generate schema changes script'
            inputs:
              script: |
                "c:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\sqlpackage.exe"    ^
                /action:script ^
                /diagnostics:true ^
                /sourcefile:$(Pipeline.Workspace)\dropDacpac\path\to\the\dacpacFile\package.dacpac    ^
                /targetConnectionString:$(targetDBConnectionString) ^
                /outputpath:$(Build.StagingDirectory)\changesScript.sql

          - task: PublishPipelineArtifact@1
            inputs:
              targetPath: '$(Build.StagingDirectory)'
              artifactName: dropSqlSchemaChangesScript
            condition: succeededOrFailed()

          - task: PowerShell@2
            displayName: Show Auto Generated SQL Script
            inputs: 
              targetType: 'inline'
              script: | 
                Write-Host "Auto Generated SQL Update Script:"
                Get-Content $(Build.StagingDirectory)\changesScript.sql | foreach {Write-Output      $_}

- stage: ApplyChanges
  displayName: 'Apply database schema changes'
  dependsOn: VerifyScript
  jobs:
  - deployment: ApplyChanges
    pool:
      vmImage: 'windows-2019'
    variables:
    - group: 'Timeline CV - Release'
    environment: 'applyChanges'
    strategy:
      runOnce:
        deploy:
          steps:
          - download: current
            artifact: dropSqlSchemaChangesScript
          - task: SqlDacpacDeploymentOnMachineGroup@0
            displayName: 'Deploy SQL schema changes script'
            inputs:
              taskType: 'sqlQuery'
              sqlFile: '$(Pipeline.Workspace)\dropSqlSchemaChangesScript\changesScript.sql'
              targetMethod: 'server'
              authScheme: 'sqlServerAuthentication'
              serverName: '$(servername)'
              databaseName: '$(databasename)'
              sqlUsername: '$(adminlogin)'
              sqlPassword: '$(adminPassword)'