Change Data Capture - initial load of historical data

Question

I'm upgrading my SQL Server 2000 database to SQL Server 2008 R2. I want to make use of Change Data Capture feature. Im my existing application I have the similar functionality, but I'm using triggers and historical table with Hst_ prefix with almost similar schema as the original tables.

My question is: is there any way to migrate my data from Hst_ tables to the tables used by CDC feature?

I was thinking of doing that like this:

• I have the table Cases.
• I'm using my custom historization mechanism , so I also have also three triggers (on insert, update and delete) and a twin table Hst_Cases.
• Now I'm enabling CDC on table Cases
• CDC creates function, which returns historical data (fn_cdc_get_all_changes_dbo_Cases) and also a system table, which actually holds the data (cdc.dbo_Cases_CT).
• I could insert data from Hst_Cases to cdc.dbo_Cases_CT, but I have the following problems:
  • I don't know how to get __$start_lsn and __$seqval.
  • It is difficult to figure out __$update_mask (I have to compare each two rows).

Is there the only way to do that? I want to avoid the situation then I join "new" historical data with the "old" historical data from Hst_ tables.

Thanks!

Answer 1

You typically don't want to use the capture tables to store long-term change data, it would be better to have an SSIS package move the capture data to permananent tables. If you do use them, I think if you ever have to restore your database, they'll be empty after restore unless you use the KEEP_CDC option when restoring. You'll also need to disable the job that automatically purges the capture tables.

If you create your own tables for storage, you can omit the lsn and mask fields.