Forum Discussion
Post PatronFabric Mirroring in CDC mode for SQLMI with some databases being restored every week or every day ..
Dears,
We are using Fabric mirroring in CDC mode to CDC a SQLMI which has an update policy as 2022
The issue is that many times the database is restored ... which makes CDC mirroring not possible...
Is there any other possibility that you would see possible , like links or something that we could use to bring information into Fabric?
Or the only way would be pipelines with copy activitis or genflow 2?
Thanks,
Pedro
Hello fabricpribeiro,
You're correct that frequent database restores are problematic for CDC-based mirroring, as restoring the database breaks the CDC chain and requires the mirroring process to be reinitialized.
If your databases are restored regularly, using Pipelines (Copy Activity) or Dataflow Gen2 is generally a more robust approach, especially if you can leverage incremental loading with a watermark column.
Regarding your question about SQL Server 2025 Change Feed, it improves change tracking compared to CDC, but a database restore still represents a reset of the source state. In other words, a restore would still require the mirroring relationship to be re-established rather than seamlessly continuing from where it left off.
So, if regular restores are part of your operational process, an ingestion pattern based on Copy Activity or Dataflow Gen2 is typically the more resilient and predictable option.
Best regards,
Omkar Shinde
Microsoft Fabric Enthusiast | Power BI Consultant
💡 If you found this response helpful, please consider giving it a Kudos.
✅ If this resolves your question, please mark it as the Accepted Solution to help others in the community.
5 Replies
ith SQL MI 2022 its tricky, every time if you restore database or added new tables or let say to need to full load the tables in SQL MI it is difficult because enables CDC.
We had similar issue and for that we created CICD pipeline, Let say we have to perform operation on SQLMI tables like DROP and recreate, as cdc is enabled on that table SQLMI will not allow us to delete the table
in that scenario we followed below sequence:
- Stop Mirroring
- Source SQL-MI --> Disable CDC (using devops pipline)
- Perform Operation on SQL-MI- Source SQL-MI --> Eanble CDC (using devops pipline)
- Start Mirroring
Caviate here is if tracking lost, mirroring mirror entore data (FULL LOAD), so in your ETL layer you need to make sure we handled using watermark table or may be using row level hash to compare the snapshots.
But switching SQLMI-2025 is the correct path forward for this get rid of CDC
best of luck.
fabricpribeiroBut what is the difference if I had 2025? would I still lose everything if a restore was made? even if I was in change feed instaed of CDC mirroring?
- Omkar_1712
Solution Sage
Hello fabricpribeiro,
You're correct that frequent database restores are problematic for CDC-based mirroring, as restoring the database breaks the CDC chain and requires the mirroring process to be reinitialized.
If your databases are restored regularly, using Pipelines (Copy Activity) or Dataflow Gen2 is generally a more robust approach, especially if you can leverage incremental loading with a watermark column.
Regarding your question about SQL Server 2025 Change Feed, it improves change tracking compared to CDC, but a database restore still represents a reset of the source state. In other words, a restore would still require the mirroring relationship to be re-established rather than seamlessly continuing from where it left off.
So, if regular restores are part of your operational process, an ingestion pattern based on Copy Activity or Dataflow Gen2 is typically the more resilient and predictable option.
Best regards,
Omkar Shinde
Microsoft Fabric Enthusiast | Power BI Consultant
💡 If you found this response helpful, please consider giving it a Kudos.
✅ If this resolves your question, please mark it as the Accepted Solution to help others in the community.
v-shchada-msftCommunity Support
Hi fabricpribeiro,
Thank you for reaching out to the Microsoft Fabric Community Forum and thanks to Omkar_1712 for sharing helpful insights.
Just checking in, were you able to resolve the issue? If not please feel free to share an update and we will be happy to assist further.
Your feedback will also help others facing similar challenges.
Thank you.- v-shchada-msft
Hi fabricpribeiro,
Could you please confirm whether the issue has been resolved or is still occurring? Your update may help others with similar concerns. Please let us know if you need any further assistance.
Thank you.
