Forum Discussion

nullabee's avatar
nullabee
New Member
1 day ago

Proper way to connect to Azure SQL wrt Firewall

Hi all

We have an Azure SQL Server that doesn't permit public access (as is the best security practice). When accessing it from a Fabric workspace, it looks like we have, essentially, 2 options (if we want to maintain that best practice):

  • we setup private endpoints and configure these. This has the (huge) disadvantage that startup time for spark environments goes from 5 seconds to 3 minutes (it took me a LOT of digging through documentation to figure the link between these settings out). That means EVERY time I use a spark notebook, it stands idle for 3 minutes, which is an insane overhead.
  • or we use a data gateway. However, when we connect to this same SQL Server from a notebook (by jdbc), it appears that this connection does not pick up on the data gateway (I don't see how it could, anyway). So this means my notebooks cannot communicate to this SQL Server?

Maybe I'm missing something, but does anybody have a clue? Also: this feels like a very basic scenario (getting data from an Azure SQL Server to Fabric). I know there are options to use mirroring, or I could get the data by a dataflow or some other non-notebook solution, but in our case: I have to call some stored procedures in the middle of the notebook execution. It would be a huge effort to refactor all of this, so I'm looking for a simple way to connect to an Azure SQL Server that has public access disabled, both from pipelines and notebooks, without a universal "overhead" (note: even if I never actually USE the private endpoint in my notebook, the overhead of 3 minutes is still present).

Note: when accessing an ADLS Gen 2 (also with public access disabled), there is the option to allow access to it with the workspace identity. A similar option seems not to be present for Azure SQL Server (there is only on option to "Allow Azure Services to access this", but that seems not fine grained enough)?

 

Context: we are migrating from an ADF setup where we had a rather large number of databricks notebooks (called from a load of different pipelines) for relatively small tasks (runtime in the order of seconds). You can see how the overhead of 3 minutes destroys this setup.

data engineering
data pipeline
Notebook

1 Reply

  • jubinsoni's avatar
    jubinsoni
    Icon for Skilled Sharer rankSkilled Sharer
    18 hours ago

    Hi nullabee​,

    Your reading is correct on both points: the 3-minute start is the managed virtual network effect (once a workspace has managed private endpoints, Spark can no longer use the pre-warmed starter pools), and notebooks never go through a data gateway. The way out is usually to not make every notebook pay for private connectivity, only the step that actually talks to SQL:

    Workspace "ingest" (managed VNet + private endpoint to Azure SQL)
  -> a few pipelines / notebooks that read SQL and land data in a Lakehouse
     (slower Spark start, but only here)

Workspace "transform" (no managed VNet, starter pools ~seconds)
  -> your many short notebooks read the landed Lakehouse tables
     via shortcuts to the ingest lakehouse

    Alternatively skip Spark for the SQL hop entirely: a pipeline Copy activity (or Copy job) through a VNet data gateway or on-premises gateway can read the private Azure SQL and write Delta tables, and the short notebooks then only touch OneLake. If the source tables are suitable, mirroring the Azure SQL database into OneLake is worth evaluating too, since it removes the scheduled extract altogether (check its networking options for your setup).

    Two smaller tips for the remaining Spark start-up cost: enable high concurrency mode for pipelines so consecutive notebook activities can share one session, and consider merging very short notebooks into one parameterised notebook called with notebookutils.notebook.runMultiple, so you pay start-up once per pipeline run instead of per notebook.

    You are also right that the "trusted workspace access" option exists for ADLS Gen2 storage accounts but not for Azure SQL, which is why the private endpoint or gateway is needed there.

    So my overall approach would be:

    Isolate SQL access in a small "ingest" workspace with the private endpoint
-> Or use Copy activity / Copy job via VNet gateway (no Spark needed)
-> Short notebooks run in a non-VNet workspace on shortcuts (fast start)
-> High concurrency + runMultiple to amortise remaining start-up

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.