Forum Discussion
Post PatronArchitecture design question
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)
Super UserWhy 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.
Post Patron
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