Run SQL scripts stored in Synapse dev instance on PPE & PROD env

Question

Run SQL scripts stored in Synapse dev instance on PPE & PROD env

Suraj Tripathi 77

Requirement is to run SQL scripts stored in Synapse Dev instance on subsequent PPE & PROD env. Please keep in minds following points :

In Synapse dev workspace, sql files are stored as .sql files
When pushed to repository, the files are stored as .json files.
During CD release pipeline the code flow through the env from dev to ppe and then to production.
But we also want to execute our sql scripts during release pipeline so that the changes in database would also flow through.

Please let me know how to do that.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-08T21:33:21.79+00:00

Hello @Suraj Tripathi ,

Welcome to the MS Q&A platform.

Please follow [this thread][1] to execute SQL scripts using devOps pipeline.

In case, if your requirement is to deploy the SQL scripts to PPE & prod from the dev environment, you can use [the Synapse deployment task][2]

![268762-image.png][3]

Please look into the prerequisites:

Spark pools and self-hosted integration runtimes aren't created in a workspace deployment task. If you have a linked service that uses a self-hosted integration runtime, manually create the runtime in the new workspace.
If the items in the development workspace are attached with the specific pools, make sure that you create or parameterize the same names for the pools in the target workspace in the parameter file.
If your provisioned SQL pools are paused when you attempt to deploy, the deployment might fail.

Please let me know if you have any further questions.
Suraj Tripathi 77 Reputation points

2022-12-09T04:20:00.517+00:00
Hi @Bhargava-MSFT ,

The requirement is to execute SQL scripts using devOps pipeline.
The thread that you have share has this power shell script :

Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(sqlserver) -Database $(dbname) -Username "$(username)" -Password "$(pwd)" -QueryTimeout 36000 -Verbose

There are 2 problems :

The SQL files in repository is in form of .json format (Since that is how it is stored in repo from synapse)

It can run 1 sql file at a time. How to run all the files in that folder ?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-09T19:54:44.603+00:00

Hello @Suraj Tripathi ,
Thanks for the reply. Let me do some research and get back to you.
And thank you for your patience.

2 answers

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-08T21:33:21.79+00:00

Hello @Suraj Tripathi ,

Welcome to the MS Q&A platform.

Please follow [this thread][1] to execute SQL scripts using devOps pipeline.

In case, if your requirement is to deploy the SQL scripts to PPE & prod from the dev environment, you can use [the Synapse deployment task][2]

![268762-image.png][3]

Please look into the prerequisites:

Spark pools and self-hosted integration runtimes aren't created in a workspace deployment task. If you have a linked service that uses a self-hosted integration runtime, manually create the runtime in the new workspace.
If the items in the development workspace are attached with the specific pools, make sure that you create or parameterize the same names for the pools in the target workspace in the parameter file.
If your provisioned SQL pools are paused when you attempt to deploy, the deployment might fail.

Please let me know if you have any further questions.
Suraj Tripathi 77 Reputation points

2022-12-09T04:20:00.517+00:00

Hi @Bhargava-MSFT ,

The requirement is to execute SQL scripts using devOps pipeline.
The thread that you have share has this power shell script :

Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(sqlserver) -Database $(dbname) -Username "$(username)" -Password "$(pwd)" -QueryTimeout 36000 -Verbose

There are 2 problems :

The SQL files in repository is in form of .json format (Since that is how it is stored in repo from synapse)

It can run 1 sql file at a time. How to run all the files in that folder ?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-09T19:54:44.603+00:00

Hello @Suraj Tripathi ,
Thanks for the reply. Let me do some research and get back to you.
And thank you for your patience.

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello @Suraj Tripathi ,

I see the only option to execute SQL scripts via Azure DevOps is using PowerShell or by using the extension “Azure SQL database deployment” and selecting Deploy type as "SQL dacpac file" or "SQL script file"

This blog explains how to deploy the SQL scripts using the dacpac file. Since you have multiple SQL files, you can use the dacpac file method.

I hope this helps. Please let me know if you have any further questions.

------------------------------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-12T23:31:31.093+00:00

Hello @Suraj Tripathi ,
I am checking to see if you got a chance to look into my above answer.
Suraj Tripathi 77 Reputation points

2022-12-13T05:54:23.083+00:00
@Bhargava-MSFT ,
The SQL pools which I am using is serverless SQL Pool. And SSDT is supported for dedicated only (best-deployment-strategy-for-synapse-serverless-sq.html).
Thanks for the answer but it doesn't help me with the 2 points I raised.

The SQL files in repository is in form of .json format (Since that is how it is stored in repo from synapse)

It can run 1 sql file at a time. How to run all the files in that folder?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-13T17:06:50.003+00:00

Hello @Suraj Tripathi ,
Sorry, I thought you were using the dedicated SQL pool. For dedicated SQL pools, you will need to use the DACPAC file.
For serverless SQL deployment, please follow the answer mentioned in the below thread
https://learn.microsoft.com/en-us/answers/questions/969053/index.html
Please let me know if you have any further questions.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-19T14:55:56.817+00:00

Hello @Suraj Tripathi ,
I am checking to see if you have any further questions here.
wctoddy 0 Reputation points

2024-05-10T16:50:41.7966667+00:00

Hello Bhargava Gunnam-MSFT, I hope you are well. I can't run this part of the script - "Invoke-Sqlcmd -Query $query -ServerInstance $sqlServerInstance -Database $database -AccessToken $access_token -QueryTimeout 36000 -ErrorAction 'Stop'". The error I have is this "Invoke-Sqlcmd: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces,

error: 25 - Connection string is not valid)".

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Run SQL scripts stored in Synapse dev instance on PPE & PROD env

2 answers

Your answer