Sql Server 更改数据捕获：添加列时保留历史记录？

Question

将新列添加到配置为更改数据捕获 (cdc) 的表时，捕获实例表不会有新列，直到为源表禁用并重新启用 cdc。在此过程中，现有的捕获实例将被删除。

我想我可以将现有数据复制到临时表中，然后使用以下 SQL 复制回来。但是，其他 CDC 元信息（例如 cdc.change_tables.start_lsn）将变得无效。

如何使用相同的捕获实例名称来保留捕获实例历史记录（如果有的话）？

谢谢， 富有的

/*Change Data Capture Test - Alter table definition test */

/*Enter restricted mode so we don't lose data changes during this process*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC OFF
alter database ChangeDataCaptureTest set RESTRICTED_USER with ROLLBACK IMMEDIATE
go

/*Add a column to the table*/
alter table dbo.Table1 add value3 varchar(20) DEFAULT '' not null

/*Copy the existing change tracking into a temp table*/
select * into cdc.dbo_Table1_temp from cdc.dbo_Table1_CT

/*Add the new column to the temp table so that we don't have to map
all columns when we copy back, note that we use NULL as the default*/
alter table cdc.dbo_Table1_temp add value3 varchar(20) DEFAULT NULL

/*Disable CDC on the source table, this will drop the associated cdc table*/
exec sys.sp_cdc_disable_table 
@source_schema='dbo',
@source_name='Table1', 
@capture_instance='dbo_Table1'

/*Enable CDC for the table which recreates the CDC table*/
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Table1',
@role_name     = NULL,
@supports_net_changes = 1,
@filegroup_name = N'ChangeDataCapture'
GO

/*Insert values from the temp table back into the new CDC Table*/
Insert into cdc.dbo_Table1_CT 
SELECT * 
From cdc.dbo_Table1_temp
go

/*Drop the temp table*/
drop table cdc.dbo_Table1_temp

/*Go back into multi-user mode*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC ON
alter database ChangeDataCaptureTest set MULTI_USER
go

/*Add a new row to the table*/
insert into table1
values(12,'zz','g')

Answer 1

丰富，

保存此类数据的最佳方法是创建一个暂存持久表来定期捕获 _CT 表数据。了解 cdc 数据在被端点（仓库/数据集市等）使用之前通常具有较短的保质期，您可以确保任何更改在维护窗口内完成，此时 _CT 表数据被复制到在实施更改之前进行分阶段。

其中需要考虑的一个方面是，一旦 _CT 架构发生更改（通过添加或删除一列或多列），用于将该数据拉出到端点的过程也必须更新。

为了克服这个问题，我们实现了一个脚本存储，用于存储暂存表的预期架构（在 _CT 和端点之间使用），一旦在客户端数据库上实施更改，我们就会将数据从暂存移动到端点并更新暂存架构。

希望这能提供一些思考。

Answer 2

我认为您还必须写出 lsn 记录，然后将它们带回到 lsntimemapping 表中。