Two new columns were added to our source table while CDC was still enabled on the table. I need the new columns to appear in the CDC table but do not know what procedure should be followed to do this? I have already disabled CDC on the table, disabled CDC on the DB, added the new columns to the cdc.captured_columns table, and enabled CDC. But now I am getting no data in the CDC table!
我们的源表中添加了两个新列,而表中仍然启用了CDC。我需要新的列出现在CDC表中,但不知道应该遵循什么程序来执行此操作?我已经在表上禁用了CDC,在数据库上禁用了CDC,将新列添加到cdc.captured_columns表,并启用了CDC。但现在我在CDC表中没有数据!
Is there some other CDC table that must be updated after columns are added to the source table? These are all the CDC tables under the System Tables folder:
在将列添加到源表后,是否还有其他CDC表必须更新?这些是System Tables文件夹下的所有CDC表:
- cdc.captured_columns <----- where I added the new columns
- cdc.change_tables
- cdc.dbo_myTable_CT <------ table where change data was being captured
- cdc.ddl_history
- cdc.index_columns
- cdc.lsn_time_mapping
- dbo.systranschemas
cdc.captured_columns <-----我添加了新列
cdc.dbo_myTable_CT <------表中正在捕获更改数据的表
1 个解决方案
#1
9
I recommend reading Tracking Changes in Your Enterprise Database. Is very detailed and deep. Among other extremly useful bits of info, there is such as:
我建议您阅读企业数据库中的跟踪更改。非常详细和深刻。在其他极其有用的信息中,有如下:
DDL changes are unrestricted while change data capture is enabled. However, they may have some effect on the change data collected if columns are added or dropped. If a tracked column is dropped, all further entries in the capture instance will have NULL for that column. If a column is added, it will be ignored by the capture instance. In other words, the shape of the capture instance is set when it is created.
启用更改数据捕获时,DDL更改不受限制。但是,如果添加或删除列,它们可能会对收集的更改数据产生一些影响。如果删除了跟踪列,则捕获实例中的所有其他条目将对该列具有NULL。如果添加了列,则捕获实例将忽略该列。换句话说,捕获实例的形状在创建时设置。
If column changes are required, it is possible to create another capture instance for a table (to a maximum of two capture instances per table) and allow consumers of the change data to migrate to the new table schema.
如果需要更改列,则可以为表创建另一个捕获实例(每个表最多两个捕获实例),并允许更改数据的使用者迁移到新的表模式。
This is a very sensible and well thought design that considers schema drift (not all participants can have the schema updated simultaneously in a real online deployment). Having a multi-staged approach (deploy DDL, capture new CDC, upgrade subscribers, drop old CDC capture) is the only feasible approach and you should follow suit.
这是一个非常明智且经过深思熟虑的设计,它考虑了架构漂移(并非所有参与者都可以在真实的在线部署中同时更新架构)。采用多阶段方法(部署DDL,捕获新CDC,升级订户,丢弃旧CDC捕获)是唯一可行的方法,您应该效仿。
#1
9
I recommend reading Tracking Changes in Your Enterprise Database. Is very detailed and deep. Among other extremly useful bits of info, there is such as:
我建议您阅读企业数据库中的跟踪更改。非常详细和深刻。在其他极其有用的信息中,有如下:
DDL changes are unrestricted while change data capture is enabled. However, they may have some effect on the change data collected if columns are added or dropped. If a tracked column is dropped, all further entries in the capture instance will have NULL for that column. If a column is added, it will be ignored by the capture instance. In other words, the shape of the capture instance is set when it is created.
启用更改数据捕获时,DDL更改不受限制。但是,如果添加或删除列,它们可能会对收集的更改数据产生一些影响。如果删除了跟踪列,则捕获实例中的所有其他条目将对该列具有NULL。如果添加了列,则捕获实例将忽略该列。换句话说,捕获实例的形状在创建时设置。
If column changes are required, it is possible to create another capture instance for a table (to a maximum of two capture instances per table) and allow consumers of the change data to migrate to the new table schema.
如果需要更改列,则可以为表创建另一个捕获实例(每个表最多两个捕获实例),并允许更改数据的使用者迁移到新的表模式。
This is a very sensible and well thought design that considers schema drift (not all participants can have the schema updated simultaneously in a real online deployment). Having a multi-staged approach (deploy DDL, capture new CDC, upgrade subscribers, drop old CDC capture) is the only feasible approach and you should follow suit.
这是一个非常明智且经过深思熟虑的设计,它考虑了架构漂移(并非所有参与者都可以在真实的在线部署中同时更新架构)。采用多阶段方法(部署DDL,捕获新CDC,升级订户,丢弃旧CDC捕获)是唯一可行的方法,您应该效仿。