如何通过Azure DevOps CICD用Terraform部署Azure SQL SSDT数据库对象

Terraform没有原生支持直接部署SSDT dacpac文件的内置资源，实际使用中主流的两种实现方式如下：

方案1：null_resource + sqlpackage 本地部署（轻量常用） #

提前准备：本地或CI/CD代理已安装sqlpackage工具，Azure账号已完成认证且有SQL数据库的管理权限，SSDT项目已编译生成.dacpac文件。

# 定义变量
variable "sql_server_fqdn" {
  type        = string
  description = "现有Azure SQL Server的完全限定域名"
}

variable "sql_database_name" {
  type        = string
  description = "目标数据库名称"
}

variable "sql_admin_username" {
  type        = string
  description = "SQL Server管理员账号"
}

variable "sql_admin_password" {
  type        = string
  sensitive   = true
  description = "SQL Server管理员密码"
}

variable "dacpac_file_path" {
  type        = string
  default     = "./bin/Release/你的SSDT项目名.dacpac"
  description = "本地dacpac文件路径"
}

# 执行dacpac部署
resource "null_resource" "deploy_ssdt" {
  # 仅当dacpac文件内容变更时触发部署
  triggers = {
    dacpac_file_hash = filemd5(var.dacpac_file_path)
  }

  provisioner "local-exec" {
    command = <<EOT
sqlpackage /Action:Publish \
  /SourceFile:"${var.dacpac_file_path}" \
  /TargetServerName:"${var.sql_server_fqdn}" \
  /TargetDatabaseName:"${var.sql_database_name}" \
  /TargetUser:"${var.sql_admin_username}" \
  /TargetPassword:"${var.sql_admin_password}" \
  /p:BlockOnPossibleDataLoss=false
EOT
  }
}

• 可根据业务需求调整/p:开头的发布参数，和SSDT原生发布参数完全通用，比如配置是否禁止数据丢失、是否忽略用户权限等。
• 需确保部署端的IP已加入Azure SQL Server的防火墙白名单。

方案2：ARM模板部署（无需本地依赖，适合纯云端部署场景） #

提前准备：将编译好的dacpac文件上传到Azure存储账户，生成对应文件的SAS访问令牌。

resource "azurerm_resource_group_template_deployment" "ssdt_deploy" {
  name                = "ssdt-dacpac-deployment"
  resource_group_name = "SQL Server所在资源组名称"
  deployment_mode     = "Incremental"

  template_content = <<EOF
{
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": { "type": "string" },
    "databaseName": { "type": "string" },
    "dacpacUri": { "type": "string" },
    "dacpacSasToken": { "type": "securestring" },
    "adminLogin": { "type": "string" },
    "adminPassword": { "type": "securestring" }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/extensions",
      "apiVersion": "2021-11-01",
      "name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/dacpac')]",
      "properties": {
        "storageKey": "[parameters('dacpacSasToken')]",
        "storageKeyType": "SharedAccessKey",
        "storageUri": "[parameters('dacpacUri')]",
        "administratorLogin": "[parameters('adminLogin')]",
        "administratorLoginPassword": "[parameters('adminPassword')]",
        "operationMode": "Import"
      }
    }
  ]
}
EOF

  parameters_content = jsonencode({
    serverName         = "你的SQL Server名称（不含.database.windows.net后缀）"
    databaseName       = var.sql_database_name
    dacpacUri          = "存储账户中dacpac文件的访问地址"
    dacpacSasToken     = { value = "dacpac文件对应的SAS令牌" }
    adminLogin         = var.sql_admin_username
    adminPassword      = { value = var.sql_admin_password }
  })
}

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