topic Schema Mismatch on Mirror database from SQL Server in Data Engineering

Schema Mismatch on Mirror database from SQL Server

TWorku — Thu, 30 Oct 2025 15:35:01 GMT

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!

Re: Schema Mismatch on Mirror database from SQL Server

lbendlin — Thu, 30 Oct 2025 23:31:57 GMT

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

Re: Schema Mismatch on Mirror database from SQL Server

v-venuppu — Mon, 03 Nov 2025 10:53:39 GMT

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.

Re: Schema Mismatch on Mirror database from SQL Server

TWorku — Mon, 03 Nov 2025 16:03:22 GMT

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:

Disabling and re-enabling CDC on the source database.
Re-enabling CDC on all tables one by one.
Creating a new connection with the latest gateway.
Establishing a new database mirror.

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

Re: Schema Mismatch on Mirror database from SQL Server

v-venuppu — Tue, 04 Nov 2025 09:49:38 GMT

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.