Deploying Synapse SQL Serverless objects across environments using SSDT

Microsoft

May 25, 2023

Hi folks,
Thanks for sharing your feedback! I agree with your comments as this is still very limited, we are in the early days of CICD for serverless, long road ahead 🙂
we have been working hard to improve the tooling and we expect this to be supported in VS2022 very very soon, stay tuned 🙂

although not being an elegant solution, I've been working on this approach below, using a yaml pipeline to build the incremental script to update the target schema + a release pipeline to deploy this script. Feel free to test and modify this code, adjusting the logic to your scenario, I'm sharing this code as a baseline for dev/testing purposes only.

Here's the logic being used:

1- use sqlpackage to extract the dacpac from the source pool
2- use sqlpackage to create a Transact-SQL incremental update script that will be used to update the schema of the target pool

3-use powershell to replace the t-sql script content (adding secrets to database scoped credentials using SAS, changing storage account names, replacing SAS secrets) and generate a new (final) t-sql script to update the target schema
4-publish the script as an artifact so it can be used by the release pipeline, during the CD workflow.

steps from the YAML:

steps:

- task: SqlAzureDataWarehouseDacpacDeployment@1

displayName: 'EXTRACT THE DACPAC FROM SOURCE POOL'

inputs:

azureSubscription: '$(v_subsc)'

AuthenticationType: 'server'

ServerName: '$(v_source_server)'

DataWarehouse: '$(v_sourcepool)'

SqlUsername: '$(user)'

SqlPassword: '$(pass)'

deployType: 'DacpacTask'

DeploymentAction: 'Extract'

IpDetectionMethod: 'AutoDetect'

- script: '"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /action:script /SourceFile:$(System.DefaultWorkingDirectory)\GeneratedOutputFiles\$(v_dacpacfile) /TargetConnectionString:"Server=tcp:$(v_target_server),1433;Database=$(v_targetpool);User ID=$(user);Password=$(pass);Trusted_Connection=False;Encrypt=True;" /outputpath:GeneratedOutputFiles/$(v_scriptname_orig)'

displayName: 'GENERATE SQL SCRIPT FROM SOURCE DACPAC'

- task: PowerShell@2

displayName: 'TRANSFORMATION - SAS Credentials'

inputs:

targetType: 'inline'

script: |

(Get-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_orig)) | Foreach-Object {

$line = $_

if ( $line | Select-String -Pattern 'CREATE DATABASE SCOPED CREDENTIAL ' ) {

$pattern = '(?<=\[).+?(?=\])'

$tempcred = [regex]::Matches($line, $pattern).Value

$cred = 'secret_' + $tempcred

}

$line -replace "WITH IDENTITY = N'SHARED ACCESS SIGNATURE';" , "WITH IDENTITY = N'SHARED ACCESS SIGNATURE' , SECRET = '$cred';"

} | Set-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)

- task: PowerShell@2

displayName: 'TRANSFORMATION - Storage naming'

inputs:

targetType: 'inline'

script: |

(Get-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)) | Foreach-Object {

$_ -replace 'stgsyncicddev', 'stgsyncicduat' -replace 'sqlondemandstorage', 'sqlondemandstorageuat' -replace 'stgadbadfdemo', 'stgadbasademo'

} | Set-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)

- task: PowerShell@2

displayName: 'TRANSFORMATION - Replacing SAS Secrets'

inputs:

targetType: 'inline'

script: |

(Get-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)) | Foreach-Object {

$_ -replace 'secret_mycred', "$(v_secret_mycred)" -replace 'secret_SasToken', "$(v_secret_sastoken)"

} | Set-Content $(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)

- task: PublishPipelineArtifact@1

displayName: 'PUBLISH ARTIFACTS - DACPAC SCRIPT - FINAL'

inputs:

targetPath: '$(System.DefaultWorkingDirectory)/GeneratedOutputFiles/$(v_scriptname_final)'

artifact: finalscript

Then, to deploy this script, you can setup a release pipeline, for example, adding the Azure SQL Datawarehouse Deployment task and using "SQL Script file" as Deploy type. of course you need to add the script file as an artifact to your release.

again, not an elegant solution, but something that you can evaluate to implement your CICD workflows for serverless, until we get the support in VS2022 🙂

Blog Post

Deploying Synapse SQL Serverless objects across environments using SSDT