无需手动操作的SQL Server自动化部署工作流优化需求

核心目标 #

移除Visual Studio生成的.sln文件，实现代码库中SQL文件夹变更后，Azure DevOps流水线自动完成检测变更→生成.sqlproj→构建.dacpac→部署到Azure SQL数据库的全流程，无需任何手动操作。

第一步：用PowerShell脚本自动生成.sqlproj #

.sqlproj本质是XML格式的项目文件，我们可以通过脚本遍历SQL文件目录，动态生成包含所有SQL对象的项目文件，替代手动维护或Azure Data Studio生成的方式。

示例PowerShell脚本（Generate-SqlProj.ps1） #

# 定义SQL文件根目录和输出的sqlproj路径
$sqlRootDir = "./SQLScripts"
$sqlProjPath = "./DatabaseProject.sqlproj"

# 基础sqlproj模板结构
$xmlTemplate = @"
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProductVersion>10.0.30319</ProductVersion>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectGuid>{$(New-Guid)}</ProjectGuid>
    <Name>DatabaseProject</Name>
    <RootNamespace>DatabaseProject</RootNamespace>
    <SqlServerVersion>SqlAzure</SqlServerVersion>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureDatabaseSchemaProvider</DSP>
    <OutputPath>bin\$(Configuration)</OutputPath>
    <BuildScriptName>DatabaseProject.sql</BuildScriptName>
    <DeployScriptFileName>DatabaseProject.Deploy.sql</DeployScriptFileName>
    <TargetDatabaseName>YourTargetDBName</TargetDatabaseName>
  </PropertyGroup>
  <ItemGroup>
    <Build Include="Properties\Database.sqlsettings" />
"@

# 遍历所有SQL文件（包含子文件夹）
$sqlFiles = Get-ChildItem -Path $sqlRootDir -Filter "*.sql" -Recurse
foreach ($file in $sqlFiles) {
    # 转换为相对路径，符合sqlproj格式
    $relativePath = $file.FullName.Substring($PWD.Path.Length + 1).Replace("\", "/")
    $xmlTemplate += "`n    <Build Include=""$relativePath"" />"
}

# 闭合XML结构
$xmlTemplate += @"

  </ItemGroup>
  <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
</Project>
"@

# 输出到sqlproj文件
$xmlTemplate | Out-File -Path $sqlProjPath -Encoding utf8

这个脚本会自动扫描指定目录下所有.sql文件（包括子文件夹），并将它们添加到sqlproj的<Build>节点中，每次运行都会生成全新的项目文件，确保所有最新的SQL变更都被包含。

第二步：改造Azure DevOps流水线 #

调整现有流水线，加入自动生成sqlproj的步骤，并移除对.sln的依赖：

流水线步骤详情 #

1. 触发条件配置

   • 在流水线的触发设置中，添加路径过滤器：SQLScripts/**，确保只有当SQL脚本文件夹发生变更时才触发流水线，减少不必要的构建。

2. 执行PowerShell脚本生成.sqlproj

   • 添加「PowerShell」任务，选择刚才的Generate-SqlProj.ps1脚本，设置工作目录为代码库根目录。
   • 注意：如果脚本中用到的SQL根目录或sqlproj路径和你的实际结构不符，需要提前修改脚本中的变量。

3. 用MSBuild构建.dacpac

   • 替换原有的VSBuild任务，使用「MSBuild」任务，直接指定生成的DatabaseProject.sqlproj作为项目文件。
   • MSBuild参数示例：/p:Configuration=Release /t:Build /p:SqlPublishProfilePath=YourPublishProfile.publish.xml（如果需要用发布配置文件，提前将配置文件存入代码库）。

4. 部署.dacpac到Azure SQL数据库

   • 保留原有的「SqlAzureDacpacDeployment」任务，指定构建输出的.dacpac路径（通常是bin/Release/DatabaseProject.dacpac），并配置Azure SQL数据库的连接信息或使用服务连接。

关键注意事项 #

• SQL文件命名规则：确保SQL文件的命名和内容符合SSDT的要求，比如创建表的脚本不要包含USE语句，避免部署时出现上下文问题。
• 脚本权限：确保Azure DevOps代理有足够权限读取SQL文件夹和写入sqlproj文件。
• 增量构建优化：如果需要增量构建，可以在流水线中添加缓存步骤，缓存MSBuild的中间文件，但因为每次都会重新生成sqlproj，建议保持全量构建以确保准确性。
• 测试环节：建议在部署到生产环境前加入测试环节，比如用测试数据库验证dacpac的部署效果，避免直接影响生产。

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