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

ccoutinho · Accepted Answer · 2020-04-27T22:59:15.780

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)'

score 3 · Answer 2 · answered Apr 16 '20 at 08:50

I don't think there is a built-in task that can do this for you. However, SQLPackage.exe has the option. Take a look at this post: http://diegogiacomelli.com.br/azure-pipelines-generating-db-script/. It describes using a command line task to generate the sql script.

Once you have the script, you could either use the SqlDacpacDeploymentOnMachineGroup to publish the script (although it won't re-use the already generated script), or you can write a powershell script that publishes the sql script to the database. An example of such a script can be found here: https://careers.centric.eu/nl/blog/custom-azure-devops-pipeline-task-execute-sql-script/

The first article really pointed me in the right direction, however I think it is possible to use SqlDacpacDeploymentOnMachineGroup to publish an incremental SQL script. I will post my solution tonight. Also, the link to the code in the second article is not working, which made it really difficult for someone who doesn't have much experience with Powershell, like myself, to write such script. — ccoutinho, Apr 19 '20 at 18:11

Generate SQL Server schema change script on Azure DevOps pipeline

2 Answers2