Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Post Patron
7 months ago
Solved

Fabric SQL Server Managed Instance Mirroring

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 foll...
Workspaces
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    7 months ago

    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's avatar
v-pnaroju-msft
Community Support
7 months ago

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's avatar
    fabricpribeiro
    Post Patron
    7 months ago

    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