Access Azure SQL DBs using Azure Synapse studio

Santhi Dhanuskodi 325

I would like to understand how Azure Synapse Analytics can be used within the organization to facilitate data analysis and development.

How will developers access Azure Datasets in a convenient way

Is there a case for using Azure Synapse?

I have a use case where developers need to access azure SQL database. We dont want to enable public access. We can not keep on adding their IPs in SQL firewall settings. or we dont want any azure VPN setup for the same. Can we solve the access issue if we use synapse studio or any other synapse solutions?

PRADEEPCHEEKATLA 90,261 Reputation points

2024-04-25T05:42:09.29+00:00

@Santhi Dhanuskodi - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

PRADEEPCHEEKATLA 90,261 Reputation points

2024-04-22T08:39:27.8666667+00:00

@Santhi Dhanuskodi - Thanks for the question and using MS Q&A platform.

Azure Synapse Analytics is a powerful tool that can help you with data analysis and development. It provides a unified experience for big data and data warehousing, and it can help you to easily access and analyze data from various sources.

Azure Synapse Link for Azure SQL Database is an option that you can consider for accessing Azure SQL databases. Azure Synapse Link is a cloud-native hybrid transactional and analytical processing (HTAP) service that allows you to analyze real-time data from Azure SQL databases using Azure Synapse Analytics.

With Azure Synapse Link, you can easily connect your Azure SQL databases to Azure Synapse Analytics and perform real-time analytics on your data. You can use familiar tools like T-SQL and Power BI to query and visualize your data, and you can also use Azure Synapse Studio to build end-to-end analytics solutions.

One of the key benefits of Azure Synapse Link is that it provides a low-latency, high-throughput connection between Azure SQL databases and Azure Synapse Analytics. This means that you can perform real-time analytics on your data without the need for complex ETL processes or data movement.

For more details, refer to Get started with Azure Synapse Link for Azure SQL Database.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Santhi Dhanuskodi 325 Reputation points

2024-04-22T13:31:55.15+00:00

my requirement is to access SQL database data. What tool/studio I can use for this? Heard that azure synapse can be used for the same. But not having complete details on that.

I dont want public ip access enabled or add ip address to firewall setting or access using azure vpn. I would like developers accessing the database in a simpler way without enabling any of these.

Azure Synapse Link - Any user specific settings or firewall settings or ip related required?....

PRADEEPCHEEKATLA 90,261 Reputation points

2024-04-23T03:34:19.6866667+00:00

As I said earlier, there are two options to achieve your requirement:

Option1: Using Private endpoint for Azure SQL Database.

To address your specific use case, Azure Synapse Analytics can help you provide secure access to Azure SQL databases without enabling public access or adding developer IPs to the SQL firewall settings. You can use Azure Synapse Studio to create a private endpoint for your Azure SQL database, which allows you to access the database over a private link instead of over the public internet. This provides a secure and private connection between your Azure Synapse workspace and your Azure SQL database.

To create a private endpoint for your Azure SQL database in Azure Synapse Studio, you can follow these steps:

Open Azure Synapse Studio and navigate to the "Manage" tab.

Click on "Private endpoints" and then click on "New".

Select your Azure SQL database and configure the private endpoint settings, such as the virtual network and subnet.

Once the private endpoint is created, you can use it to securely access your Azure SQL database from within Azure Synapse Studio.

Overall, Azure Synapse Analytics provides a powerful and flexible platform for data analysis and development, and it can help you address a wide range of data-related challenges and use cases.

Option2: Using Azure Synapse link for SQL

To access SQL database data securely without enabling public IP access or adding IP addresses to the firewall settings or using Azure VPN, you can use Azure Synapse Link. Azure Synapse Link is a cloud-native hybrid transactional and analytical processing (HTAP) service that enables you to run near real-time analytics on operational data in Azure SQL Database without the need to copy or move data.

Azure Synapse Link does not require any user-specific settings or firewall settings or IP-related configurations. It uses a secure and private connection between Azure Synapse Analytics and Azure SQL Database, and it does not require any additional infrastructure or configuration.

To use Azure Synapse Link, you can follow these steps:

Open Azure Synapse Studio and navigate to the "Data" tab.

Click on "Linked services" and then click on "New".

Select "Azure Synapse Link" as the linked service type and configure the settings, such as the Azure SQL Database and the database credentials.

Once the linked service is created, you can use it to query and analyze data in Azure SQL Database in near real-time.

Overall, Azure Synapse Analytics provides a powerful and flexible platform for accessing SQL database data, and it can help you address a wide range of data-related challenges and use cases.

Hope this helps. Do let us know if you any further queries.

Santhi Dhanuskodi 325 Reputation points

2024-04-30T06:37:38.7766667+00:00

how we provide access to uses for accessing this data in azure synapse?

I went through the documentation of azure synapse link. It is just replicating the data from azure sql to azure synapse dedicated sql pool. We need to setup azure syanpse analytics for this and add azure syanpse link which links the azure sql db to synapse dedicated sql pool.

How this will solve the issue of just accessing the data, User should be able to access it outside azure cloud portal. Is it possible to use SSMS to access this data and how we give access to end users for the same? Is there any other tool?

All I am looking for is to find a way to provide access to end users to database. In general users will be connecting to DB by SSMS. And that requires access to be given in portal - azure sql access settings, public ip or add user's ip to firewall settings or setup azure vpn.

I want to get rid of all these. A simple tool/solution for developers to access data in SSMS or something....

PRADEEPCHEEKATLA 90,261 Reputation points

2024-05-02T03:55:06.5266667+00:00

@Santhi Dhanuskodi - Can you please help us understand do you want to access Azure SQL DBs using Azure Synapse studio? or Access synapse resource like (Serverlesss, dedicated SQL pool, Spark pool, data explorer) from SSMS?

Looking forward for your response.

Santhi Dhanuskodi 325 Reputation points

2024-05-03T08:37:21.83+00:00

I want to access azure SQL DB data using syanose or any otehr possible solutions. could you please answer

Santhi Dhanuskodi 325 Reputation points

2024-05-06T08:21:39.6366667+00:00

@PRADEEPCHEEKATLA , Could you please answer?

PRADEEPCHEEKATLA 90,261 Reputation points

2024-05-06T11:04:28.7933333+00:00

@Santhi Dhanuskodi - For accessing Azure SQL DB data using synapse, I had already shared the solution above? Did you tried the above options or are you experiencing any problem in following the steps?

Santhi Dhanuskodi 325 Reputation points

2024-05-10T09:49:34.46+00:00

I havent tried the solutions yet.

I want to choose azure synapse link as you said before, And I am studying more on this.

From microsoft documentation, it is more of a data replication from azure sql db to azure synapse dedicated SQL pool. If choosing Azure synapse link, what will be the cost involved for the same? Are we then going to have 2 copies of data, one in original source azure sql db and another is on azure synapse dedicated sql pool?

Santhi Dhanuskodi 325 Reputation points

2024-05-13T05:59:25.4966667+00:00

@PRADEEPCHEEKATLA , Could you pls answer?

PRADEEPCHEEKATLA 90,261 Reputation points

2024-05-14T07:05:33.26+00:00

@Santhi Dhanuskodi - You are correct that Azure Synapse Link is primarily used for real-time data ingestion from Azure SQL Database to Azure Synapse Analytics. It allows you to perform near real-time analytics on operational data in Azure SQL Database without the need to copy or move data.

Regarding the cost, Azure Synapse Link is billed based on the amount of data ingested into Azure Synapse Analytics. The pricing is based on the number of data ingestion units (DIUs) used, which is determined by the amount of data ingested per hour. You can find more information about the pricing for Azure Synapse Link on the Azure Synapse Analytics pricing page.

Regarding your question about having two copies of data, with Azure Synapse Link, you do not need to create a copy of the data in Azure Synapse Analytics. Instead, Azure Synapse Link provides a direct and secure connection between Azure SQL Database and Azure Synapse Analytics, allowing you to perform real-time analytics on the data in Azure SQL Database without the need to copy or move the data.

I hope this helps clarify your question. Let me know if you have any further questions or concerns.
Sign in to comment

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

Share via

Access Azure SQL DBs using Azure Synapse studio

1 answer

Your answer