CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Dustin Davis
Dustin Davis

Reputation: 14585

SQL Server CDC: Track additional column after the fact

If CDC has been setup on a table, only tracking Columns A,D,E instead of the entire table, is it possible to add a column Z to the source table, then add column Z to the list of tracked columns for CDC? Is it possible to do this without losing CDC data?

I've looked around and the only examples I find are for tracking the entire table and not for cherry picking columns. I'm hoping for a way to update a table schema and not lose CDC history, without doing the whole copy CDC to temp table then back to CDC process.

Do you always have to create a new instance of CDC for schema changes?

SQL Server 2012

Upvotes: 2

Views: 915

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

The reason that CDC allows for two capture instances on a given table is exactly this reason. The idea is this:

  1. You have a running instance tracking columns A, B, and C
  2. You now want to start tracking column D
  3. So you set up a second capture instance to track A, B, C, and D.
  4. You then process everything from the original capture instance and note the last LSN that you process
  5. Using the fn_cdc_increment_lsn() function, you set the start point to start processing the new capture instance
  6. Once you're up and running on the new instance, you can drop the old one

Of course, if you're using CDC as history of all changes ever on the table… that's not what it was designed for. CDC was meant to be an ETL facilitator. That is, capturing changes to data so that they could be consumed and then ultimately discarded from the CDC system. If you're using it for a historical system of record, I'd suggest setting up a "dumb" ETL meaning a straight copy out of the CDC tables into a user table. Once you do that, you can implement the above.

Upvotes: 2

Related Questions