Forum Discussion
Architecture design question
Dears,
Hope this message finds you well
I am designing a new architure for fabric
So far I have the following sources and method for bringing the infromation into fabric:
1) Dataverse - I will create on dataverse a direct fabric link , meaning no data will be added into fabric fro dataverse, instead, only shortcuts will be added and a lakehouse will be automatically created in my bronze workspace. That lakehouse will contact the link to the tables
2) A SQL Server Managed Instanced Mirroring , created from my Fabric side. With this mirroring, SQL server will replicate continuously its data into onelake using parquet files and I will have an auto-generated Data Wharehouse in the Workspace which will have the tables
Both sources above allow near-realtime. None of them, as far as I am aware allow me to chooose the schemas to where the tables will land inside the Items (LH or DWH)
Now, I have a third Source which is a SQL Server on-premisses. And the question is:
1) What shall I use to bring this data into fabric ? Shall I do a mirroring ? Shall I Instead use Fabric pipelines?
Please note, I have no special needs for near-realtime for this source , but maybe to keep coerency (with the other sources) I can do it? so that I can state that my architecture is prepared for near-realtime ingestion?
What option would you choose?
Thanks a lot,
Pedro Ribeiro
1) Append or merge?
Copy activity supports append and overwrite
No native merge
Merge must be done later using Spark or SQL
2) Deletes?
Copy activity does not handle deletes
Deleted rows in source will remain in Lakehouse
Use CDC or soft delete if deletes matter
3) CDC vs watermark
Watermark: simple, needs timestamp, no deletes
CDC: captures insert/update/delete, more complex
Choose CDC if correctness matters, watermark if reporting-only
4) What gets created in Lakehouse?
Delta tables
Stored as Parquet in OneLake
Tables are auto-created, no manual linking
5) SHIR vs Power BI gateway
Same runtime
Different name
In Fabric it is called SHIR
Use Copy activity from Fabric Data Factory
Do not use gen2 dataflow for SQL Server ingestion
Scheduler
Use pipeline triggers
Time-based schedule (for example every 5–15 minutes)
No separate scheduler needed
"Another questions is, shall I use a service principal to secure this? which permissions shall it has in SQL Server on-prem and on Fabric this service principal? last, but not less important, where do I store the : SHIR ? does it needt o be installed on the SQL Server on-prem machine? And the coonnection shall be encypted? "
Yes, use it
Permissions : SQL Server onprem
Read-only
SELECT on required tables
SELECT on CDC tables if CDC is used
No write, no db_owner
Permissions -> Fabric
Workspace role: Contributor
Lakehouse: Write
Permission to run pipelines
SHIR:
Install on a separate VM
Do not install on the SQL Server machine
Must have network access to SQL Server
Can scale with multiple nodes if needed
Encryption
Yes
SQL connection uses TLS (Encrypt=True)
SHIR -> Fabric uses HTTPS (outbound 443 only)
8 Replies
Why not mirroring for onprem SQLServer
SQL Server mirroring to Fabric is not supported for on-prem in a clean, first-class way today. It is designed mainly for Azure SQL, SQL MI.
Even if you force it through gateways, you lose the “low-ops, managed” value that makes mirroring attractive.
Mirroring gives you no control over schema, naming, or landing structure, which you already identified as a limitation.
Why Fabric Data Pipelines is the right choice
Works natively with onprem SQL Server via SHIR.
You control landing zone: Lakehouse bronze, folder structure, table naming, schemas.
You can run incremental loads (watermark, CDC like logic) and schedule it frequently if you want to claim “near real time ready”.
Operationally consistent with enterprise patterns. This matters more than theoretical real-time.
On “coherency” and architecture story, do not force mirroring just for consistency. That is bad architecture.
Instead, you have to position it like this...
Dataverse link and SQL MI mirroring -> event/replication driven ingestion
On-prem SQL via pipelines -> micro batch ingestion (near real time capable)
That is a credible, production-grade narrative.
Recommended pattern
Pipelines -> Bronze Lakehouse
Standardize schemas in Silver (Spark/SQL)
Keep mirroring outputs isolated; normalize downstream
Bottom line is...
Dataverse: Direct Fabric link - Yes
SQL MI: Mirroring - Yes
On-prem SQL Server: Fabric Data Pipelines - Yes
Do not over-optimize for near real time where it adds no business value
This is the architecture a Fabric reviewer or Microsoft CSA would sign off on.
Thank you very much Vinodh247 . And what shall I use inside this Fabric Data Pipelines ? the copy tasks from fabric data factory ? or the genflow 2? and what is the scheduler for it? Another questions is, shall I use a service principal to secure this? which permissions shall it has in SQL Server on-prem and on Fabric this service principal? last, but not less important, where do I store the : SHIR ? does it needt o be installed on the SQL Server on-prem machine? And the coonnection shall be encypted? thanks a lot
I have tried to list out the complete flow in detail, pls refer below this should ans all your questions.
1. Inside Fabric Data Pipelines
Use Copy activity (Fabric Data Factory)
Do not use gen2 dataflow
Reason: performance, incremental loads, enterprise control
2. Scheduler
Use pipeline triggers
Time-based schedule (for example every 5 to 15 mins)
Use watermark or CDC style logic
3. Service principal
Yes, use it
SQL Server on-prem:
Read-only access
SELECT on required tables (and CDC tables if used)
Fabric:
Workspace role: Contributor
Lakehouse: Write
Allowed to run pipelines
4. SHIR
Install on a dedicated VM, not on the SQL Server
Must have network access to SQL Server
Can scale with multiple nodes
5. Security
Connection is encrypted
SQL: Encrypt=True
SHIR -> Fabric uses HTTPS outbound only (port 443)
Overall:
Copy activity + scheduled triggers
Service principal + least privilege
SHIR on separate VM
Bronze Lakehouse landing
This is the correct Fabric pattern. HTH!
Thanks a lot for the replies Vinodh247
1) For Lakehouse, does the copy activities allow append, merge ? or only merge?
2) What about deletes ? if I delete something on the source will it be able to delete as well on the lakehouse in fabric? or it will nnot know and hence the deleted rfecord will stay forever in the lakehouse?
3) Whay would you choose (Use watermark or CDC style logic) ? I am not sure that all columns have timestamp, maybe they have. What are the advantages of one versus the other and when would you use CDC vs Watermark?
4) The Copy activity, creates the tables in the raw zone as delta tables with parquet files in the OneLake? or does it create only the parquet files and I will have to create link the tables in the lakehouse?
5) Is SHIR the same as now called PowerBI data gateway?
1) Append or merge?
Copy activity supports append and overwrite
No native merge
Merge must be done later using Spark or SQL
2) Deletes?
Copy activity does not handle deletes
Deleted rows in source will remain in Lakehouse
Use CDC or soft delete if deletes matter
3) CDC vs watermark
Watermark: simple, needs timestamp, no deletes
CDC: captures insert/update/delete, more complex
Choose CDC if correctness matters, watermark if reporting-only
4) What gets created in Lakehouse?
Delta tables
Stored as Parquet in OneLake
Tables are auto-created, no manual linking
5) SHIR vs Power BI gateway
Same runtime
Different name
In Fabric it is called SHIR
Hi fabricpribeiro,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Vinodh247 for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
Use Copy activity from Fabric Data Factory
Do not use gen2 dataflow for SQL Server ingestion
Scheduler
Use pipeline triggers
Time-based schedule (for example every 5–15 minutes)
No separate scheduler needed
"Another questions is, shall I use a service principal to secure this? which permissions shall it has in SQL Server on-prem and on Fabric this service principal? last, but not less important, where do I store the : SHIR ? does it needt o be installed on the SQL Server on-prem machine? And the coonnection shall be encypted? "
Yes, use it
Permissions : SQL Server onprem
Read-only
SELECT on required tables
SELECT on CDC tables if CDC is used
No write, no db_owner
Permissions -> Fabric
Workspace role: Contributor
Lakehouse: Write
Permission to run pipelines
SHIR:
Install on a separate VM
Do not install on the SQL Server machine
Must have network access to SQL Server
Can scale with multiple nodes if needed
Encryption
Yes
SQL connection uses TLS (Encrypt=True)
SHIR -> Fabric uses HTTPS (outbound 443 only)
Hello fabricpribeiro,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
