Azure SQL DB bug in identifying the primary key for table

[jozseftuska]

Hi.

We want to try Fabric with a mirrored MSSQL database, however we get this error for several tables: This table can't be mirrored to Fabric because the primary key column uses one of the following data types: computed types, user-defined types, geometry, geography, hierarchy ID, SQL variant, timestamp, datetime2(7), datetimeoffset(7) or time(7).

However it's not true, we have int Id as primary key. I think it's because we have another unique constraint apart from the ID that also contains a date field. Is it a bug that the PK is being identified incorrectly or is it a limitation that we can't have unique constraint for date? (I did not find in the documentation).

[Anonymous]

Hi,@jozseftuska .I am glad to help you.
I found the following documentation on the relevant restrictions

URL:

Limitations and Behaviors for Fabric Mirrored Databases From Azure SQL Database - Microsoft Fabric |...

According to the documentation, certain data types cannot be used for the primary key, including calculated type, user-defined type, geometry, geography, hierarchy ID, SQL variant, timestamp, datetime2(7), datetimeoffset(7), and time(7)

You mentioned that the primary key of your table is the Id column of type int, but if there are other unique constraints in the table that contain date fields, this may cause the Fabric to mistakenly assume that the primary key is using an unsupported data type when processing the data.
Please try the following actions:

Make a backup of the table data that is currently experiencing the problem to prevent data loss. Temporarily remove the unique constraint that contains the date field.
Try loading the modified mirror table into the fabric again to see if the same error still occurs, if the problem disappears then this may indeed be an unwritten constraint.

Check the precision of the data in your mirrored table to ensure that it meets the requirements of the Fabric. If the problem persists after testing and you are able to ensure that the format and precision of the relevant data types in the mirrored table meets the documentation, then you may need to provide more detailed information, which will help you find the problem quickly.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[jozseftuska]

Dear Carson,

I can confirm that the mirrorring was indeed successful after removing the unique constraint on the date field. Would it be considered a bug on your side that will be fixed in the future or we have to live with it? In the latter case, could you recommend a workaround?

Thank you in advance,

József

[Anonymous]

Hi,@jozseftuska .Thank you for your reply.

There does seem to be a relationship with the limitations described in the documentation. I think you could try modifying your data source to get around this limitation. (Change the data type of the date column, e.g. text type)
If possible, creating a Microsoft Support ticket will give you more detail on the cause of the problem.
The Link of Power BI Support:

Microsoft Fabric Support and Status | Microsoft Fabric

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian