Forum Discussion

JVDA98's avatar
JVDA98
Frequent Visitor
8 months ago

Mirrored On-Premise SQL Server Database not updating

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 re...
data engineering
lakehouse
nielsvdc's avatar
nielsvdc
Super User
8 months ago

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's avatar
JVDA98
Frequent Visitor
8 months ago

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's avatar
    JVDA98
    Frequent Visitor
    8 months ago

    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.