sql-server - 在 Azure DevOps 管道上生成 SQL Server 架构更改脚本

Question

我试图允许管道将架构更改发布到本地 SQL Server 2017 实例，但我想分两步完成:

• 生成架构更改脚本操作
• 批准后发布

我知道可以通过 publishing to SQL Azure 来实现通过设置 deploymentAction: 'Script' 然后 deploymentAction: 'Publish'

有没有办法以类似的方式发布到本地 SQL Server？我已经尝试过SqlDacpacDeploymentOnMachineGroup任务，但似乎不可能通过此任务分两步完成

Answer 1

我终于成功地通过数据库更改实现了 SQL 模式生成，然后发布了这些更改(在批准后)。一些备注:

• 如果更改会导致数据丢失，则此方法将不起作用。
• 仅当安装了 Visual Studio 2019 时，sqlpackage 的路径才正确，如在 windows-2019 镜像中。
• 之前的 package.dacpac 是之前通过构建 .sqlproj 项目生成的。
• 我通过组变量传递了以下变量(有关如何创建组变量的更多信息 here ):
  • targetDBConnectionString
  • 服务器名称
  • 数据库名称
  • 管理员登录
  • 管理员密码
• 我添加了 approval到 ApplyChanges 阶段(在 Pipelines 菜单中，选择环境，然后选择 ApplyChanges环境，然后从三个点进行批准和检查按钮，位于右上角)。这样改变就不会在手动批准之前应用于数据库。

<小时/>

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