tables with a nonclustered primary key are not supported on Microsoft Fabric platform

[lkara777]

I mirror data from an azure sql db to Fabric, but before azure sql db, we replicate data from an old db in a azure managed instance to azure sql db and then we mirror to Fabric. In the old db some tables have constraints, non clustered keys but in the azure sql db we do not copy those constraints only keep primary keys. But yesterday after recreate the replication, we had this error and I do not understand why, and also for those tables we have data in the Fabric but 1 day behind. Do you know why?

[SoundariyaEV]

Hi@lkara777- Hope this helps to solve the issue.
Root Cause:Fabric requires clustered primary keys for mirroring. If your Azure SQL DB has tables with nonclustered primary keys, mirroring may fail or lag.
Why it happened: After replication, some tables may have retained nonclustered keys from the old DB. Fabric doesn't support mirroring such tables.
Issue Fix: Check the index type of tables in Source Azure SQL DB, Drop the nonclustered primary key constraint and Create a clustered primary key, this will make the table compatible with Fabric mirroring.

You can use this query to list the constraints and indexes :

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_primary_key,
    i.is_unique,
    i.is_unique_constraint,
    c.name AS ConstraintName,
    c.type_desc AS ConstraintType,
    col.name AS ColumnName
FROM 
    sys.tables t
LEFT JOIN 
    sys.indexes i ON t.object_id = i.object_id
LEFT JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
LEFT JOIN 
    sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
LEFT JOIN 
    sys.key_constraints c ON i.object_id = c.parent_object_id AND i.index_id = c.unique_index_id
ORDER BY 
    t.name, i.name, col.column_id;

Query to Drop and Create index:

ALTER TABLE YourTableName DROP CONSTRAINT PK_YourTableName;

ALTER TABLE YourTableName ADD CONSTRAINT PK_YourTableName PRIMARY KEY CLUSTERED (YourPrimaryKeyColumn);