azure - 如何通过 ARM 模板部署 Synapse Serverless 数据库？

Question

简介

我尝试通过 Azure Pipelines 部署 ARM 模板，其中部署了 Synapse 工作区，随后创建了 Synapse 无服务器 数据库。 请注意，这不是专用 SQL 池，而是 Synapse 无服务器数据库。

执行此操作的“点击式”方法是:

<小时/>

现有模板

我有一个 ARM 模板，可以成功创建我的 Synapse 工作区:

    {
        "type": "Microsoft.Synapse/workspaces",
        "apiVersion": "2021-06-01",
        "name": "[variables('synapseName')]",
        "location": "[parameters('location')]",
        "tags": "[parameters('tags')]",
        "identity": {
            "type": "SystemAssigned"
        },
        "properties": {
            "defaultDataLakeStorage": {
                "resourceId": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageName'))]",
                "createManagedPrivateEndpoint": true,
                "accountUrl": "[concat('https://', variables('storageName'),'.dfs.core.windows.net')]",
                "filesystem": "[concat('lake', parameters('project'))]"
            },
            "encryption": {},
            "managedResourceGroupName": "[variables('synapseManager')]",
            "managedVirtualNetwork": "default",
            "sqlAdministratorLogin": "[variables('adminusername')]",
            "sqlAdministratorLoginPassword": "[parameters('sec_syn')]",
            "privateEndpointConnections": [],
            "publicNetworkAccess": "Enabled",
            "managedVirtualNetworkSettings": {
                "preventDataExfiltration": false,
                "allowedAadTenantIdsForLinking": []
            },
            "cspWorkspaceAdminProperties": {
                "initialWorkspaceAdminObjectId": "[parameters('cliid')]"
            },
            "trustedServiceBypassEnabled": true,
            "azureADOnlyAuthentication": false
        },
        "resources": [
            {
                "condition": true,
                "type": "firewallRules",
                "apiVersion": "2021-06-01",
                "name": "AllowAllConnections",
                "properties": {
                    "startIpAddress": "0.0.0.0",
                    "endIpAddress": "255.255.255.255"
                },
                "dependsOn": [
                    "[resourceId('Microsoft.Synapse/workspaces', variables('synapseName'))]"
                ]
            },
            {
                "condition": true,
                "type": "firewallRules",
                "apiVersion": "2021-06-01",
                "name": "AllowAllWindowsAzureIps",
                "properties": {
                    "startIpAddress": "0.0.0.0",
                    "endIpAddress": "0.0.0.0"
                },
                "dependsOn": [
                    "[resourceId('Microsoft.Synapse/workspaces', variables('synapseName'))]"
                ]
            },
            {
                "condition": true,
                "type": "integrationRuntimes",
                "apiVersion": "2021-06-01",
                "name": "AutoResolveIntegrationRuntime",
                "properties": {
                    "type": "Managed",
                    "typeProperties": {
                        "computeProperties": {
                            "location": "AutoResolve"
                        }
                    }
                },
                "dependsOn": [
                    "[resourceId('Microsoft.Synapse/workspaces', variables('synapseName'))]"
                ]
            }
        ],
        "dependsOn": [
            "[resourceId('Microsoft.Storage/storageAccounts/blobServices/containers', variables('storageName'), 'default', concat('lake', parameters('project')))]"
        ]
    }

<小时/>

创建 Synapse Serverless SQL 数据库的选项

但是，似乎有两个用于创建数据库的已记录选项。我已经尝试过如下所示:

• sqlDatabase

  {
      "type": "sqlDatabases",
      "apiVersion": "2020-04-01-preview",
      "name": "laketestdb",
      "location": "[parameters('location')]",
      "properties": {
          "collation": "Latin1_General_100_BIN2_UTF8"
      },
      "dependsOn": [
          "[resourceId('Microsoft.Synapse/workspaces', variables('synapseName'))]"
      ]
  }
  

此方法嵌套在 Synapse 资源模板中，但我也在后续管道任务中在外部运行它；部署过程中出现同样的错误并且失败:

• sqlPool

  {
      "type": "Microsoft.Synapse/workspaces/sqlPools",
      "apiVersion": "2021-06-01",
      "name": "string",
      "location": "string",
      "tags": {
          "tagName1": "tagValue1",
          "tagName2": "tagValue2"
      },
      "sku": {
          "capacity": "int",
          "name": "string",
          "tier": "string"
      },
      "properties": {
          "collation": "string",
          "createMode": "string",
          "maxSizeBytes": "int",
          "provisioningState": "string",
          "recoverableDatabaseId": "string",
          "restorePointInTime": "string",
          "sourceDatabaseDeletionDate": "string",
          "sourceDatabaseId": "string",
          "storageAccountType": "string"
          }
   }
  

通过这种方法，我根本不知道是否有服务层或 SKU 可以创建无服务器数据库。所有在线示例(其中很少)似乎都显示了典型的数据仓库配置(例如“DW2000c”)。我无法通过猜测让它发挥作用。

<小时/>

Azure Shell 尝试

我还注意到，当我使用 Azure Shell 命令时:

New-AzSynapseSqlDatabase -ResourceGroupName rg_admin -WorkspaceName synjmi -Name testazpssqldb

我遇到了与部署期间相同的错误，即:

New-AzSynapseSqlDatabase: Operation returned an invalid status code 'BadRequest' Specified edition or service level objective is not supported

我完全不知道出了什么问题，并且似乎无法在网上找到太多支持文档或故障排除 Material 。

非常感谢任何帮助。

Answer 1

我与 ChatGPT 进行了长时间的交谈才弄清楚这一点，但我将在这里引用它，因为它解释得很好。我也通过尝试证实了这一点:

Azure Synapse serverless SQL pools do not have child resources for databases in the same way that dedicated SQL pools (formerly known as SQL Data Warehouses) do.

In a serverless SQL pool, databases are not explicitly created as child resources under the SQL pool. Instead, databases are effectively created on-demand when you submit SQL statements to work with them. This is one of the key differences between serverless SQL pools and dedicated SQL pools.

To work with databases in a serverless SQL pool, you typically do not need to explicitly create them in advance through an ARM template. Instead, you interact with the serverless SQL pool by connecting to it and issuing SQL statements to create or manipulate databases and their objects as needed.

因此，一旦模板部署了 Synapse 工作区，您就可以连接到内置无服务器 SQL 实例并针对主数据库运行 CREATE DATABASE myDatabaseName SQL 语句。

如果您需要在 ARM 模板中执行此操作，则可以使用 deploymentScript 资源来运行 SQL 命令。这里显示了 BICEP 示例:

param location string = resourceGroup().location
param serverFQName string 
param adminUsername string
@secure()
param adminPassword string
param newDatabaseName string

resource deploymentScript 'Microsoft.Resources/deploymentScripts@2020-10-01' = {
  name: 'createDatabaseScript'
  location: location
  kind: 'AzurePowerShell'
  properties: {
    azPowerShellVersion: '3.0'
    environmentVariables: [
      {
        name: 'serverFQName'
        value: serverFQName
      }
      {
        name: 'adminPassword'
        secureValue: adminPassword
      }
      {
        name: 'adminUsername'
        value: adminUsername
      }
      {
        name: 'databaseName'
        value: newDatabaseName
      }
    ]
    scriptContent: '''
      # Connect to the Synapse Analytics server
      $connStr = "Server=tcp:${Env:serverFQName};Initial Catalog=master;Persist Security Info=False;User ID=${Env:adminUsername};Password=${Env:adminPassword};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
      $conn = New-Object System.Data.SqlClient.SqlConnection
      $conn.ConnectionString = $connStr
      $conn.Open()

      # Execute the CREATE DATABASE command
      $sqlCommand = "CREATE DATABASE [${Env:databaseName}]"
      $cmd = $conn.CreateCommand()
      $cmd.CommandText = $sqlCommand
      $cmd.ExecuteNonQuery()

      # Clean up
      $conn.Close()
    '''
    retentionInterval: 'PT1H'
  }
}