Can I Create a Shortcut in OneLake to an On-Prem SQL Database via Gateway?

[sk2784]

Hi everyone,

I have a large on-premises SQL Server database. I’ve set up a data gateway and can successfully access the data using PySpark in a Fabric notebook.

I was hoping to create a shortcut in OneLake to this on-prem SQL database, but I’m not seeing that as an option in the UI.

what’s the best workaround to make the data from the on-prem SQL database accessible within OneLake, ideally in a way that can be referenced easily by other Fabric components like Lakehouses or Pipelines?

Thanks in advance for any guidance!

[suparnababu8 · Accepted solution]

Hi @sk2784 

At present direct shortcut to an on-prem SQL Databases for OneLake not supported. But, you can use Fabric SQL database or you may use Data pipelines concept by conencting on-premises with data gateway. But, I know this is not you are expecting. 

If you want to explore about Fabric SQL databases please go through this https://learn.microsoft.com/en-us/sql/sql-server/fabric-database/sql-database-in-fabric?view=azuresq... 

Still if you need on-prem SQL serevr shortcut, you may raise your idea here https://community.fabric.microsoft.com/t5/Fabric-Ideas/idb-p/fbc_ideas  If your ideat got enough votes,

Microsft may bring up this feature in upcoming releases.

Thank you!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

[burakkaragoz]

Hi @sk2784 ,

Currently, OneLake does not support creating shortcuts directly to on-premises SQL Server databases, even if they are accessible via a data gateway.

However, here are a couple of workarounds you can consider:

1. Use Dataflows Gen2 or Pipelines: You can use a Dataflow Gen2 or a Pipeline in Fabric to connect to your on-prem SQL Server via the gateway and ingest the data into a Lakehouse or a Delta table in OneLake. This way, the data becomes accessible to other Fabric components.

2. Scheduled Notebook Jobs: Since you can access the data using PySpark in a notebook, you could schedule the notebook to regularly pull data from the on-prem SQL Server and write it to a Lakehouse or a designated OneLake folder.

3. External Tables (if applicable): If you're using a Lakehouse, you might consider creating external tables that point to the ingested data, making it easier to query and integrate with other components.

While not as seamless as a shortcut, these approaches can help you achieve similar functionality.

Hope this helps!

[sk2784]

Can you please elaborate more on item 3 External Tables (if applicable) ... consider creating external tables that point to the ingested data

[NandanHegde · Accepted solution]

Unfortunately as of today, shohrtcut is not supported for On prem SQL server.

You can follow the below methods for replications :

1) https://blog.fabric.microsoft.com/en-US/blog/mirroring-sql-server-database-to-fabric/

2) In case if it is batch data extraction, then you can use data pipelines and/or dataflow Gen 2.

Also, curious can you show how did you access the On Prem data via gateway through spark notebook? 

[sk2784]

Thank you for your quick answer.  
This is how I access the on premis sql database.  

from pyspark.sql import SparkSession

# Connection parameters

server_name = “server “  # SQL Server name

database_name = "db"   # Database name

table_name = "tbl"         # Table name to read

username = "sa"        # SQL Server username

password = "pw”        # SQL Server password

# JDBC URL for on-premises SQL Server through gateway

jdbc_url = f"jdbc:sqlserver://{server_name}:1433;database={database_name};encrypt=true;trustServerCertificate=true;"

# Read data from SQL Server using the gateway

df = spark.read \

    .format("jdbc") \

    .option("url", jdbc_url) \

    .option("dbtable", table_name) \

    .option("user", username) \

    .option("password", password) \

    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \

    .option("gateway", "true") \

    .load()

# Display the data

display(df)

[NandanHegde]

Where are you providing the gateway details? And based on my knowledge gateway integration with notebooks is not available. So I am curious whether has there been any update?

[sk2784]

Hi Nandan,

In Microsoft Fabric notebooks, when you set option("gateway", "true"), the system uses the default gateway that's associated with your workspace. The gateway name itself isn't specified in the code because Fabric automatically selects the appropriate gateway based on your workspace configuration.

This works because:

1. When you set up a gateway in Microsoft Fabric, it gets registered to your workspace
2. When your code runs with option("gateway", "true"), Fabric knows to route the connection through the gateway associated with your workspace
3. If multiple gateways are available, it uses the default one

[suparnababu8 · Accepted solution]

Hi @sk2784 

At present direct shortcut to an on-prem SQL Databases for OneLake not supported. But, you can use Fabric SQL database or you may use Data pipelines concept by conencting on-premises with data gateway. But, I know this is not you are expecting. 

If you want to explore about Fabric SQL databases please go through this https://learn.microsoft.com/en-us/sql/sql-server/fabric-database/sql-database-in-fabric?view=azuresq... 

Still if you need on-prem SQL serevr shortcut, you may raise your idea here https://community.fabric.microsoft.com/t5/Fabric-Ideas/idb-p/fbc_ideas  If your ideat got enough votes,

Microsft may bring up this feature in upcoming releases.

Thank you!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

[sk2784]

Thank you!