Forum Discussion
Foreign Key Constraints Behavior After Mirroring SQL Server to Fabric
Hi Team,
I have mirrored an on-premises Microsoft SQL Server database into Microsoft Fabric using the mirroring feature.
In the source database, multiple tables have foreign key constraints defined. After mirroring, I understand that these constraints are not carried over to Fabric by default.
So, I manually created the foreign key constraints in the mirrored database using SQL Endpoint. However, I am noticing that these constraints are not being retained — they seem to get dropped or deleted after some time (possibly during sync or refresh).
- Is this expected behavior with Fabric mirroring?
- Is there any way to prevent the foreign key constraints from being dropped automatically?
- What is the recommended approach to maintain referential integrity in this scenario?
Any guidance would be greatly appreciated.
Thank you.
Hi ShanthanPaladi ,
Yes, this is expected behavior. The Fabric mirroring process only replicates data — it does not carry over DDL schema objects like foreign key constraints from the source SQL Server. More importantly, the SQL Analytics Endpoint in Fabric is a read-only, auto-generated view over the underlying Delta/Parquet files in OneLake. Any schema-level changes you make directly on the SQL Analytics Endpoint (including manually adding foreign keys) can be lost or overridden when the mirroring sync refreshes the metadata or when the endpoint regenerates its schema.
This is a fundamental architectural difference from on-premises SQL Server. Fabric is an analytics platform, not a transactional one, and its constraint system is intentionally non-enforcing.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hello ShanthanPaladi
This is expected behaviour. The mirrored database, available as a SQL analytics endpoint, is read-only. You are only allowed to analyze data in delta tables using a no code visual query editor or T-SQL to create views, functions, stored procedures, and apply SQL security - you can't apply DDL constructs!
In summary, in Fabric Mirroring:
- Foreign keys are not replicated
- The SQL analytics endpoint is read‑only
- Constraints you manually add are not retained
Therefore:
- ARI (Assume Referential INtegrity) is the correct analytical substitute for missing physical constraints
- Integrity should be enforced in the source SQL Server
- ARI tells Fabric: “Trust the source system”
This is the recommended pattern for mirrored databases on Fabric at this time.
4 Replies
Hi ShanthanPaladi ,
Yes, this is expected behavior. The Fabric mirroring process only replicates data — it does not carry over DDL schema objects like foreign key constraints from the source SQL Server. More importantly, the SQL Analytics Endpoint in Fabric is a read-only, auto-generated view over the underlying Delta/Parquet files in OneLake. Any schema-level changes you make directly on the SQL Analytics Endpoint (including manually adding foreign keys) can be lost or overridden when the mirroring sync refreshes the metadata or when the endpoint regenerates its schema.
This is a fundamental architectural difference from on-premises SQL Server. Fabric is an analytics platform, not a transactional one, and its constraint system is intentionally non-enforcing.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hello ShanthanPaladi
This is expected behaviour. The mirrored database, available as a SQL analytics endpoint, is read-only. You are only allowed to analyze data in delta tables using a no code visual query editor or T-SQL to create views, functions, stored procedures, and apply SQL security - you can't apply DDL constructs!
In summary, in Fabric Mirroring:
- Foreign keys are not replicated
- The SQL analytics endpoint is read‑only
- Constraints you manually add are not retained
Therefore:
- ARI (Assume Referential INtegrity) is the correct analytical substitute for missing physical constraints
- Integrity should be enforced in the source SQL Server
- ARI tells Fabric: “Trust the source system”
This is the recommended pattern for mirrored databases on Fabric at this time.
Hi ssrithar & deborshi_nag
Thank you both for your detailed inputs and clarification.
I appreciate your guidance and the detailed explanation — it helped me understand the expected behavior and the recommended approach moving forward.
Thanks again for your support!Hi ShanthanPaladi ,
We really appreciate your efforts and for letting us know the update on the issue.
Please continue using fabric community forum for your further assistance.
Regards
