Optimizing data access B/W SQL Server and Azure Synapse for Real-Time and Analytical Data

Question

Optimizing data access B/W SQL Server and Azure Synapse for Real-Time and Analytical Data

Vikranth-2626 160

We currently maintain a SQL server infrastructure hosting both real-time and analytical data. This setup integrates real-time data processing with historical analytical data for reference purposes. In our initial Proof of Concept (POC) design, we intend to segregate real-time data and processes on the SQL server, while migrating analytical data to Azure Synapse. However, we're seeking the most efficient approach to access Azure Synapse data as reference data from our SQL server instance while prioritizing performance. Any insights or recommendations on optimizing this data access would be greatly appreciated.

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-02-21T04:32:57.2133333+00:00

@Vikranth Following up to see if the below answer was helpful. 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.

Answer accepted by question author

0 additional answers

Your answer

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-02-21T04:32:57.2133333+00:00

@Vikranth Following up to see if the below answer was helpful. 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.

Answer 1

Hi @Vikranth ,

Thank you for using Microsoft Q&A platform and thanks for your question.

Regarding your query to segregate real-time data and processes on the SQL server, while migrating analytical data to Azure Synapse, we suggest using PolyBase to access data in Azure Synapse from SQL Server.

PolyBase is indeed a recommended and easy option in SQL Server to export data from Azure Synapse. However, it is important to note that there are known limitations in PolyBase as documented by Microsoft.

In addition to PolyBase, there are other options to connect to Azure Synapse from SQL Server, each with its own pros and cons depending on your specific requirements. You can find a comprehensive guide on connecting to Azure Synapse from SQL Server in this document.

For more complex transformations on data that need to be run on a scheduled basis, we recommend using Azure Data Factory. Azure Data Factory is a cloud-based data integration service that supports connecting to both on-premise SQL Server and Azure Synapse, with various options to schedule the pipelines. This can help automate the data transfer process and ensure that the data is always up-to-date.

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.

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-02-20T10:32:53.48+00:00

@Vikranth Following up to see if the above answer was helpful. 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.

Share via

Optimizing data access B/W SQL Server and Azure Synapse for Real-Time and Analytical Data

0 additional answers

Your answer