Source table column ADD's - Proper work-flow?

[RonFusionHSLLC]

Looking to discover a work-flow that works every time...

I have both a SS source (MS-CDC) and a SS target.

What is the proper set of steps to add a column in a source table without having to re-load the target table?  Is this even possible?

I noticed that adding a column seems to propagate the column to the source table and __CT table and then table gets 'suspended'.

My method of disable/reenable replication on the table, reload source seems to work...but is there a way to avoid reloading the target?  To avoid losing __ct changes, I run compose CDC capture to my DW before making any DDL changes, since I haven't found a method that eliminates the reload step.

I don't understand why the table gets suspended, when Replicate has added the column to both the target and the __CT table for the target.

What are the next steps to get the table to replicate data again?

Ron

[Heinvandenheuvel]

I believe it should just work as you describe. Was there a further detail error around the suspension? Perhaps a non-null attribute which wouldn't work for already existing change rows.

I'd recommend a tiny (3 columns: ID (auto increment), DATETIME (Default current) , COMMENT - 3 starting rows ) test table first to retry. This will allow you to set maximum logging and to safely share SQL  full task JSON and/or task log (as .TXT) details for your tests, should they fail.

Hein.

[RonFusionHSLLC]

'as described'  do you mean it should 'suspend' a table even after it made the DDL changes in the target, or in order to get back to replicating after adding a column I need to disable/enable source table replication, then reload the target?

Here are the logs/task json - attached.

Somewhere around line 676 is where it suspends the target....just after having done the alter table on both the source and __CT tables.

Not sure what I'm looking for as far as why it failed to just start replicating the table after the DDL, without suspending.

Ron

[SwathiPulagam]

Hi @RonFusionHSLLC ,

Below is the limitation when you use MS-CDC as a source endpoint:

• Table-level DDLs are not supported. When a table DDL is encountered, the DDL will be transferred to the target and the table will be suspended to allow the CT table to be manually aligned.

User guide link for your reference:

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/Azure...

Thanks,

Swathi

[RonFusionHSLLC]

What does 'manually aligned' mean?  Oh, and BTW, this is NOT an Azure Sql Managed instance - it is a Windows server with SQLServer installed.  We abandoned Azure Sql Managed instances, too many issues.

If it is supposed to mean adding the columns, then that doesn't make sense...since the alter table add column on the source propagates to both the target table and the target table __CT table.  In the verbose logs, the tasks to add columns to both the target and __ct tables are successful.

So, 'manually aligned' must mean something else...what is the definition of that 'something else'

ron

[SwathiPulagam]

Hi @RonFusionHSLLC ,

Even for Using Microsoft SQL Server (MS-CDC) as a source the same limitation applies.

Below is the user link for your reference using Microsoft SQL Server (MS-CDC) as a source:

https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/MicrosoftSQLServer-MSCDC/Micros...

Thanks,

Swathi

[RonFusionHSLLC]

Yep, still don't know what 'manually aligned' means.  I think it's important to spell it out, since it's about as vague as one can get.

Again, the __CT table and the target table both were altered to properly reflect the changes in the source, right before the target was suspended.  So, as far as table structure, they are 'aligned', but still suspended.

What are these 'manual alignment' tasks that need to be executed, what do they do and when should they be performed?

Ron

[SwathiPulagam]

Hi @RonFusionHSLLC ,

If the source team didn't inform and perform the DDL change then the table will be suspended as it requires some manual effort to fix (disable and enable MS-CDC for the task) this scenario. Please follow below steps if DDL change is unplanned.

DDL Change Handling
1. do the alter table
2. stop the task
3. disable ms-cdc for table
4. resume task
5. unsuspend table or reload the table
- NOTE: Only the first DDL change will be picked up, will need to follow 'Truncation' steps if a DDL change is done while the table is suspended.

If DDL change is a planned activity then please follow the below steps:

Alternate DDL Change Handling
1. stop task
2. disable ms-cdc for table
3. modify source table
4. modify target table
5. start task with metadata only run (Create missing tables and then stop) – this will refresh the internal metadata without losing position
6. enable ms-cdc for table (if task is not set to do it automatically)
7. resume task

Thanks,

Swathi

[RonFusionHSLLC]

Perfect, I tested the first scenario.

First scenario - item 3 - also included re-enabling cdc on the table

First scenario works perfectly - no reload is needed, simply un-suspend the table - provided NO changes are made to the source table data while the table is suspended.

What I found was that if data was changed on a record while the table was suspended, even if the task was resumed and the table un-suspended, those records that were changed no longer get updated in the target.  However, the __CT table do reflect the changes made after resuming and un-suspending the task.  All other records in the table can be updated and their changes will reflect in the target and the __CT.  This is horrible, as no warnings, no errors are produced in this situation.  I'm going to call this a serious bug and will log a ticket!

I totally understand the 'NOTE'...

ie.  This would pickup both column add the changes...

alter table x add column added_column int, secondadded_column int

This would only pickup the first change - as the table gets suspended after the first change, and no CDC changes propagate during suspension

alter table x add column added_column int

alter table x add secondadded_column int

Ron

[SwathiPulagam]

Hi @RonFusionHSLLC ,

As per the above process, we suggest to do unsuspend the table or reloading the table. 

So Unsuspend the table if there are no changes happened after the table gets suspended. If there are any changes happened between suspend and unsuspend the table those changes will be lost and this is the expected behavior.

And If there are any changes happened then reload is the only option (starting from back in time also works but it effects all the tables) in order not to lose any changes.

Also please share your ticket number. I will have a working call with you and will explain if you have any doubts regarding this.

Thanks,

Swathi