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

[Raavanan_7]

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!

[govindarajan_d]

@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.

[Anonymous · Accepted solution]

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

[Raavanan_7]

Thanks for the reply!