Schema Mismatch on Mirror database from SQL Server

[TWorku]

Hi everyone,

I’m having trouble with a mirrored database from SQL Server 2017. The initial synchronization works fine, but when the source data updates, I get a schema mismatch error.

After checking the schema, I found that a column defined as NOT NULL in the source table appears as Is_Nullable = Yes in Fabric. I don’t want to modify the source table schema. I’ve already tried restarting replication and CDC, but the issue persists. The error is as follows.

ErrorCode: SchemaMergeFailure, Message: Failed to write table 'dbo\xxxxxxx' due to schema mismatch. Schema merge failure due to potential invalid input. Cause: Type mismatch for column 'Date'. Incoming type: 'date' (Nullable: True), existing type: 'date' (Nullable: False). 

Has anyone else experienced this or found a workaround?

Thanks in advance for your help!

[v-venuppu]

Hi @TWorku ,

Great to hear that it's working from your end!I would suggest accepting your approach as the solution so that  It would be really helpful for others in the community who might be facing similar issues and can address them quickly. 

Thank you.

[v-venuppu]

Hi @TWorku ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

The schema mismatch occurs because the nullable flag differs between SQL Server (NOT NULL) and Fabric (Nullable = True). Even though the data type (date) matches, Fabric treats this as a schema conflict. The mirroring process often marks all columns as nullable by default.Since you don’t want to modify the source, fix it on the Fabric side by altering the target column to allow NULLs:

ALTER TABLE dbo.xxxxxxx ALTER COLUMN [Date] date NULL;

Then restart the mirroring/CDC job - the incoming and target schemas will align, and the SchemaMergeFailure error will be resolved.

[TWorku · Accepted solution]

The Alter command cannot be used on a mirrored database due to its read-only state.

Thank you to @lbendlin  and @v-venuppu  for trying to help. The issue has been resolved by:

1. Disabling and re-enabling CDC on the source database.

2. Re-enabling CDC on all tables one by one.

3. Creating a new connection with the latest gateway.

4. Establishing a new database mirror.

The solution has been stable and monitored successfully for the last four days

[lbendlin]

The "not null" is not your issue.  Your issue is that Fabric doesn't support auto increment primary keys.