Open Mirroring - Tables go missing after Stopping and Starting Replication

[Steve_Pritchard]

I'm using Open Mirroring to mirror 12 SQL Server tables to a Fabric Mirror database on my F64 capacity.

I've noticed that if I use the "Stop Replication" option, wait for everything to report as being "Stopped", and then hit the "Start Replication" button, then not all of the tables appear to come back.

Out of the 12, 5 are missing for the SQL Endpoint - they show up in the replication list, but the "Rows Replicated" is blank, and the "Last Completed" just shows "--" (two dashes).

The tables that are missing appear to be the larger tables (millions of rows), and I have seen one table of approximately 5 million rows show as "0" rows replicated (shortly after re-starting Replication) update to the appropriate number within a few minutes, but the others are unavailable over half an hour later.

My suspicion is that it could be just stalled because of the size of the tables, but I'm not sure if there's a log or anything that I can access to confirm this one way or the other.

Does anybody have any suggestions?

[AyusmanBasu0604]

I am facing the same issue and for me it's a fairly small table with 4 records. The moment I stopped and re-started the table vanished from SQL Endpoint/Mirrored DB. I tried uploading new files to the same Folder, with correct naming conventions. It's just that the files just queue up one after another like "00000000000000000004.parquet", 5,6... But the table doesn't get created anymore even though I have clearly set RowMarker accordingly to perform insert operations.

Any suggestions please?

[Steve_Pritchard]

The files need to start at 1 again for it to work. If it is trying to carry on as it left off, it even if you are re-seeding the table from scratch but keeping the old parquet file sequence numbers, then it won't work. 

This is why I think it's a bug, but the motivation from Microsoft's point of view is that once these parquet files have been processed, then they are just costing them storage space.

[v-sgandrathi]

Hi @Steve_Pritchard,

To resolve the issue of missing tables after restarting replication in Open Mirroring, try the following workarounds:

Ensure the Log Reader Agent and Distribution Agent are running without errors. If they're stalled, replication can be delayed, especially with large tables.

If some tables are missing, stop replication for those tables, remove them from the replication setup, then re-add and restart replication to trigger a full sync.

Increase the batch size and commit batch size to handle large tables more efficiently. Also, check that your system has enough CPU, memory, and network bandwidth for replication.

Review Replication Agent Logs, SQL Server Error Logs, and Windows Event Logs for any issues. Use SQL Profiler to track detailed replication events.

Trying these steps should address the issue with missing tables and ensure that replication for larger tables proceeds smoothly after restarting replication.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you for using Microsoft Community Forum.

[Steve_Pritchard]

Thanks for the response.

As this is an Open Mirroring solution, I'm not convinced the status of the Log Reader and Distribution Agents is relevant. I would have thought that the stopping and starting of the Replication Process would flush the tables, and then re-evaluate the contents of the Landing Zone. The status of the SQL Server isn't relevant unless I'm trying to mirror further updates, and that would simply generate more parquet files.

I think this is what it's doing, and it might be a problem in the parquet files that is causing the system to stall. I've noticed that there are more parquet files than I expected, and I'm thinking that the earlier files are from previous attempts to mirror the table of that name. My concern is that although I'm providing parquet file "00000000000000000001.parquet", I think it might be getting renamed based on the existing folder contents, and thus added to the end of the file chain.

I'm unable to get the Mirror Log to show anything when I try and engage Workspace Monitoring, but I suspect I have parquet files of different schemas because my attempt to delete the table using the API isn't fully completing before I try to recreate it.

[v-sgandrathi · Accepted solution]

Hi @Steve_Pritchard,

Thanks for providing such a detailed explanation. Based on what you've described, it seems the issue may be related to how parquet files are managed and potential schema mismatches during the deletion and re-creation of tables.

Here are a few steps that should help resolve the problem:

Ensure each parquet file has a unique name and isn’t being renamed or appended during replication restarts. Use a clear naming convention, such as including a timestamp or version number, to avoid conflicts with older parquet files from previous replication attempts.

Check for old or unnecessary parquet files in the Landing Zone, as they might interfere with the new replication cycle. Set up a cleanup process to remove these files before restarting replication.

Review the logging configuration in Workspace Monitoring to ensure it’s properly capturing replication events. This will help you identify any specific issues when tables fail to sync.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Thank you.

[Steve_Pritchard]

Hi @v-sgandrathi 

I think you are on the right lines.

Using the OneLake Explorer extension, I could see that one of the tables had parquet file indices that weren't as expected, as I described in my earlier post. However, the other 4 tables looked fine, apart from the fact that all but the last parquet file were held in a _ProcessedFiles sub-folder.

As soon as I moved the parquet files from the sub-folder back up to the table's main folder, then the replication started again automatically.

So, my conclusion is that any table that has multiple parquet files in the Landing Zone will not successfully survive a Replication Stop/Start cycle. However, the table can be resurrected if the contents of the _ProcessedFiles folder are moved back into the table's root folder. (Once the table is resurrected, then all but the latest parquet file will be moved back to the _ProcessedFiles folder).

This feels like a bug, to be honest. I would expect a Stop/Start cycle to handle the fact that some of the parquet files got moved to the _ProcessedFiles sub-folder.

[austin_infinite]

I have the same issue - it does seem like a bug. Stopping the replication then starting it back up removes the tables from the SQL endpoint. To get them back I need to move them from _FilesReadyToDelete folder to the table folder, a level up.