Best practices to use databrics to work with sql server data on premises

[serdia]

I want to use Databricks to analyze data that is stored on an on-premises SQL Server. What is the best way to bring this data into Databricks?

I tried to configure Lakehouse Federation so I could query on-premises data directly from Databricks. However, due to the different networks, I cannot connect. I assume I’d have to configure an Azure Virtual Network or something similar.

The same issue arises when using the JDBC connector. I cannot simply connect and create a DataFrame from my data because it is on a completely different network.

Let's say I have a simple query that joins 10 tables and outputs just 200 rows. Do I need to bring all those 10 tables to Databricks? Or should I move them to Data Lake Storage first and then to Databricks?

Thanks

[Kaniz_Fatma]

Hi @serdia, Since you mentioned network issues, setting up an Azure Virtual Network (VNet) peering between your on-premises network and Azure could be a solution. This involves:

• Creating a VNet in Azure and peering it with your on-premises network using VPN or ExpressRoute.
• Configuring routing to ensure that Databricks can access your on-premises SQL Server.
• If you prefer to use the JDBC connector, ensure that:

• VNet peering is configured as mentioned above.
• Firewall rules on your SQL Server allow connections from the Databricks IP range.

To handle your query that joins 10 tables and outputs 200 rows, you can either move all tables directly to Databricks using ETL tools like ADF for efficient complex queries, or first transfer the tables to Data Lake Storage (ADLS) and then load them into Databricks, which is useful if you plan to leverage ADLS for other purposes.

Given your network constraints, I recommend using Azure Data Factory to move the necessary tables to Azure Data Lake Storage and then load them into Databricks.