如何在DevOps部署流水线中自动记录数据库变更

要实现你想要的Before/After变更清单，我们可以利用SqlPackage.exe（微软官方的SQL数据库部署工具）的差异对比能力，结合Azure Pipelines的任务来捕获并格式化变更内容。下面是具体的实现步骤：

1. 准备基础任务 #

首先，确保你的Pipeline已经包含了Azure SQL Database Deployment任务（用来部署dacpac），我们需要在部署前后添加对比和报告生成的步骤。

2. 部署前：获取目标数据库当前状态并生成预部署差异报告 #

添加一个PowerShell任务，使用SqlPackage.exe对比目标数据库和待部署的dacpac，生成预部署的差异报告。SqlPackage.exe通常在Azure DevOps代理的C:\Program Files\Microsoft SQL Server\160\DAC\bin\路径下（版本号可能根据代理环境调整）。

示例脚本：

# 定义参数
$targetServer = "$(TargetServerName)"
$targetDatabase = "$(TargetDatabaseName)"
$dacpacPath = "$(Build.ArtifactStagingDirectory)\YourSolution\YourDatabase.dacpac"
$preDeployReportPath = "$(Build.ArtifactStagingDirectory)\PreDeployChanges.xml"

# 执行SqlPackage对比，生成预部署差异报告
& "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" `
    /Action:Compare `
    /SourceFile:$dacpacPath `
    /TargetServerName:$targetServer `
    /TargetDatabaseName:$targetDatabase `
    /TargetUser:"$(SqlUsername)" `
    /TargetPassword:"$(SqlPassword)" `
    /OutputPath:$preDeployReportPath `
    /p:GenerateDeploymentReport=True

这个XML报告会包含所有待执行的变更，包括表架构迁移、列类型修改、新增列等。

3. 执行Dacpac部署 #

使用Azure SQL Database Deployment任务完成部署，记得开启Generate deployment report选项（在任务的"Deployment options"里），这样任务会自动生成部署后的执行报告，我们可以后续捕获它。

4. 部署后：解析差异报告，生成格式化的Before/After清单 #

再添加一个PowerShell任务，解析预部署的XML报告，提取你需要的表和列变更信息，转换成你想要的Before/After格式。

示例解析脚本（针对表迁移和列变更的场景）：

$reportPath = "$(Build.ArtifactStagingDirectory)\PreDeployChanges.xml"
$xml = [xml](Get-Content $reportPath)

# 遍历所有变更项
foreach ($change in $xml.DeploymentReport.Operations.Operation) {
    # 处理表架构迁移
    if ($change.Type -eq "SqlTable" -and $change.AlterType -eq "Move") {
        $beforeSchema = $change.Source.Value.Split('.')[0]
        $beforeTable = $change.Source.Value.Split('.')[1]
        $afterSchema = $change.Target.Value.Split('.')[0]
        $afterTable = $change.Target.Value.Split('.')[1]

        Write-Host "### Table Migration"
        Write-Host "Before: TableName: $beforeSchema.$beforeTable"
        Write-Host "After: TableName: $afterSchema.$afterTable"
        Write-Host ""
    }

    # 处理列变更（新增、修改类型）
    if ($change.Type -eq "SqlColumn") {
        $tableName = $change.Parent.Value
        $columnName = $change.Source.Value

        # 获取变更前的列信息
        $beforeColumnDetails = $change.Source.Property | Where-Object { $_.Name -eq "DataType" } | Select-Object -ExpandProperty Value
        # 获取变更后的列信息
        $afterColumnDetails = $change.Target.Property | Where-Object { $_.Name -eq "DataType" } | Select-Object -ExpandProperty Value

        Write-Host "### Column Change for Table: $tableName"
        Write-Host "Before: Columns: $columnName $beforeColumnDetails"
        Write-Host "After: Columns: $columnName $afterColumnDetails"
        Write-Host ""
    }

    # 处理新增列
    if ($change.Type -eq "SqlColumn" -and $change.AlterType -eq "Add") {
        $tableName = $change.Parent.Value
        $columnName = $change.Target.Value
        $columnType = $change.Target.Property | Where-Object { $_.Name -eq "DataType" } | Select-Object -ExpandProperty Value

        Write-Host "### New Column Added to Table: $tableName"
        Write-Host "Before: Columns: [No such column]"
        Write-Host "After: Columns: $columnName $columnType"
        Write-Host ""
    }
}

你可以根据自己的需求扩展这个脚本，比如处理删除列、索引变更等场景。

5. 保存或发布变更清单 #

最后，你可以将生成的清单保存为文件，通过Publish Build Artifacts任务上传到Azure DevOps的工件库，或者直接在Pipeline日志中输出，方便后续查看。

额外提示 #

• 如果你的Azure DevOps代理没有安装最新版本的SqlPackage.exe，可以通过Chocolatey任务安装sqlpackage包，确保工具可用。
• 对于复杂的变更场景，你也可以考虑使用Microsoft.SqlServer.Dac NuGet包编写C#工具来解析差异报告，然后在Pipeline中运行这个工具。

内容的提问来源于stack exchange，提问作者SomeDude