Proper way to connect to Azure SQL wrt Firewall

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.