synapse sql database with external table

Question

synapse sql database with external table

Hiral Shah 141

Hello,

I am trying to create the repo for synapse workspace. when I connect to azure git repos, I could create all the resources like notebook, sqlscript, lake database, pipelines, triggers. But I do not see any folder for SQL database with external tables. Because I am not able to deploy sql database in UAT using CICD.

Please help me understanding this , if I am missing anything here.

Thanks in advance.

AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-16T06:27:25.957+00:00

Hi @Hiral Shah ,

Just following up to see if the answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-17T09:04:59.903+00:00

Hi @Hiral Shah ,

Just checking back to see if the answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-21T07:58:11.91+00:00

Hi @Hiral Shah ,
Just Following up to see if the above suggestion was helpful, kindly do click Accept Answer for the same as accepted answer helps community as well. In case you have any further query please do let us know.

2 answers

Your answer

AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-16T06:27:25.957+00:00

Hi @Hiral Shah ,

Just following up to see if the answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-17T09:04:59.903+00:00

Hi @Hiral Shah ,

Just checking back to see if the answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-21T07:58:11.91+00:00

Hi @Hiral Shah ,
Just Following up to see if the above suggestion was helpful, kindly do click Accept Answer for the same as accepted answer helps community as well. In case you have any further query please do let us know.

Answer 1

AnnuKumari-MSFT 34,361 Microsoft Employee

Hi @Hiral Shah ,

Thankyou for using Microsoft Q&A platform and posting your query.

As I understand your query here is that when you do deployment for Azure synapse analytics using Azure DevOps , it's taking care of migrating all the codes for all resources like : notebook, sql scripts, lake database, pipelines, triggers but not SQL datawarehouse objects or dedicated SQL pool objects. Please let me know if my understanding is wrong.

Unfortunately, that capability is missing. Deployment of dedicated SQL pool objects is external to Synapse deployment task.

As workaround, you can use Flyway installer task and Flyway CLI task which includes Baseline,Migrate,Repair and Clean commands for SQL database objects.

Please refer to the following article: https://flywaydb.org/documentation/

Hope this will help. Please let us know if any further queries.

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

Please don't forget to click on or 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

Hiral Shah 141 Reputation points

2022-03-03T17:49:02.543+00:00

So basically , you are saying I have to add Flyway installer task and Flyway CLI task to my release pipeline for synapse ? or build pipeline?
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-04T13:00:32.513+00:00

Hi @Hiral Shah ,
Yes that is one suggested workaround which will take care of deploying SQL objects for Synapse. However, I have reached out to our internal product team for checking if there is any other better approach . I will let you know once I hear back from them . Thanks !
Hiral Shah 141 Reputation points

2022-03-04T15:55:28.04+00:00

Thanks for responding. I am looking forward to the solution for it.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-04T18:08:45.38+00:00
Hi @Hiral Shah ,

I got the response from the product team . This is what they have quoted:

Dedicated SQL Pool objects don’t get deployed along with the workspace. The dedicated SQL Pool objects deployment is separate and Synapse Workspace Git integration and deployment is separate

Source Control Integration - Azure Synapse Analytics | Microsoft Learn this shows how to integrate the SQLpool objects – tables,views,stored procedures using Visual Studio SSDT to the repository

Please refer this blog on how to do build pipeline(CI pipeline where dacpac is generated and built) an Release pipeline(CD pipeline deploys the dacpac file to the target database) : Azure SQL Database and Synapse SQL Pool Azure DevOps CI/CD

If the suggested response helped you, please click Accept Answer and kindly upvote the same as accepted answer helps community as well.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-07T06:05:09.117+00:00

Hi @Hiral Shah ,
We haven’t heard from you on the last response and was just checking back to see if the suggested response helped you, please click Accept Answer and kindly upvote the same as accepted answer helps community as well.
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-08T10:46:15.177+00:00

Hi @Hiral Shah ,
Just following up to see if the answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
Hiral Shah 141 Reputation points

2022-03-08T15:55:36.933+00:00

still looking for the solution, can I use the same approach for serverless sql in synapse. ?
AnnuKumari-MSFT 34,361 Reputation points Microsoft Employee

2022-03-11T05:56:07.853+00:00

Hi @Hiral Shah ,

Yes Same goes with Serverless SQL pool as well.

Please click Accept Answer and kindly upvote the same. If you have any further questions about this answer, please do Comment.
Stéphane Paquette 0 Reputation points

2023-03-02T18:42:30.51+00:00

We're using Flyway in our PaaS environnement and on-prem. So we want to use Flyway with Azure Synapse also.
I can deploy sql scripts on Synapse Dedicated pool from a Flyway client.
With Synapse Serverless we can only create views and external tables, so I'm not sure that Flyway can be used as there is no place for Flyway to create its history table, unless we can create the Flyway history table on a dedicated pool
Stéphane Paquette 0 Reputation points

2023-03-02T18:52:01.28+00:00

We prefer migration base deployment instead of state base deployment (DB projects) as it is more predictable and forced you to deliver by small increments (agile)

Answer 2

AaronHughes 396

for the SQL Pool you will need to set up a visual studio project to store your db code in

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/get-started-visual-studio

you can then use standard DB CICD release the SQL code to your other environments

Hiral Shah 141 Reputation points

2022-03-09T21:05:21.52+00:00

I am still not able to do this for synapse sql databse with external tables.
Can you please provide more detail on the same ?

Share via

synapse sql database with external table

2 answers

Your answer