Querying 1 serverless sql server db from another serverless sql server db

Ankita Gupta 0

Hello -
I would like to check if this scenario is possible in azure synpase analytics.

Query data of serverless SQL in azure synapse analytics from another serverless sql server.

We have a requirement , where we want to create views in the second server and query data from 1st server due to the access limitation.

1st server- Read only access.

2nd Server- Admin access.

Looking forward to the response.

Ankita Gupta 0 Reputation points

2024-05-07T07:13:40.0733333+00:00

Hello - Do you have any other suggestion on this ?The link that you have shared is speaks about azure dedicated sql server not serverless sql.

1 answer

Vinodh247 25,291 Reputation points MVP

2024-05-06T14:08:37.5233333+00:00

Hi Ankita Gupta,

Thanks for reaching out to Microsoft Q&A.

Try using CETAS

https://stackoverflow.com/questions/66531172/join-data-in-azure-synapse-from-dedicated-sql-pool-and-serverless-sql-pool

you can also define external tables pointing to the data in the other instance and query them like regular tables or using synapse linked service you can query or use it for data movement.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Please sign in to rate this answer.
Ankita Gupta 0 Reputation points

2024-05-06T14:15:20.7533333+00:00

I have tried all possible options to create external tables , however seems like datasource creation does not support in serverless sql.
Tried using - SQLDATAPOOL, RDBMS etc.

Using synapse linked service you can query- Do we need to copy data to somewhere in this case ?
We do not want to store data in the server.

Vinodh247 25,291 Reputation points MVP

2024-05-08T14:30:27.2533333+00:00

create linked service to your server 1 from where you are going to source your data and then use synapse pipelines. the lookup activity can get you generate data that you want to create the view. try this and let me know if it works.

Ankita Gupta 0 Reputation points

2024-05-13T10:04:40.4366667+00:00

I am unable to create linked service for serverless sql server as it gives ask for below info and i believe serverless sql server do not have these properties :
Source SQL logical server resource ID
Source managed identity ID

Do you know how can i get this ?

Vinodh247 25,291 Reputation points MVP

2024-05-14T10:55:57.95+00:00

okay, leave the linked service, did you try calling the db directly in the select query?
Sign in to comment

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

Share via

Querying 1 serverless sql server db from another serverless sql server db

1 answer

Your answer