Fabric and SQL Azure Mirroring - duplicate data on Fabric side

[jon_clemens]

We have mirroring setup, and it has been running fine.  But then we noticed that we are starting to see duplicate records in Fabric, almost like updates on the SQL side are being seen as changes, but then end up creating a new record, rather than update the existing record. 

Is this a known issue?  Any insight or explanations?

[v-nikhilan-msft]

Hi @jon_clemens 
Thanks for using Fabric Community.
Currently there is no such type of known issue for Mirroring Databases.
Can you please provide me the screenshots for the issue? This would help me understand what's happening.

Thanks

[twhite_cargas]

Hi @v-nikhilan-msft thanks for the response. I don't have a screenshot of the Azure db, however if I create a dataflow in Fabric, you can see that the column 'TransferID' is the primary key, and if I use the 'Keep Duplicates' transformation on that column, it results in no rows (as expected). The db admin has also confirmed for me that there are no duplicates on the Azure side.

However, in the mirrored Fabric database of the same source, I created the same query to keep the duplicates, and there are multiple duplicated records as seen here:

The mirrored db is actively replicating, and not displaying any errors. I can see new records as recently as one minute ago. There are over 11M records in the table, with 38 duplicates, and each of the duplicates were created between April 19 - 23. We started replicating the db on April 16.

[v-nikhilan-msft]

Hi @jon_clemens , @twhite_cargas 
You can try the below steps:

1) Stop and restart Mirroring, this will reseed all the tables.. {the turn it off and on again option}
2) Better option, is to deselect this table in the Mirroring Configuration, save the configuration, then add the table back in again.

If the issue still persists then we need our internal team to investigate on this. So I request you to create a support ticket here: Microsoft Fabric Support and Status | Microsoft Fabric
After creating a Support ticket please provide the ticket number as it would help us to track for more information.
Hope this helps. Please let us know if you have any other queries.

[twhite_cargas · Accepted solution]

Option 1 (unplug it and plug it back in) did the trick! 

Is it a best practice with mirrored databases to periodically give them a restart like this?

[v-nikhilan-msft]

Hi @twhite_cargas 
No - turning it off and on, moves all the data again. Its easy, but an expensive way to fix it.
The issue with duplicate rows might be happening due to a bug. Hence I have escalated the same, and our team is working on this to fix it. 

Thanks