Azure Synapse as Refrence data

Question

Azure Synapse as Refrence data

Rohit Sapru 26

We have an existing in-house SQL server infrastructure, which currently holds both real-time as well as extract (Analytical) data. Both of these reside together because real-time data processing needs access to historical (analytical) data as a reference data.

As part of initial POC design we are planning to keep the real-time data/ processes on SQL server and move analytical data to Azure Synapse. But we are still trying to figure out the best approach to access data in Azure Synapse from SQL server instance as reference data while keeping performance at its best.
Any pointer is this regard will be apricated.

Accepted answer

0 additional answers

Your answer

Answer 1

HarithaMaddi-MSFT 10,146

Hi @Anonymous ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Polybase is the recommended and easy option in SQL Server that can be used to export data from Synapse. However, there are known limitations in Polybase as documented. Various other options to connect to synapse from SQL can be found in this document and each has its own pros and cons that can be chosen depending on the requirement. For more transformations on data that need to be run on scheduled basis can be done using Azure Data Factory that supports connecting to both on-premise SQL and Azure Synapse DWH with various options to schedule the pipelines.

Hope this helps! Please let us know for more queries and we will be glad to assist.

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

Please accept an answer if correct. 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.

Rohit Sapru 26 Reputation points

2020-10-28T10:42:22.627+00:00

Thanks, But we do not want to export data and create duplicate copies of the data, it will defeat the original purpose of segregating real-time and historical data. We were looking for options like Linked server, where in we can refer to the data in synapse without exporting it.
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-28T10:46:28.83+00:00

Thanks @Anonymous . Polybase works with external tables which are similar to linked servers that establish references to particular tables and they load the data only when we run the commands on the external table. As per my understanding, there is no alternate way but I will work with internal teams for alternate options other than Polybase and get back to you.

Thanks for your patience!
Rohit Sapru 26 Reputation points

2020-10-28T14:29:42.577+00:00

Thank you really appreciate your help. I will work on to check if polybase link works for us.
Could you share sample/ document to access a Synapse table from Azure SQL database server .
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-28T15:15:50.58+00:00

Sure, thanks @Anonymous . Please let us know if these documents - polybase-configure-sql-server and import-data-into-asdw-with-polybase helps. Also, please share volume of data to be stored in Synapse DWH along with schedule of data refresh needed for this data.
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-29T17:43:05.94+00:00

Hi @Anonymous . - We have not received a response from you. Please let us know if you got a chance to try the above suggestion and also share the requested details if the approach does not work.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Share via

Azure Synapse as Refrence data

0 additional answers

Your answer