Using Microsoft SQL Server source in v17.0.0 for connecting to Azure Synapse

1

Hi,

We are connecting to our Azure Synapse workspace using the Microsoft SQL Server source. We are able to list and query the views and external tables in Dremio.

As Azure SQL Data Warehouse is the SQL Server Data Warehouse (a feature from SQL Server) in the cloud, and Azure synapse is an evolution of Azure SQL Data Warehouse, I have a few questions:

  1. Is it okay if we are connecting to Azure Synapse using the Microsoft SQL Server source?
  2. Do you foresee any issues that might arise while connecting and querying data in Synapse through this source?
  3. We are able to list the tables and views only when the legacy dialect is enabled from the advanced options tab. Otherwise, we would get an error saying Error retrieving all tables for source - @@FETCH_STATUS is not supported. Will there be any problems in the future regarding ‘Enable Legacy Dialect’ ? since this is the only way we are currently able to connect Synapse through Microsoft SQL Server?
  4. Will there be any other limitations when connecting to Synapse using the Microsoft SQL Server source?

If there’s anything else we should be knowing beforehand, please do mention it.

TIA,
Farhan.

2

Hi @Farhan

Great questions, Although you are using SQL Server source to connect to Synapse and it is working, I see few issues

  • Dremio team had not tested connecting to Synapse
  • Legacy Dialect will be dropped soon
  • We may run into performance issues as performance and load tests have not been done

The reason legacy is working is because using the non legacy connector it seems like there is a function used that is not available in Synapse

When using the legacy connector, Dremio is probably not using that function

Maybe you can build a Synapse connector using Dremio Hub

1 Like