Deploying Synapse SQL Serverless objects across environments using SSDT

At Microsoft, we love to hear your voice! The Synapse community asked for SqlPackage’s long-awaited support for Azure Synapse Analytics Serverless SQL Pools, and we delivered! Now you can automate the deployment of your Synapse serverless SQL objects along with your Synapse dedicated SQL objects!

 

SqlPackage release 161.8089.0, dated February 13, 2023, brings a new feature: support for Synapse serverless SQL pools. According to the release notes, you can use SqlPackage to extract and publish both external and internal objects from serverless SQL pools. This includes the T-SQL surface area of serverless SQL pools is limited by design to external tables, external views, stored procedures, functions, statistics, and credentials. 

 

The following features are included in support for serverless SQL pools:

• delta external file format
• openrowset provider CosmosDB with PROVIDER, CONNECTION, OBJECT, and CREDENTIAL or SERVER_CREDENTIAL specified
• openrowset format SStream
• with () clause on openrowset

 

Quick demonstration

In this article I will demonstrate how you can use the Azure SQL Datawarehouse Deployment task in a DevOps pipeline to replicate your SQL serverless objects from a lower environment (DEV) to a target environment (UAT). If you don't have this task installed in your DevOps organization, you can check out Azure SQL Data Warehouse deployment for more information.

 

Step 1: Configuring the Release Pipeline in Azure DevOps

After accessing your Azure DevOps Project, from the left navigational bar, select "Pipelines" and then "Releases".

 

 

 

Select "New" and then "+ New release pipeline" to start configuring your new release pipeline.

 

 

When prompted for a template, select "Empty job".

 

 

I'm renaming the stage as "SQL OD Deployment"

 

 

 

Step 2: Configuring the Azure SQL Data Warehouse deployment tasks

After renaming the stage, select the task link to start the tasks configuration.

 

 

You need to add two tasks to your stage:

1. A first one that will run the SqlPackage utility using the EXTRACT action to generate the DACPAC file from the source database;
2. A second one that will run the SqlPackage utility using the PUBLISH action to deploy the DACPAC to the target database;

 

Let's start by adding the first (EXTRACT) task:

 

Hit the plus "+" sign to add a new task to the stage.

 

Search for "sql data warehouse" to select the "Azure SQL Data Warehouse deployment" task.

Note: If you haven't installed the task yet, it will show under the "Marketplace" section. You just need to hit the "Get it free" button to install this extension in your Azure DevOps organization.

 

 

After adding this task to your stage, you need to populate these items to complete the task configuration.

 

Notice that I'm not exposing the user credentials directly on the task. Instead, I'm using two Pipeline variables, that can be defined by selecting the "Variables" tab. You can reference these variables in your task by using $(variable_name).

 

Also notice that the "pass" variable type was changed to secret instead of plain text. You can switch the variable type by clicking on the locker button.

 

 

 

Back to the EXTRACT task, after configuring the items above, you must configure the task's Deployment Package section. You must select the "SQL DAPAC file" deploy type and the "Extract" action. Under "Output Variables", don't forget to add a reference name to the generated output file. This variable will be used by the next task, as it will require the DACPAC file location.

 

 

Now that you have completed this task configuration, you must add a second task, which will be responsible for publishing this DACPAC to your target Serverless instance.

 

When configuring this task, you must provide the target server name (the serverless SQL endpoint) and the target database name (in case the target database does not exist in your server, it will be automatically created).

 

Select the "SQL DACPAC file" deploy type and the "Publish" action. You will use the "dacpac" reference name (output variable) from the first task, as it contains the file path to the generated DACPAC.

 

 

 

After completing the tasks configuration, you can "Save" the release pipeline and hit the "Create release" button.

 

 

Select the "Create button" to start your deployment. To track the release progress, click on the release name and then mouse over the stage to select the "Logs" button.

 

 

 

When viewing the "EXTRACT the DACPAC" task log, you can see the SqlPackage cmd line utility being executed using the "Extract" action:

 

 

and your output variable (dacpac) pointing to the DACPAC file location. 

 

 

 

When viewing the "PUBLISH the DACPAC" task log, you can see the SqlPackage cmd line utility being executed using the "Publish" action and the DACPAC file path referenced by the output variable "dacpac".

 

 

 

And that's it! This is how you can easily deploy your Synapse SQL Serverless objects across different environments/workspaces.

 

Conclusion

You can now take advantage of SSDT new features to improve the CICD lifecycle for your Synapse SQL pools by automating the deployment of Synapse Serverless SQL pools objects.

 

In this article, we have covered a simple approach using Azure DevOps release pipelines to replicate your SQL serverless pool objects from a lower environment (DEV) to a target environment (UAT). Keep in mind that, as of the time of writing, the T-SQL surface area of serverless SQL pools is limited by design to external tables, external views, stored procedures, functions, statistics, and credentials.