How can I deploy a Synapse Serverless database via an ARM template?
Introduction
I'm trying to deploy an ARM template through Azure Pipelines in which a Synapse Workspace is deployed and a Synapse Serverless Database is created thereafter. Please note, this is not a Dedicated SQL Pool, it is a Synapse Serverless Database.
The 'clicky' way to do this is:
Existing Template
I have an ARM template that is successfully creating my Synapse Workspace:
{
"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')))]"
]
}
Options to Create Synapse Serverless SQL Database
However, there appear to be two documented options for creating a database. I have tried both shown below:
-
{ "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'))]" ] }
This approach is nested within the Synapse resource template, but I have also run it externally in a follow-up pipeline task; the same error occurs and it fails during deployment:
-
{ "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" } }
With this approach, I simply don't know if there's a service tier or sku that will create a serverless database. All examples online (of which there are few) seem to show typical data warehouse provisioning ("DW2000c", for example). I haven't been able to get it working with guesswork.
Azure Shell Attempt
I also notice that when I use the Azure Shell command:
New-AzSynapseSqlDatabase -ResourceGroupName rg_admin -WorkspaceName synjmi -Name testazpssqldb
I get the same error as I get during the deployment, namely:
New-AzSynapseSqlDatabase: Operation returned an invalid status code 'BadRequest' Specified edition or service level objective is not supported
I have absolutely no idea what's going wrong and can't seem to find much in the way of supporting documentation or troubleshooting materials online.
Any help is much appreciated.
It took me a long conversation with ChatGPT to figure this out, but I'm going to quote it here as it explains it quite well. I have also confirmed this by trying it out:
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.
So, once the template has deployed your Synapse workspace, you connect to the Built-in serverless SQL instance and run a CREATE DATABASE myDatabaseName SQL Statement against the master database.
If you need to do this in an ARM template, then you can use a deploymentScript resource in order to run the SQL Commands. A BICEP example shown here:
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'
}
}