使用 Azure DevOps Pipeline 执行 SQL 脚本

最后修改:2025年6月27日

by Michal Aibin

1. 概述

在本教程中，我们将探讨如何使用 Azure DevOps Pipelines 来执行 SQL 脚本。我们会介绍几种在 Azure SQL Database 上运行 SQL 脚本的方法，包括使用内置任务、PowerShell 命令以及认证方式。

现代 DevOps 实践中，数据库的自动化部署通常需要与应用程序代码同步进行。Azure DevOps 提供了多种方式在 CI/CD 流水线中执行 SQL 脚本。无论是部署数据库结构变更、执行数据迁移，还是运行维护脚本，自动化这些任务都能提升一致性并减少人为错误。

2. 前置条件

在开始之前，我们需要准备好以下资源和环境。

2.1. Azure 资源

你需要准备以下 Azure 资源：

  • 一个已有的 Azure SQL Database 和服务器
  • 一个 Azure DevOps 组织 及项目
  • 具备足够权限的 Azure 订阅
  • 在 Azure DevOps 与 Azure 之间配置好 服务连接（Service Connection）

2.2. 仓库结构

建议将 SQL 脚本按如下结构组织：

/SQLScripts
  /Tables
    CreateCustomersTable.sql
    CreateOrdersTable.sql
  /StoredProcedures
    GetCustomerOrders.sql
  /Views
    CustomerOrdersView.sql
  /Data
    InitialData.sql

这种结构有助于我们控制脚本的执行顺序。比如，表的创建应优先于视图或存储过程，这样可以避免引用错误。通过将脚本分目录管理，我们可以更方便地在部署时按顺序处理它们。

3. 使用 Azure SQL Database Deployment 任务

最简单的方式是使用内置的 SqlAzureDacpacDeployment 任务，它支持 DACPAC 部署和 SQL 脚本执行。

3.1. 执行单个 SQL 脚本

以下是一个执行单个 SQL 脚本的 YAML 示例：

trigger:
- main

pool:
  vmImage: 'windows-latest'

variables:
  azureSubscription: 'AzureServiceConnection'
  serverName: 'myserver.database.windows.net'
  databaseName: 'mydatabase'
  sqlUsername: 'sqladmin'
  sqlPassword: '$(SqlPassword)' # 存储为密文变量

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: '执行 SQL 脚本'
  inputs:
    azureSubscription: '$(azureSubscription)'
    ServerName: '$(serverName)'
    DatabaseName: '$(databaseName)'
    SqlUsername: '$(sqlUsername)'
    SqlPassword: '$(sqlPassword)'
    deployType: 'SqlTask'
    SqlFile: '$(Build.SourcesDirectory)/SQLScripts/Tables/CreateCustomersTable.sql'

该任务会自动处理数据库连接和错误报告。注意：我们将 SQL 密码作为密文变量存储，避免密码泄露在日志中。 使用 $(Build.SourcesDirectory) 可以确保路径在不同构建代理上通用。

3.2. 执行多个 SQL 脚本

如果需要按顺序执行多个脚本，可以使用 InlineSqlTask：

- task: SqlAzureDacpacDeployment@1
  displayName: '执行多个 SQL 脚本'
  inputs:
    azureSubscription: '$(azureSubscription)'
    ServerName: '$(serverName)'
    DatabaseName: '$(databaseName)'
    SqlUsername: '$(sqlUsername)'
    SqlPassword: '$(sqlPassword)'
    deployType: 'InlineSqlTask'
    InlineSql: |
      :r $(Build.SourcesDirectory)/SQLScripts/Tables/CreateCustomersTable.sql
      :r $(Build.SourcesDirectory)/SQLScripts/Tables/CreateOrdersTable.sql
      :r $(Build.SourcesDirectory)/SQLScripts/Views/CustomerOrdersView.sql

:r 是 SQLCMD 指令，用于读取并执行外部 SQL 文件。这些脚本会在同一个数据库会话中执行，可以共享变量和临时表。 但如果其中某个脚本失败，整个任务也会失败，因此建议每个脚本都加上错误处理逻辑。

4. 使用 PowerShell 的 Invoke-Sqlcmd

如果你需要更高的灵活性，可以使用 PowerShell 的 Invoke-Sqlcmd 命令。

4.1. 配置 PowerShell 任务

下面是一个使用 Invoke-Sqlcmd 执行 SQL 脚本的示例：

- task: PowerShell@2
  displayName: '安装 SqlServer 模块'
  inputs:
    targetType: 'inline'
    script: |
      if (-not (Get-Module -ListAvailable -Name SqlServer)) {
        Install-Module -Name SqlServer -Force -AllowClobber
      }

- task: PowerShell@2
  displayName: '执行 SQL 脚本'
  inputs:
    targetType: 'inline'
    script: |
      $serverInstance = "$(serverName)"
      $database = "$(databaseName)"
      $username = "$(sqlUsername)"
      $password = "$(sqlPassword)"
      
      # 执行单个脚本
      Invoke-Sqlcmd -InputFile "$(Build.SourcesDirectory)/SQLScripts/Tables/CreateCustomersTable.sql" `
        -ServerInstance $serverInstance `
        -Database $database `
        -Username $username `
        -Password $password `
        -QueryTimeout 3600 `
        -Verbose

第一个任务确保 PowerShell 模块存在，因为微软提供的代理不总是预装该模块。 使用 -Force 和 -AllowClobber 可避免安装冲突。

第二个任务中，-QueryTimeout 设置为 3600 秒，防止脚本执行时间过长导致超时；-Verbose 提供详细输出，便于调试。

4.2. 执行目录下的所有 SQL 脚本

我们可以进一步优化脚本，按顺序执行目录下的所有 SQL 文件：

- task: PowerShell@2
  displayName: '执行目录下的所有 SQL 脚本'
  inputs:
    targetType: 'inline'
    script: |
      $scriptsPath = "$(Build.SourcesDirectory)/SQLScripts"
      $executionOrder = @("Tables", "Views", "StoredProcedures", "Data")
      
      foreach ($folder in $executionOrder) {
        $folderPath = Join-Path $scriptsPath $folder
        if (Test-Path $folderPath) {
          Get-ChildItem -Path $folderPath -Filter "*.sql" | ForEach-Object {
            Write-Host "执行脚本: $($_.Name)"
            try {
              Invoke-Sqlcmd -InputFile $_.FullName `
                -ServerInstance "$(serverName)" `
                -Database "$(databaseName)" `
                -Username "$(sqlUsername)" `
                -Password "$(sqlPassword)" `
                -QueryTimeout 3600
              Write-Host "执行成功: $($_.Name)"
            }
            catch {
              Write-Error "执行失败 $($_.Name): $_"
              exit 1
            }
          }
        }
      }

该脚本通过 $executionOrder 数组控制执行顺序，确保对象依赖正确。每个脚本执行时都包裹在 try-catch 中，便于捕获异常并及时终止流水线。

5. 使用 Azure AD 认证

为了增强安全性，推荐使用 Azure AD 而不是 SQL 账户进行认证。

5.1. 使用托管身份（Managed Identity）

以下是一个使用托管身份执行 SQL 脚本的示例：

- task: AzurePowerShell@5
  displayName: '使用 AAD Token 执行 SQL'
  inputs:
    azureSubscription: '$(azureSubscription)'
    ScriptType: 'InlineScript'
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      # 获取 SQL 数据库的访问 Token
      $tokenResponse = Get-AzAccessToken -ResourceUrl https://database.windows.net
      $accessToken = $tokenResponse.Token
      
      # 使用 Token 执行 SQL 脚本
      Invoke-Sqlcmd -ServerInstance "$(serverName)" `
        -Database "$(databaseName)" `
        -AccessToken $accessToken `
        -InputFile "$(Build.SourcesDirectory)/SQLScripts/Tables/CreateCustomersTable.sql" `
        -QueryTimeout 3600

这种方式避免了在脚本中暴露 SQL 凭据。 Get-AzAccessToken 会使用流水线的服务连接身份获取 SQL 数据库的访问 Token。Token 通常有效时间为 1 小时，足以满足大多数部署场景。

使用 Azure AD 的优势包括：支持条件访问、多因素认证、审计日志等。需要确保服务主体在 SQL 数据库中有足够的权限。

5.2. 使用服务主体认证

你也可以使用服务主体获取 Token：

- task: AzurePowerShell@5
  displayName: '为服务主体获取 AAD Token'
  inputs:
    azureSubscription: '$(azureSubscription)'
    ScriptType: 'InlineScript'
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext
      $sqlToken = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate(
        $context.Account, 
        $context.Environment, 
        $context.Tenant.Id.ToString(), 
        $null, 
        [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, 
        $null, 
        "https://database.windows.net"
      ).AccessToken
      
      Write-Host "##vso[task.setvariable variable=SQLTOKEN;]$sqlToken"

- task: SqlAzureDacpacDeployment@1
  displayName: '使用 Token 部署'
  inputs:
    azureSubscription: '$(azureSubscription)'
    AuthenticationType: 'connectionString'
    ConnectionString: 'Data Source=$(serverName);Initial Catalog=$(databaseName);'
    deployType: 'SqlTask'
    SqlFile: '$(Build.SourcesDirectory)/SQLScripts/Tables/CreateCustomersTable.sql'
    SqlAdditionalArguments: '-AccessToken $(SQLTOKEN)'

该方式手动从 Azure PowerShell 会话中提取认证上下文，并生成用于 SQL 的访问 Token。然后通过特殊语法 ##vso[task.setvariable] 将 Token 存储为变量。

第二个任务使用该 Token 和连接字符串执行 SQL 脚本。此模式适用于不直接支持 Azure AD 认证的任务，但可以通过参数传递 Token 的情况。

6. 最佳实践

6.1. 事务管理

为了确保 SQL 脚本执行的原子性，我们可以在脚本中使用事务控制：

BEGIN TRANSACTION;

-- 你的 SQL 语句

COMMIT TRANSACTION;

这样可以确保脚本中的多个操作要么全部成功，要么全部回滚，避免数据库处于不一致状态。

✅ 总结：Azure DevOps 提供了多种方式来执行 SQL 脚本，包括内置任务和 PowerShell 命令。合理使用目录结构、事务控制和认证方式，可以显著提升数据库部署的稳定性和安全性。

⚠️ 注意：SQL 脚本的执行顺序和错误处理是关键，建议在部署前进行本地验证。

原始标题:Execute SQL Script Using Azure DevOps Pipeline

« 上一篇： 解决 Docker 部署 Flask 应用时的连接问题
» 下一篇： 使用 GitHub Actions 在工作流中创建 .env 文件