Mirrored On-Premise SQL Server Database not updating

[JVDA98]

I have a mirrored on-premise sql server.

The set-up & connection is working. I can query the mirrored database without any issue, but the database is not being updated by itself.

When I stop the replication & restart it, the new data is there, but this should happen automatically. Anyone has had this before?

The replication status is still running:

I have already recreated the mirroring database, updated the gateway, but none seem to help.

[v-venuppu]

Hi @JVDA98 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @nielsvdc @spaceman127  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.

[JVDA98]

The issue is currently with the product team for Microsoft Fabric, so no updates yet. 

[v-veshwara-msft]

Hi @JVDA98 ,

Thank you for the update.
Since this is currently under investigation by the Microsoft Fabric product team, we hope a resolution will be available shortly.

Please feel free to share any updates here when available, as it may help others facing a similar behavior.

Regards.

[spaceman127]

Hi @JVDA98,

As @nielsvdc  already suggested, these are methods to solve your problem.

Perhaps as additional information for us, what version of SQL Server are you using and is it up to date?
I once had a version that was not up to date and performed replication once but then stopped replicating.
I updated the version and then it worked.

It's just one approach, but maybe it will help.

Another phenomenon was that Fabric capacity was stopped at night to save costs and then restarted in the morning. After that, replication did not work either.

If none of that helps, I would open a support ticket if I were you.

Best regards

Feel free to leave kudos or accept it as a solution. This will also help other community members.

[nielsvdc]

We had the same problem with an Azure SQL Database and SQL Server 2019 Database. You mentioned you updated the version and then it worked. But what also happened is services were restarted and then started running correctly again.

Stopping and starting a Fabric capacity while mirroring is in progress might cause issues. Pauzing the mirroring first before stopping the capacity might prevent this issue.

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.

[nielsvdc]

Hi @JVDA98, there could be different reasons for the mirroring to be stalled.

• If you used the OAuth2 authentication method for the Fabric connection, you may need to reauthenticate. Then check if the connection is still online, otherwise reauthenticate.
• Your change_feed could be stalled. Stop and start the change feed on the database. Then stop and start the mirroring process again to reinitialize all data. This worked on my mirrored database.

EXEC sys.sp_change_feed_disable_db;
EXEC sys.sp_change_feed_enable_db @destination_type = 2; -- 2 = Fabric database mirroring

-- Check status
SELECT
  name,
  is_change_feed_enabled,
  is_data_lake_replication_enabled
FROM sys.databases
WHERE name = DB_NAME();​

You can also check the site Troubleshoot Fabric Mirrored Databases From SQL Server - Microsoft Fabric | Microsoft Learn.

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.

[JVDA98]

Hi @nielsvdc , thanks for the response.

So couple of updates;
- I had to manually start the CDC on the database & the table, look like it didn't start by default.

- Removed the mirrored db completely, but still the same issue.

- I ran

EXEC as SQLUser
SELECT TOP 10 * 
FROM CHANGETABLE(CHANGES [dbo].[Landtanks_Volume_Status], NULL) C
order by Time  desc;
REVERT;

 This shows me the changed rows, so they are detected by the user, but they are not synced to the mirrored database.

It might have something to do with the Fabric Agent (who is up-and-running), I restarted it to make sure ; as for a very small table, we got this warning at creation time:

I've looked for some solutions online, but they are not really straight forward & would surprise me if it would be so hard to accomplish this..

Thanks !

[JVDA98]

@spaceman127 @nielsvdc 

And as an extra: I wasn't able to run the above queries, because the sp / columns did not exist.

The SQL Server edition is: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

The gateway is on another server, but has acces to the SQL Server as we can read data.