Fabric SQL Server Managed Instance Mirroring

[fabricpribeiro]

Dears,

Hope this message finds you well

I am designing an architecture which includes SQL Server Managed Instance mirroring to Fabric

I would be delighted if you can help me with the following questions:

1) From what I read, seems that its only possible to work with the public endpoint of SQL Server MI, its not possible to use private endpoints. If that is still true, ticking the checkbox which states encrypt connection will encrypt the data movement between SQL MI and Fabric using which encryption method?

2) I intend to use a Service Principal. What are the permissins that thyis use will need on Fabric and on SQL Server MI? Probably on Dabric it needs to be WS Conbributor?

3) Seems I am only able to choose the name of the of DWH which will be created on Fabric , but not the schemas where the information should land. Instead, it will copy all the schemas as they are from SQL MI into the DWH it will generate, right?

4) Can I choose the Worskpace where the DWH will be created?

5) Its a red-only copy. I cannot do any changes on the tables in this replicated DWH, right?

6) The files of this replication are generated, probably in parquet files, to which table in fabric? how can I see those files?

Thanks a lot,

Pedro 

[v-pnaroju-msft]

Hi fabricpribeiro,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi fabricpribeiro,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi fabricpribeiro,

Thankyou for the followup.

Based on my understanding, SQL Server MI mirroring to Microsoft Fabric is a managed, read-only analytical replication service that maintains only the current state and does not expose CDC or ingestion metadata at the table or row level.

Regarding Append versus Merge:

1. Fabric mirroring applies changes with semantics similar to MERGE. The Fabric warehouse always reflects the latest state of the source tables, no history is retained. A record inserted at source will appear in Fabric, and a record later deleted at source will be removed from Fabric. There is no SCD Type 2 behaviour and no historical versions.

Regarding metadata, ingestion time, and sync status:

1. Fabric mirroring does not add ingestion timestamps or CDC metadata to tables. It does not expose rows inserted, updated, or deleted, nor does it provide per table sync success or failure information.

Please consider the following approach to manage metadata:

1. It is not possible to request that the mirroring process populate a metadata table. Instead, create a downstream monitoring solution (for example, a separate Warehouse or Lakehouse) and periodically capture table existence, row counts, and observation timestamps. Store these in a custom metadata table for operational tracking. For row level history or auditing, enable CDC at source or use Fabric Data Pipelines / Dataflows Gen2 downstream. Mirroring alone is not designed for historical tracking.

We hope this information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi fabricpribeiro,

Thank you for your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the behavior you are observing is by design. SQL Server Managed Instance (MI) mirroring to Fabric is implemented as a managed, read only analytical replication service, with security, storage and schema handling controlled by the Fabric platform.

Please follow the steps below, which may help resolve the issue:

1. Only the public endpoint of SQL Server MI is supported at present. Private endpoints are not supported for Fabric mirroring. When the Encrypt connection option is enabled, data is encrypted in transit using TLS 1.2. Data is also encrypted at rest in OneLake.
2. Service principal permissions on Fabric: the required workspace role is Contributor (minimum).
   Service principal permissions on SQL Server MI: an Azure AD login for the service principal and database permissions such as db_datareader and VIEW DEFINITION are required.
3. You can select only the Warehouse name. All schemas and tables are replicated as-is from SQL MI. Schema selection or transformation during replication is not supported.
4. You may choose the Fabric workspace in which the Warehouse is created. The service principal must have the Contributor role in that workspace.
5. The replicated Warehouse is read only. INSERT, UPDATE, DELETE and DDL operations are not permitted. The data is intended for analytics, reporting and Power BI consumption only.
6. Data is stored in OneLake in Delta or Parquet format. Files are system managed and not directly accessible. Supported access is via the Fabric Warehouse (SQL endpoint) only.

Additionally, please refer to the links below for further information:
Mirroring - Microsoft Fabric | Microsoft Learn
OneLake, the OneDrive for data - Microsoft Fabric | Microsoft Learn

We hope the information provided helps to resolve your issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[fabricpribeiro]

@v-pnaroju-msft ,

Thank you very much for the reply. Do you know if it will do Append method or Merge method? Meaning, will I get history? or only latest records?

For example,

1) Imagine a record was added

2) Some days after , its deleted

How ill it work in my DWH which is created by fabric?

Will it show the two records (similar to a SCD type 2) or will it show first the added record and then when its deleted, the record will desappear from my DWH in raw?

Another question is related with metadata management. Will I get infromation like ingestion date on fabric , sucess or failre of sync?

How would you manage metadata in for this particular synk process to understand ingestion date into fabric, if it was scuessfull or nor for each table, etc..?  Can I create a metadata table and ask the process to feed it? 

Like :

Source | Tablename | Ingestion Time | Rows Inserted | Rows Updated | Rows Deleted | Etc...   ?  

Thanks,

Pedro