topic Re: Data replication in SQL server from fabric LH or WH using Polybase in Data Engineering

Data replication in SQL server from fabric LH or WH using Polybase

Raavanan_7 — Wed, 04 Dec 2024 18:14:46 GMT

I recently encountered a requirement to migrate existing data from SQL Server to Fabric Lakehouse or Warehouse, making Fabric the permanent source for operations previously built and running. As part of the process, I also need to delete the records from SQL Server after the migration to reduce the storage cost.

Additionally, I need to query the data stored in Fabric Lakehouse or Warehouse from SQL Server using PolyBase to run previously build application using sql sp as a source.

The data which is going to be migrated are history tables which is not mostly used.

Is it possible to connect Fabric Lakehouse to SQL Server using PolyBase? If so, could you please provide some guidance?

Thanks!

Re: Data replication in SQL server from fabric LH or WH using Polybase

Anonymous — Thu, 05 Dec 2024 02:19:48 GMT

HI @Raavanan_7,

You can use fabric to get data from on premise SQL server through on-premises data gateway that hosted in the same network to access to the SQL server.

Getting On-Premises SQL Server Data to Microsoft F... - Microsoft Fabric Community
For polybase feature, I haven't found any documents mention it supported to work with fabric.

Regards,

Xiaoxin Sheng

Re: Data replication in SQL server from fabric LH or WH using Polybase

Raavanan_7 — Thu, 05 Dec 2024 04:43:20 GMT

Thanks for the reply!

Re: Data replication in SQL server from fabric LH or WH using Polybase

govindarajan_d — Thu, 05 Dec 2024 05:16:21 GMT

@Raavanan_7,

You could use pipelines to bring the data into Fabric One Lake and as you say it is going to be one-time, that would be the best solution until mirroring for on-premise SQL DB comes in.

But if you are moving from SQL Server, know that not all SQL features are supported in LH/WH SQL. For example, you cannot have MERGE statement nor Primary Key constraints.

In terms of Polybase, it would be very difficult to provide a solution unless we know the architecture used. But instead of Polybase, you can try using OLE DB connection (linked servers) which will allow you to do cross-querying between LH and SQL Server data.