SQL Server source limitations Replication of table without primary key

[MoeE]

Hi guys.

I saw in the SQL server source endpoint limitations "If your database is not set up for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but bear in mind that in such a setup only INSERT/DELETE DML events will be captured. UPDATE events will be ignored" 

I tested this with neither MS-REPLICATION nor MS-CDC enabled and a table with no PK but the task failed with the error "The MS SQL Server instance is not set up for Replication".

So is that limitation wrong or am I missing something? It seems that when both MS-REPLICATION and MS-CDC are not enabled then even full load won't work. Also when MS-REPLICATION only is enabled then this rule seems true. Can I please get some assistance on this?

Thanks,

Mohammed

[john_wang]

Hello @MoeE ,

Thank you for opening the article.

You are right. With neither MS-REPLICATION nor MS-CDC enabled, only INSERT/DELETE can be captured. UPDATE cannot because insufficient information recorded in TLOG.

You may disable the MS-REPLICATION check by adding an internal parameter in source endpoint:

ignoreMsReplicationEnablement

set to TRUE and startup task again.

Let me know if it works for you.

Regards,

John.

[MoeE]

Hi John,

Thanks for the reply. However what I'm trying to say is that with neither MS-REPLICATION nor MS-CDC enabled, nothing is captured. No INSERT/DELETE/UPDATE, and no full load either. The task simply fails.

Thanks,

Mohammed

[john_wang]

Hi @MoeE ,

If both Full Load and CDC are enabled in a task, while the task starting up, both Full Load thread and CDC thread startup at same time:

Full Load thread - transfer the history data

CDC Source Capture thread - capture changes and cache them in memory or harddisk

In your scenario, the CDC thread failed to startup and the task stopped. No Full Load or CDC threads transfer data yet. I think this is probably the reason.

You may add the internal parameter in source endpoint, or disable change processing in task setting and see at least the Full Load should startup successfully.

Hops this helps.

Regards,

John.

[MoeE]

Hi John,

Yep I can run Full Load only, but ultimately without MS-REPLICATION or MS-CDC configured on SQL Server, Replicate does not capture the INSERT or DELETE events despite the documentation saying that it should. This is where I am confused. Can you please confirm? 

Thanks,

Mohammed

[john_wang]

Hello @MoeE ,

Sounds like you are missing some data... can you confirm the "Recovery Model" of the database is set to "Full" or "Bulk-logged"? for example:

Regards,

John.

[MoeE]

Hi John,

Yep these settings are correct. Just to clarify this is what I did. 

(1) In SQL Server I created a table called Fish without a primary key.

(2) Did a backup and recovery mode is set to Full. 

(3) In SQL Server, I disabled distribution.

After disabling distribution both MS-REPLICATION and MS-CDC are now disabled. 

(4) I run the task in Full load only. It works successfully then stops.

(5) I try to start the task with change processing but the task starts then stops instantly.

I can't see how DELETE/INSERT are captured from step 5.  From here I am going with the assumption that the limitation quoted is a typo because without MS-REPLICATION or MS-CDC enabled, no sort of change will occur. Additionally the prerequisites state that MS-REPLICATION is required which is a contradiction to the limitation. 

Is this correct?

Thanks,

Mohammed

[john_wang · Accepted solution]

Hello @MoeE ,

Thanks for your patience.

I'd confirmed in my labs that Replicate still captures INSERT and DELETE DMLs even both of MS-REPLICATION and MS-CDC are disabled. Certainly, the UPDATE DMS is lost.

(My test env is: Replicate 2023.5 + SQL Server 2019 on Windows Server 2019)

There are warnings in the task run time (with ignoreMsReplicationEnablement set true):

• The MS SQL Server instance is not set up for Replication.
• MS-REPLICATION is not enabled for table 'dbo.test'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead.

Screen sample:

Please set source_capture/source_unload to Verbose and check the task log file to understand further. If you need support help, please feel free to open a support ticket with attaching Diag Packages, we'd love to help to check further.

Regards,

John.

[MoeE]

Hi John,

Thanks for going through this for me. I will try this latest method now.

Regards,

Mohammed

[MoeE]

Hi John, 

Yep I was able to do it with the internal parameter. Thanks for the help.

Regards,

Mohammed