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 ta...
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);