Support for Synapse Serverless Database

[leo-schick]

Is there an existing issue for this?

• I have searched the existing issues

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Description

I would like to be able to create a Synapse Serverless Database in Synapse with an own resource.

This could be accomplished by sending the SQL command CREATE DATABASE to the database engine. See as well: https://docs.microsoft.com/en-us/azure/synapse-analytics/quickstart-serverless-sql-pool#create-database

I am not firm with SQL Pools but it might be helpful there as well

New or Affected Resource(s)/Data Source(s)

azurerm_synapse_sql_database

Potential Terraform Configuration

No response

References

No response

[slanton-a]

@leo-schick any updates on this? did you managed to create this with Terraform?

[leo-schick]

No. I created it now manually and live with it 🫤 hope this gets added soon

[Red1971]

No. I created it now manually and live with it 🫤 hope this gets added soon

Good day. Are you able to share the steps you did to create a "serverless db" on Synapse?

[Red1971]

leo-schick, thanks. I am looking to create this in Terraform via a Azure devOps pipeline.

[slanton-a]

@Red1971 Those are the step I made inside of Synapse, not sure if you can use it in devOps pipeline.

1. Create database

CREATE DATABASE serverless_db;

2. Allow DB to be case-sensitive (if needed)

ALTER DATABASE serverless_db COLLATE SQL_Latin1_General_CP1_CS_AS;

3. Create an external file format for PARQUET files.

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] 
    WITH ( FORMAT_TYPE = PARQUET)
GO

4. Create an external data source

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'datasource_name') 
    CREATE EXTERNAL DATA SOURCE [datasource_name] 
    WITH (
        LOCATION   = 'https://yourstorageaccount.blob.core.windows.net/some-container', 
    )
Go

5. Create view

CREATE OR ALTER VIEW view_name
AS SELECT *, 
    -- partition data from filepath
    view_name_data.filepath(1) AS [customer_id], 
    view_name_data.filepath(2) AS [year],
    view_name_data.filepath(3) AS [month], 
    view_name_data.filepath(4) AS [version]
FROM
    OPENROWSET(
        BULK '/customer_id=*/year=*/month=*/version=*/data.parquet',
        DATA_SOURCE = 'datasource_name',
        FORMAT='PARQUET'
    )
WITH(
-- map all columns in data to prevent Synapse from trying to figure out the schema and missing new columns
-- note that this query should be updated and re-run each time you make changes to the schema
component_id VARCHAR(40), 
column_1 INT, 
column_2 INT, 
column_3 DATETIME, 
column_4 FLOAT, 
column_5 VARCHAR(40)
) AS view_name_data

[Red1971]

Thanks for the reply, I am sure this would work.

I ended up using Terraform to create the Synapse Serverless DB.

terraform {

required_providers {
azurerm = {
source = "hashicorp/azurerm"
version = "3.84.0"
}

}
}

resource "null_resource" "synapse_create_db" {
provisioner "local-exec" {
command = <<EOT
az login --service-principal -u ${var.deploymentSpAzureDevOps_Sec} -p ${var.deploymentSpAzureDevOps_EA_Client_Id} --tenant ${var.tenant_id}
$accessToken = az account get-access-token --resource "https://database.windows.net/" | ConvertFrom-Json | select-object -ExpandProperty accessToken
$ServerInstance = '${var.sqlOnDemand}'
$Database = "master"
function SqlQuery($ServerInstance, $Database, $AccessToken, $query)
{
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=$ServerInstance;Database=$database;"
$connection.AccessToken = $AccessToken
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$connection.Close()
return $table
}
$SQLQuery = @"
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '${local.databasename}')
CREATE DATABASE [${local.databasename}];
"@
SqlQuery -ServerInstance $ServerInstance -Database $Database -AccessToken $AccessToken -query $SQLQuery
EOT
interpreter = ["pwsh", "-Command"]
}
triggers = {
trigger_each_run = uuid()
}
}

[alexIvan20]

Any updates? We would like to use this feature on a use-case, as we don't want to create the SQL database manually.