Forum Discussion

lkara777's avatar
lkara777
Regular Visitor
1 year ago

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

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?

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hilkara777- 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);




  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Fabric doesn’t support nonclustered primary keys.

    After you recreated replication, some tables in Azure SQL DB ended up with nonclustered PKs, so Fabric stopped syncing them — leaving data 1 day behind.

    👉 Fix: Make sure PKs in Azure SQL DB are clustered before mirroring.