更改表后无法重启CDC

Question

我需要为一个已经被跟踪的表向CDC添加几个列。我的方法如下：

EXEC sys.sp_cdc_drop_job 'capture'

BEGIN TRANSACTION

BEGIN TRY
    -- Copy existing data of the tracked table into a temporary table
    SELECT * INTO CDC.dbo_MyTable_Temp FROM CDC.dbo_MyTable_CT

    -- Add the new column to the temp table. This allows us to just use INSERT..SELECT * later
    ALTER TABLE CDC.dbo_MyTable_Temp ADD MyColumn INT NULL

    -- Disable CDC for the source table temporarily. This will drop the CDC table
    EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable'

    -- Reenable CDC for the source table. This will recreate the table with the new columns
    EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable', @supports_net_changes = 1, @role_name = NULL, @filegroup_name = 'CDCGROUP'

    -- Insert the old values into the CDC table
    INSERT INTO cdc.dbo_MyTable_CT SELECT * FROM cdc.dbo_MyTable_Temp

    -- Correct the start_lsn in CDC.change_tables. Otherwise all of the CDC stored procedures will be confused
    -- and although the old data will exist in the table the functions won't return it
    UPDATE cdc.change_tables SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_MyTable_Temp) WHERE capture_instance = 'dbo_MyTable'

    -- Drop the temp table
    DROP TABLE cdc.dbo_MyTable_Temp

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    RAISERROR('Error!', 16, 1)
END CATCH

EXEC sys.sp_cdc_add_job 'capture'

但是，当作业在重新添加后再次运行时，它会生成一个错误，表明违反了CDC.lsn_time_mapping上的主键约束。

有什么建议吗？

谢谢!

Answer 1

我无法找到错误的确切原因，因为对lsn_time_mapping的更新发生在扩展存储过程中，但我能够使用不同的代码来添加列。基本上，我坚持使用CDC存储过程，而不是对CDC表执行ALTER TABLE命令。代码如下：

-- Create a new capture instance for the table
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable_New', @supports_net_changes = 1, @role_name = NULL, @filegroup_name = 'CDCGROUP'

-- Copy all of the existing data from the old capture instance
INSERT INTO CDC.dbo_MyTable_New_CT SELECT *, NULL AS NewColumn1, NULL AS NewColumn2 FROM CDC.dbo_MyTable_CT

-- Disable the old capture instance
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable'

-- Now we need to jump through some hoops to rename the capture instance back to what it was:
-- Recreate a new capture instance with the old name again
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable', @supports_net_changes = 1, @role_name = NULL, @filegroup_name = 'CDCGROUP'

-- Copy all of the data over again
INSERT INTO CDC.dbo_MyTable_CT SELECT * FROM CDC.dbo_MyTable_New_CT

-- Remove the capture instance that we had been using to add the new columns
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'MyTable', @capture_instance = 'dbo_MyTable_New'

-- We need to correct the start_lsn in the cdc.change_tables table. Otherwise all of the CDC stored procedures and views will be confused and
-- although the old data will exist in the CDC table it wouldn't be returned by those CDC procs
UPDATE cdc.change_tables SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_MyTable_CT) WHERE capture_instance = 'dbo_MyTable'

请注意，我是在数据库停机期间执行此操作的。如果需要在数据库正在使用时执行此操作，则可能需要将WHERE子句添加到insert语句中，这样就不会将同一记录从一个表复制到已经存在该记录的表中。当两个捕获实例都处于活动状态时，任何影响表的DML都将将行放入这两个捕获实例中。