Running Scheduled SQL job using the data from fabric lakehouse

[Raavanan_7]

Hi,

I have large history tables in SQL Server(2019) and have scheduled several jobs that rely on these tables. I plan to move these history tables to Fabric OneLake Storage for archival purposes and to reduce storage consumption in SQL Server. After completely migrating the data from SQL Server to Fabric OneLake, how can I connect to the data in Fabric OneLake and execute the existing jobs in SQL Server?

NOTE: the SQL server is already configured and running, Enabling polybase is not possible.

[v-aatheeque]

Hi @Raavanan_7 ,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Raavanan_7 ,
Thanks for reaching out to the Microsoft Fabric Community forum.

To address your issue you can follow the below steps:

In SQL Server, use the CREATE EXTERNAL DATA SOURCE statement to point to the Fabric OneLake location where the data is stored.

Ensure the correct URL, authentication mechanism, and access credentials are configured for secure and reliable connectivity.

Since Fabric OneLake supports formats like Parquet and Delta Lake, make sure the format matches how your historical data was exported. For example:

• If Parquet: Confirm that all the partitions and metadata were preserved during migration.
• If Delta Lake: Ensure compatibility with SQL Server external table querying.

Use the CREATE EXTERNAL TABLE statement in SQL Server to map the table schema to the external data source.

Carefully define the column names and data types to ensure consistency with the archived data in Fabric OneLake.

Update the existing SQL Server jobs to reference the external table instead of the original local history tables.

If the jobs include logic that depends on data format or storage location, validate that these adjustments work seamlessly.

Before deploying the updated jobs, conduct thorough testing to ensure:

Query performance is acceptable for your use case (since external tables may introduce latency).

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[Raavanan_7]

Hi!

Thanks for the reply,

In SQL Server 2019, the PolyBase feature needs to be enabled during the Feature Selection phase to create and query data from external sources. However, since the server is a clustered instance, my database team has said to me that it is not possible to enable the PolyBase feature in this setup.

is there is any other possibilities or options availble?

[v-aatheeque]

Hi @Raavanan_7 ,

To address the issue of accessing historical data archived in Fabric OneLake without enabling PolyBase in a clustered SQL Server 2019 instance, follow the steps below:

• Set up a linked server to connect to an external source (e.g., another SQL Server instance or Fabric OneLake via a proxy).
• Use four-part naming for querying (server_name.database_name.schema_name.table_name).
  This method works well for light querying but may not scale well for large datasets or complex queries.
• Use SSIS to periodically extract data from Fabric OneLake and load it into a staging table in SQL Server.This approach avoids direct querying of the external source during job execution, potentially improving performance.
• Suitable for batch jobs and scheduled processing, especially if your existing jobs depend heavily on SQL Server's local processing capabilities.
• If another SQL Server instance (with PolyBase enabled) is available in your environment, consider creating a linked server to that instance.The linked server can act as an intermediary for external data access.

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[Raavanan_7]

Thanks for your reply

But in SQL server (2019) we need to enable polybase in feature selection to create and query the external tables. Here the server is a clustered one.so, database team said it is not possible to enable polybase in it.