Fabric_Test1 · PowerShell demo
A customer from Azure SQL (1, Jansen, A) and a customer from the local Win10 SQL Server (1, Pietersen, B) are loaded into a Fabric warehouse, enriched with T-SQL, and published as dwh.Customers. Every step is a script; one cleanup removes it all. AZURE · OPENDATA.NL TENANT YOUR WIN10 PC FABRIC · ONMICROSOFT.COM TENANT · TRIAL CAPACITY Azure SQL db1 dbo.Customer 1 · Jansen · A SQL Server 2025 db1 dbo.Customer 1 · Pietersen · B On-prem data gateway one-time setup in the portal Pipeline pl_load_customers Copy activity: local only WAREHOUSE db1 stg.Customer_Azure truncated, then loaded stg.Customer_Local empty until gateway is set Enrich (T-SQL) union both sources, key, full name, load date dwh.Customers for reporting 04-Load.ps1 reads, then inserts (Entra token) read insert … select Dashed boxes and arrows mark the optional local path. It needs a gateway connection created once in the Fabric portal; until then the run loads only the Azure customer and dwh.Customers holds one row instead of two.
What each part does
Azure SQL (opendata.nl)
Serverless General Purpose, 0.5 vCore minimum, pauses after 60 minutes idle. Scripts reach it with your Entra token; the read-only login fabric_reader is kept for a future Fabric connection.
Local SQL Server
Already installed on this PC. Fabric cannot reach it from the cloud, so the gateway relays the copy over an outbound connection. No inbound firewall change.
Loading the staging tables
The Azure row is read by the script and inserted into the warehouse. A warehouse COPY cannot read Azure SQL directly without a staging store, which is too much for one row. The local table goes through a pipeline and the gateway. Both loads truncate first, so a rerun never duplicates rows.
Warehouse db1
Schemas stg (raw copies) and dwh (reporting). Both sources use CustomerId 1, so enrichment adds a surrogate CustomerKey and a SourceSystem column.
Run order
| Script | What it does | Cost |
|---|---|---|
| 00-Prereq | Checks logins, tools, capacity and the local SQL Server. Read-only. | none |
| 01-Azure | Resource group, SQL server, db1, dbo.Customer row, read-only login. | starts billing |
| 02-Local | Local db1.dbo.Customer with Pietersen. | none |
| 03-Fabric | Workspace on the trial capacity, warehouse (and an Azure SQL connection, currently unused). | trial |
| 04-Load | Staging tables, load the Azure row, and run the local pipeline when a gateway connection is set. | trial |
| 05-Transform | Runs 05-Transform.sql: union, enrich, full reload of dwh.Customers. | trial |
| 06-Verify | Reads dwh.Customers and checks the row count. | trial |
| 99-Cleanup | Deletes workspace, connection and resource group. -DropLocal also drops the local db1. | stops billing |
Decisions you inherit
No mirroring
Mirroring needs the SQL server’s managed identity to be a member of the Fabric workspace. Azure sits in the opendata.nl tenant and Fabric in onmicrosoft.com, so Fabric cannot find that identity. Reading with a token from the script works across tenants.
Two logins
Azure calls use the opendata.nl account. Fabric calls use the tenant-level account for onmicrosoft.com, selected by tenant id so az does not pick the wrong cached token.
Germany West Central
West and North Europe refused new SQL servers on this subscription. The region does not matter to Fabric.
Secrets
Generated passwords live only in .secrets/state.json. That folder and config.ps1 are in .gitignore.