Category: sql server dbdesign
srivastavamehul2 on Wed, 29 May 2019 17:28:38
I want to monitor SQL server database and capture each changes as it happens. However, creating new CDC table an disabling previous one for each DDL change is not feasible in practice. Instead, I was thinking of using a sql script to make necessary change to the existing CDC table so that it now support newly added columns (in case of addition of new column). Can anyone tell what all changes I should do in the available CDC tables to make them support newly added column??
I would also appreciate if someone describe in detail what internal tasks 'sys.sp_cdc_enable_table' does (Like which new tables are created and what rows are inserted in CDC tables of the database)?
Dedmon Dai on Thu, 30 May 2019 03:12:12
>>Can anyone tell what all changes I should do in the available CDC tables to make them support newly added column??
You can create a second capture instance for the table that reflects the new column structure. This allows the capture process to make changes to the same source table into two distinct change tables having two different column structures.
Allowing the capture mechanism to populate both change tables in tandem means that a transition from one to the other can be accomplished without loss of change data. This can happen any time the two change data capture timelines overlap. When the transition is effected, the obsolete capture instance can be removed.
Note: The maximum number of capture instances that can be concurrently associated with a single source table is two.
>>if someone describe in detail what internal tasks 'sys.sp_cdc_enable_table' does (Like which new tables are created and what rows are inserted in CDC tables of the database)?
It enables change data capture for the specified source table in the current database. When a table is enabled for change data capture, a record of each data manipulation language (DML) operation applied to the table is written to the transaction log. The change data capture process retrieves this information from the log and writes it to change tables that are accessed by using a set of functions. For more details, please refer to https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-2017
Hope this could help you .