使用Bicep在Azure部署SQL资源超时失败：资源写入操作未完成

部署Azure SQL Server及数据库时，运行约30分钟后超时失败，错误提示操作超时并自动回滚，需重试。部署场景为频繁删除资源组及部署记录后重新创建资源。

deploy_sql.bicep #

targetScope = 'subscription'

module createSQL './modules/sql/create-sql.bicep' = {
  name: 'CreateProvisionSQL'
  params:{
    server_name: 'sql-db-app-dev'
    admin_username: 'sqladministrator'
    admin_password: 'xxxxxxxxxxxx'
    db_name: 'my_db'
  }
  scope: resourceGroup('my-resource-group')
  dependsOn: [createRG]
}

var myoutput = createSQL.outputs.MyConnectionString

./modules/sql/create-sql.bicep #

resource sqlServer 'Microsoft.Sql/servers@2021-11-01' = {
  name: 'sql-db-app-dev'
  location: resourceGroup().location
  // kind: 'v12.0'
  properties: {
    administratorLogin: 'administratorLogin'
    administratorLoginPassword: 'xxxxxxxxxxxx'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: 'myGroup'
      sid: 'xxxxxxxxxxxxxx'
      tenantId: tenant().tenantId
      azureADOnlyAuthentication: false
  }
}
}

resource serverName_sqlDBName 'Microsoft.Sql/servers/databases@2020-08-01-preview' = {
  parent: sqlServer
  name: 'sample_db'
  location: resourceGroup().location
  sku: {
    name: 'standard'
    tier: 'standard'
    capacity: 10
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CS_AS'
  }
}
output MyConnectionString string = '${sqlServer.name}${environment().suffixes.sqlServerHostname},1433;'

az deployment group create --name deploy_sql --template-file .\create-sql.bicep --resource-group my-resource-group --output jsonc

{"status":"Failed","error":{"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.","details":[{"code":"Conflict","message":"{\r\n  \"status\": \"Failed\",\r\n  \"error\": {\r\n    \"code\": \"ResourceDeploymentFailure\",\r\n    \"message\": \"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\",\r\n    \"details\": [\r\n      {\r\n        \"code\": \"DeploymentFailed\",\r\n        \"message\": \"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.\",\r\n        \"details\": [\r\n          {\r\n            \"code\": \"Conflict\",\r\n            \"message\": \"{\\r\\n  \\\"status\\\": \\\"Failed\\\",\\r\\n  \\\"error\\\": {\\r\\n    \\\"code\\\": \\\"ResourceDeploymentFailure\\\",\\r\\n    \\\"message\\\": \\\"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.\\\",\\r\\n    \\\"details\\\": [\\r\\n      {\\r\\n        \\\"code\\\": \\\"OperationTimedOut\\\",\\r\\n        \\\"message\\\": \\\"The operation timed out and automatically rolled back. Please retry the operation.\\\"\\r\\n      }\\r\\n    ]\\r\\n  }\\r\\n}\"\r\n          }\r\n        ]\r\n      }\r\n    ]\r\n  }\r\n}"}]}}}

• 解决资源残留冲突：频繁删建资源组时，Azure后台可能存在资源清理延迟（如DNS缓存、资源状态同步滞后），导致新部署的SQL Server名称看似可用但实际处于锁定状态。临时修改SQL Server名称（如添加随机后缀），验证是否能成功部署。
• 修复代码参数传递问题：当前模块内的SQL Server名称、管理员账号为硬编码，未使用传入的模块参数，可能导致配置不一致。修改create-sql.bicep，替换硬编码值为参数：

  param server_name string
  param admin_username string
  param admin_password string
  param db_name string
  
  resource sqlServer 'Microsoft.Sql/servers@2021-11-01' = {
    name: server_name
    location: resourceGroup().location
    properties: {
      administratorLogin: admin_username
      administratorLoginPassword: admin_password
      administrators: {
        administratorType: 'ActiveDirectory'
        principalType: 'Group'
        login: 'myGroup'
        sid: 'xxxxxxxxxxxxxx'
        tenantId: tenant().tenantId
        azureADOnlyAuthentication: false
      }
    }
  }
  
  resource sqlDB 'Microsoft.Sql/servers/databases@2020-08-01-preview' = {
    parent: sqlServer
    name: db_name
    location: resourceGroup().location
    sku: {
      name: 'standard'
      tier: 'standard'
      capacity: 10
    }
    properties: {
      collation: 'SQL_Latin1_General_CP1_CS_AS'
    }
  }
  

• 延长部署超时时间：Azure默认部署超时可能不足以完成复杂资源部署，通过--timeout参数延长部署时间（单位：分钟，最大180）：

  az deployment group create --name deploy_sql --template-file .\create-sql.bicep --resource-group my-resource-group --output jsonc --timeout 120
  

• 验证AD管理员配置：若AD组SID或租户ID配置错误，会导致SQL Server部署过程中身份验证环节耗时过长甚至失败。确认AD组SID正确性，或临时移除AD管理员配置，先部署基础SQL Server资源，成功后再添加AD身份验证。
• 查看详细部署日志：执行以下命令获取具体资源的部署操作日志，定位超时的具体环节：

  az deployment group operation list --name deploy_sql --resource-group my-resource-group --output jsonc
  

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