We have a requirement of deploying the stored procedures to UAT and Test that is being created in the serverless synapse SQL environment (XXXXX-ondemand.sql.azuresynapse.net). Could someone please assist in providing the step by step process or document on how to perform CICD process of serverless synapse SQL objects through Azure Devops?

Hello @Tej , Welcome to the MS Q&A platform. Please correct me if my understanding is wrong. You want to know how to deploy SQL objects(SPs) to an existing Serverless SQL pools in UAT and test environments through Azure DevOps. If this is your requirement, the below thread explains the deployment of SQL scripts on Serverless SQL pools. https://learn.microsoft.com/en-us/answers/questions/651865/azure-synapse-serverless-sql-pool-cicd.html Or if you want to deploy/migrate the following SQL Objects- Database(serverless SQL pools), Database Master Key Encryption, Database Scoped Credential, Schema, External Data Sources, External File Formats, External Tables, Views, and Stored Procedures, please follow the below thread. The Release Pipeline contains the Azure Synapse Workspace Deployment task which is followed by a PowerShell task responsible to deploy the Serverless SQL Pools. The PowerShell script is attached to the answer. https://learn.microsoft.com/en-us/answers/questions/969053/serverless-db-creation-using-ci-cd-in-synapse.html Please let us know if you have any further questions.

I will leave this here in case anybody is still looking to do this using Azure DevOps. https://www.kevinrchant.com/2023/02/15/deploying-a-dacpac-to-a-serverless-sql-pool/

@ BhargavaGunnam-MSFT, Please post steps for Deployment process of Dedicated SQL Pool

Deployment of Synapse Serverless SQL pools through Azure Devops (CI/CD)

Accepted answer

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-12T21:16:01.447+00:00

Hello @Tej ,

Welcome to the MS Q&A platform.
Please correct me if my understanding is wrong. You want to know how to deploy SQL objects(SPs) to an existing Serverless SQL pools in UAT and test environments through Azure DevOps.

If this is your requirement, the below thread explains the deployment of SQL scripts on Serverless SQL pools.

https://learn.microsoft.com/en-us/answers/questions/651865/azure-synapse-serverless-sql-pool-cicd.html

Or if you want to deploy/migrate the following SQL Objects- Database(serverless SQL pools), Database Master Key Encryption, Database Scoped Credential, Schema, External Data Sources, External File Formats, External Tables, Views, and Stored Procedures, please follow the below thread. The Release Pipeline contains the Azure Synapse Workspace Deployment task which is followed by a PowerShell task responsible to deploy the Serverless SQL Pools. The PowerShell script is attached to the answer.

https://learn.microsoft.com/en-us/answers/questions/969053/serverless-db-creation-using-ci-cd-in-synapse.html

Please let us know if you have any further questions.
Please sign in to rate this answer.
Tej 146 Reputation points

2022-09-13T12:16:02.553+00:00

Hi @Bhargava-MSFT , Thanks for your reply. Could you please help assist on couple of things which are specified below.

1) I have tried using the first method and was able to make rest API call to azure synapse analytics SQL script, I was able to run the script and behind the scenes stored procedure was getting created.

But my look up activity is getting failed due to the below error. I understand lookup activity fails when there is no query provided to extract some data, how to overcome this?

"Operation on target LookupToRunSQLScripts failed: Failure happened on 'Source' side. ErrorCode=SqlInvalidDbQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified SQL Query is not valid. It could be caused by that the query doesn't return any data. Invalid"

I have tried writing the dynamic SQL expression as below, but its not getting executed successfully due to error

("message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Must declare the scalar variable

Tej 146 Reputation points

2022-09-13T12:20:46.583+00:00

Hi @Bhargava-MSFT , I would like to test deployment of serverless SQL pools using powershell script, but the steps which was provided are not understandable like for which environment either Source or target these variables (Resource Group, Synapse Workspace, SQL Server Instance, SQL Database, Database Schema, Database Scoped Credential, Master Key Encryption Password, ClientID, TenantID, and Client Secret ) have been created and how the azure repos folder hierarchy have generated . Could you please provide document or step by step process or any document to understand better?

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-13T23:31:46.333+00:00

Hello @Tej ,

if you look at the PowerShell script, the parameters have been defined at the start of the script.
You will need to link the variable groups to the release pipeline with the appropriate values for the variables.
Once defined these variables, We will pass these values as arguments in the PowerShell task. These are the target resources. Please see the screenshot for your reference.

param (
$DefaultDirectory,
$SqlServer,
$Database,
$DatabaseSchema,
$DatabaseScopedCredential,
$DatabaseMasterKey,
$ClientId,
$ClientSecret,
$TenantId
)

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-13T23:33:17.307+00:00

Hello @Tej ,
I will check and get back to you on this.

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-16T21:50:13.817+00:00

Hello @Tej ,
I see you have opened another thread specific to this error.
If both questions are resolved, please consider accepting the answer by hitting the Accept answer button, as it helps the community.

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-19T21:51:01.147+00:00

Hello @Tej ,
I am just checking in to see if you have any further questions here.

Tej 146 Reputation points

2022-09-20T13:22:07.493+00:00

Hi @Bhargava-MSFT ,

Thanks for the support you have provided, my issue has been resolved.

Bhargava-MSFT 31,121 Reputation points Microsoft Employee

2022-09-20T17:11:24.153+00:00

Thank you, @Tej
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Deployment of Synapse Serverless SQL pools through Azure Devops (CI/CD)

2 additional answers

Your answer