9

I have a requirement where I want to enable CDC on newly added columns of table, but I cannot disable CDC and enable it again. Is there any way I can achieve this?

I found a solution where I can copy old CDC table values into a temp table, then disable CDC and then enable CDC with new table schema. Later copying the temp table values into new CDC table and updating the LSN value.

Instead of the above I need a solution where I can include the new column into the CDC table while the CDC is enabled.

Mohan
  • 95
  • 1
  • 1
  • 7
  • 1
    Possible duplicate of [CDC table not working after adding new columns to the source table](http://stackoverflow.com/questions/14958119/cdc-table-not-working-after-adding-new-columns-to-the-source-table) – M.Hassan Jul 26 '16 at 10:31
  • @M.Hassan I don't want to disable the CDC at any case while adding the new column. – Mohan Jul 26 '16 at 11:21

2 Answers2

11

CDC supports two instances of capture tables. So, You can do following steps:

  1. Add new column
  2. Add new cdc-capture inctance
  3. Move data from old table to the new one
  4. Disable old cdc instance

This solution prevent you from stopping collecting changes and you won't lose data.

EXEC sp_cdc_enable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @role_name = NULL,
    @filegroup_name = N'CDC',
    @capture_instance = 'common_EntityTypes'


ALTER TABLE common.EntityTypes
    ADD TestColumn int

EXEC sp_cdc_enable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @role_name = NULL,
    @filegroup_name = N'CDC',
    @capture_instance = 'common_EntityTypes2'

INSERT INTO cdc.common_EntityTypes2_CT
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,Id,Name)
SELECT
    __$start_lsn, 
    __$end_lsn,
    __$seqval,
    __$operation,
    __$update_mask,
    Id,
    Name
FROM cdc.common_EntityTypes_CT

EXEC sp_cdc_disable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @capture_instance = 'common_EntityTypes'
Backs
  • 24,430
  • 5
  • 58
  • 85
  • but when I have performed DDL operation on newly added column "TestColumn" changes are not captured in the newly created instance cdc.common_EntityTypes2_CT – Mohan Jul 26 '16 at 11:17
  • "Move data from old table to the new one". Would it be more accurate to say "move your ETL process to use the new CDC instance"? That is, you can dovetail the two instances by using the min LSN for the second instance as the endpoint for the first capture instance. Once you've processed all the records up to and including that endpoint from the first, you switch your ETL to use the second and continue processing from there. Personally, I like to use abstraction (i.e. synonyms or a view) to hide the fact that I'm switching CDC instances. – Ben Thul Jul 26 '16 at 14:13
  • @Mohan you must create new cdc instance after adding new column – Backs Jul 26 '16 at 15:21
  • @BenThul yes, it's one of possible solutions. In our project we use a view, but I don't know all requirements in this case – Backs Jul 27 '16 at 05:18
  • 1
    I know this is about adding new columns, but one should be careful about removing columns as well because they can affect the exact computation of what columns changed based on the historical masks. It is very easy to forget that one column was dropped sometimes in the past before the new one is added. Once you disable and re-enable cdc your historic masks are screwed because they reflect structures that where in effect at that time. The solution would be to save the columns lists along with each insert/update. Or somehow take a snapshot of the table structure somewhere and reference that. – boggy Jul 24 '20 at 01:56
  • is there a way we can disable CDC on the table without deleting cdc instance, as I see `sys.sp_cdc_disable_table` will remove cdc capture instance table. – minhhungit Oct 16 '22 at 05:17
3

The solution above works well when you have a single instance on the target table, but if you have two instances already, you might need to think a little further out and use some planning.

The question of loss of data is relevant but you might consider this question when designing the cdc process initially, one very pertinent item being, what is the end-point for the data? For example, if you are using a BI solution, perhaps using a warehouse, or a simple OLAP DB like a data-mart for a reporting solution, then you might consider introducing maintenance windows or utilizing a current maintenance window. The process is simple and not unlike the answer by Backs:

  1. Transfer all current _CT data into the end-point.

  2. Disable the current instance on the table (sp_cdc_disable_table)

  3. Enable the instance again with the updated column list (sp_cdc_enable_table)

This allows for the changes to be effected even if two instances already target the same table with no loss of client data.

Note: the use of the column list parameter is not so much a requirement (if you wish to capture all columns) but it might be preferable for consistency when other developers might need to review the code. Albeit, it is just as easy to gain that data via the cdc.X system tables.

LogicalMan
  • 384
  • 2
  • 6