如何基于SQL脚本在Azure DevOps为SQL Server 2016搭建CI/CD流水线？

一、构建流水线（经典编辑器） #

构建阶段核心是合并SQL脚本并发布为工件，具体操作：

1. 基础配置

   • 选择代理池：用托管代理（如Hosted Ubuntu 22.04）即可处理文件合并；有特殊依赖时也可使用自托管代理。
   • 触发设置：勾选「持续集成」，指定触发分支为main，确保代码合并到main后自动启动构建。

2. 添加任务

   • 获取源代码：默认已包含该任务，确认仓库指向你的Azure Git仓库，分支选择main。
   • PowerShell任务（合并脚本）：
     新增PowerShell任务，在「脚本」框中输入合并逻辑（可根据你的脚本目录结构调整顺序，保证依赖优先执行）：

     # 定义脚本合并顺序，按依赖优先级排列
     $scriptOrder = @(
         "schema/*.sql",
         "functions/*.sql",
         "views/*.sql",
         "stored-procedures/*.sql"
     )
     $outputPath = "$(Build.ArtifactStagingDirectory)/combined-deployment.sql"
     
     # 清空并创建输出文件
     if (Test-Path $outputPath) { Remove-Item $outputPath }
     New-Item -Path $outputPath -ItemType File | Out-Null
     
     # 按顺序合并脚本，添加GO分隔批处理
     foreach ($pattern in $scriptOrder) {
         Get-ChildItem -Path $pattern -Recurse | ForEach-Object {
             Add-Content $outputPath "-- 开始执行: $($_.Name)`n"
             Add-Content $outputPath (Get-Content $_.FullName -Raw)
             Add-Content $outputPath "`nGO`n-- 结束执行: $($_.Name)`n"
         }
     }
     

     注：添加GO是为了符合SQL Server批处理执行规则，避免跨批对象引用报错。
   • 发布构建工件：
     新增「发布构建工件」任务，设置：
     • 工件名称：SQLDeploymentScripts
     • 要发布的路径：$(Build.ArtifactStagingDirectory)
     • 发布位置：Azure Pipelines

二、发布流水线（经典编辑器） #

发布阶段核心是将合并后的脚本部署到本地SQL Server 2016，具体操作：

1. 基础配置

   • 链接构建工件：选择上一步构建生成的SQLDeploymentScripts作为发布源。
   • 选择自托管代理：由于目标SQL Server是本地部署，必须使用能访问该实例的自托管代理（托管代理无法连接本地内网资源）。

2. 添加部署任务
   推荐使用SQLCMD任务（更贴合SQL Server执行场景）：

   • 任务配置：
     • 服务器名称：本地SQL Server实例名，例如localhost\MSSQLSERVER2016
     • 数据库名称：目标部署数据库名
     • 认证方式：根据实际选择「Windows认证」（需代理账号有数据库权限）或「SQL Server认证」（填写账号密码）
     • 脚本路径：$(System.DefaultWorkingDirectory)/SQLDeploymentScripts/combined-deployment.sql
     • 额外SQLCMD参数：添加-b（执行出错时终止并返回错误码），确保流水线能捕获部署失败。

   若偏好PowerShell，也可使用PowerShell任务，脚本如下：

   Invoke-SqlCmd -ServerInstance "localhost\MSSQLSERVER2016" `
                 -Database "TargetDB" `
                 -InputFile "$(System.DefaultWorkingDirectory)/SQLDeploymentScripts/combined-deployment.sql" `
                 -ErrorAction Stop
   

关键注意事项 #

• 脚本幂等性：所有SQL脚本需支持重复执行，例如创建存储过程使用CREATE OR ALTER PROCEDURE（SQL Server 2016及以上支持），避免重复创建报错。
• 依赖顺序：合并脚本时必须按依赖优先级排序（如先创建函数，再创建引用该函数的存储过程），否则部署会因对象不存在失败。
• 代理权限：自托管代理的运行账号需拥有目标SQL Server的DDL权限（如创建/修改存储过程、函数等）。

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