Azure DevOps经典发布Dacpac部署异常：本地正常但无实际效果

问题1：sqlPackage.exe未被识别的解决 #

• 确保Azure DevOps代理机器已安装SQL Server Data Tools (SSDT)，sqlPackage.exe的常见安装路径：
  • Visual Studio 2022 Enterprise: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\160\sqlpackage.exe
  • 独立SSDT安装包: C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe
  • Visual Studio 2019 Enterprise: C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe
• 脚本中通过遍历常见路径自动查找sqlPackage.exe，避免硬编码绝对路径的兼容性问题：

  $sqlPackagePaths = @(
      "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\160\sqlpackage.exe",
      "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe",
      "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe"
  )
  
  $sqlPackageExe = $null
  foreach ($path in $sqlPackagePaths) {
      if (Test-Path $path) {
          $sqlPackageExe = $path
          break
      }
  }
  
  if (-not $sqlPackageExe) {
      throw "sqlPackage.exe not found. Ensure SSDT is installed on the agent machine."
  }
  

问题2：部署到错误LocalDB实例的解决 #

• 放弃使用默认用户级LocalDB实例，改为连接到SQL Server命名实例/默认实例，示例连接字符串：

  Data Source=.\SQLEXPRESS;Initial Catalog=YourTargetDB;Integrated Security=True;
  

• 若必须使用LocalDB，需创建共享实例并连接到共享实例：
  1. 在代理机器执行（可加入脚本）：sqllocaldb create "SharedLocalDB" -s
  2. 共享实例：sqllocaldb share "SharedLocalDB" "SharedLocalDB"
  3. 连接字符串改为：Data Source=(LocalDB).\.\SharedLocalDB;Initial Catalog=YourTargetDB;Integrated Security=True;
• 确保Azure DevOps代理服务账户（通常为NT AUTHORITY\SYSTEM或自定义服务账户）拥有目标数据库实例的db_owner或部署权限。

可用的PowerShell部署脚本 #

# 配置参数（根据实际项目修改）
$dacpacPath = "$(System.DefaultWorkingDirectory)\YourBuildArtifact\YourDatabase.dacpac"
$targetConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=YourTargetDB;Integrated Security=True;"
$preDeploymentScript = "$(System.DefaultWorkingDirectory)\YourBuildArtifact\Scripts\PreDeployment.sql"
$postDeploymentScript = "$(System.DefaultWorkingDirectory)\YourBuildArtifact\Scripts\PostDeployment.sql"

# 查找sqlPackage.exe
$sqlPackagePaths = @(
    "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\160\sqlpackage.exe",
    "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe",
    "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe"
)

$sqlPackageExe = $null
foreach ($path in $sqlPackagePaths) {
    if (Test-Path $path) {
        $sqlPackageExe = $path
        break
    }
}

if (-not $sqlPackageExe) {
    throw "sqlPackage.exe not found. Install SSDT on the agent machine."
}

# 构建部署命令参数
$publishArgs = @(
    "/Action:Publish",
    "/SourceFile:`"$dacpacPath`"",
    "/TargetConnectionString:`"$targetConnectionString`"",
    "/p:IncludeCompositeObjects=True"
)

# 加入预部署脚本（如果存在）
if (Test-Path $preDeploymentScript) {
    $publishArgs += "/PreDeploymentPath:`"$preDeploymentScript`""
}

# 加入后部署脚本（如果存在）
if (Test-Path $postDeploymentScript) {
    $publishArgs += "/PostDeploymentPath:`"$postDeploymentScript`""
}

# 执行部署并检查结果
Write-Host "Running deployment command: $sqlPackageExe $publishArgs"
& $sqlPackageExe $publishArgs

if ($LASTEXITCODE -ne 0) {
    throw "Deployment failed with exit code $LASTEXITCODE"
}
Write-Host "Database deployment completed successfully."

额外注意事项 #

• Azure DevOps管道中，确保构建工件包含.dacpac文件和预/后部署脚本，路径与脚本配置一致。
• 使用SqlDacpacDeploymentOnMachineGroup@0任务时，需在Connection String字段明确指定目标数据库实例，不要依赖默认值；同时确认Deployment Action设为Publish，并正确配置预/后部署脚本路径。

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