SQL Server Mirroring - how to identify latest replicated records

[krisjones]

I am using SQL Server Mirroring to mirror my SQL Server database called XX into a Fabric workspace.

I created a Lakehouse and a shortcut in the Lakehouse to the XX database so I can access it in Notebooks.

I'm building a data warehouse from the XX database using ETL code in Spark Notebooks.

Requirement:

I need to now what are the latest records that were replicated so I can do incremental loads into my data warehouse.

I've been using Change data feed | Delta Lake with my other ETL code that does not use mirrored databases.

Since a mirrored database is stored in OneLake in delta format, I tried to use CDF by first turning it on for one mirrored table in my Lakehouse. 

ALTER TABLE Slide SET TBLPROPERTIES (delta.enableChangeDataFeed = true)

Problem:

I get the following error:

Operation failed: "Forbidden", 403, PUT

This request is not authorized to perform this operation using this permission.

I guess since the delta tables are managed by OneLake, I cannot change anything. 

Other facts:

- I am the Fabric Administrator and an Admin on all workspaces

- The XX database does not have any timestamp columns that I can use.

Question:

Is there a way to turn on delta lake CDF on the OneLake mirrored database tables? 

If not, how can I know which records have been updated/replicated since the last time I ran my ETL code when using SQL Server Mirroring? 

[v-venuppu]

Hi @krisjones ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @krisjones ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[rohit1991]

Hi @krisjones 

Change Data Feed (CDF) can’t be enabled on mirrored databases in Fabric because those tables are managed by OneLake , they’re read-only and don’t allow property changes like enableChangeDataFeed.

Could you please try below steps:

• CDF Limitation: Works only for Delta tables you create or control, not mirrored ones.

• Track updates from SQL Server: If your table has a LastModifiedDate or UpdatedOn column, use that to detect new or changed records in ETL.

• No timestamp column:Add one or maintain a small log table in SQL Server to capture last-updated times.

• Alternative approach: Use Change Tracking (CT) or Change Data Capture (CDC) on SQL Server , they’re built for incremental updates.

• About the 403 error: That’s expected; Fabric blocks direct edits on mirrored tables since they’re managed by the service.

[krisjones]

@rohit1991 
I'm already using SQL Server change tracking but I would like to use SQL Server Mirroring because it's much simpler. But if there is no way to have similar functionationality as Delta Lake CDF in Fabric on the mirrored database then I can't use mirroring.

[rohit1991 · Accepted solution]

Hi @krisjones 

SQL Server Mirroring helps keep databases synchronized for high availability, but it doesn’t provide the same row-level change tracking that Delta Lake’s Change Data Feed (CDF) offers in Fabric. Mirroring is designed mainly for disaster recovery and ensures both databases stay identical, but it doesn’t store information about what data was changed, updated, or inserted. That’s why Fabric can’t enable CDF on mirrored tables , it has no change metadata to work with. If your goal is to track incremental updates, you can use SQL Change Tracking (CT) or Change Data Capture (CDC) instead, as both are made for identifying new or modified records. You can enable CT or CDC on your source SQL database before mirroring and then use that data in your ETL or Fabric pipelines. This setup allows you to capture only the changes without stressing the mirrored database. In short, mirroring keeps data in sync but not aware of changes, so CT or CDC is the best way to achieve incremental refresh similar to CDF.