Handling a DDL Change for SQL Server CDC Enabled Tables

what (question)? how can we ensure that any new columns added to a table where change data capture (cdc) is enabled will be synchronized to its target? where? any sql server database where cdc is turned on and your sql server source connector uses cdc as the incremental update configuration how (answer)? unfortunately, sql server does not automatically track certain ddl changes in the cdc log after cdc was enabled on the table this includes adding new columns to a table after cdc was enabled for the table dlh io aligns with the integrity of each database system, and will not pull a new column into the synchronization process if it does not belong to the tracked table this is very important so that your synchronization targets are in line with your database policies in order to update the cdc configuration to include new ddl changes (ex new columns typically done through a ddl command such as, alter table \<table name> add column \<column name>\<data type>), in the cdc log, you must create a new cdc instance to replace the existing cdc instance on the table any one table can have a max of two cdc instances assigned to the table it is best practice to only have one instance so, basically you will enable a new instance and disable the old one like most database systems sql server itself will almost always loose any previous pointer the cdc logic is using to track delta records used in the synchronization process therefore a re sync of the table will be required for consistency here are the conceptual steps you and your team will take when new columns and other ddl changes in a existing cdc tracked table are made conduct the ddl operation on the table in question (ex add column) now confirm that the column ddl operation was successful by viewing you table, you can use the sp help command for this or your ide exec sp help 'schema table name' now assert using the cdc reference query here that the cdc capture column log is ignoring your newly added column or ddl change, since you will not see it in the list because you ran a ddl operation on an already cdc enabled table select from cdc captured columns order by column name verify some information regarding the capture instance of the table, by running the following command, but specifically look for the following fields in the returned result set capture instance and start lsn exec sys sp cdc help change data capture @source schema = '\<schema name>', @source name = '\<table name>'; replace the placeholders with the name of your actual schema and table (source name attribute), for example, exec sys sp cdc help change data capture @source schema = 'person', @source name = 'address'; again the above just gives you details that will help you understand the cdc table instance you can also view what current events are in the cdc tracking table for the cdc enabled table by running a query such as select from cdc\<schema> \<table> ct if using the basic capture instance naming, for example, select from cdc person address ct disable the existing sql server cdc instance for the table being modified by executing the following command execute sys sp cdc disable table @source schema = n'\<schema name>', @source name = n'\<table name>', @capture instance = n'all'; go replace the placeholders with the name of your actual schema and table (source name attribute), for example, execute sys sp cdc disable table @source schema = n'person', @source name = n'address', @capture instance = n'all'; go once the command completes you can run the command from above, sys sp cdc help change data capture and notice that the result set is not available as this tracking logic has been removed create a new sql server cdc instance for the table that was modified by executing the following command exec sys sp cdc enable table @source schema = n'\<schema name>', @source name = n'\<table name>' go replace the placeholders with the name of your actual schema and table (source name attribute), for example, exec sys sp cdc enable table @source schema = n'person', @source name = n'address' go be sure here that the command completes also, please refer to your original cdc logic as there may have been other parameters such as @supports net changes that you wanted to incorporate without those parameters you will receive the defaults which work for 99% of all customers we believe run the command from the second step above to now verify that there is a result set for the new tracking capture instance in dlh io run a historical re sync on this table to complete the operation who? your sql server database administrator (dba) is best suited for the job use your support credits with us by contacting your engagement manager or using the support portal when? incorporate the instruction in the answer above anytime you seem to be missing a column in the target where your dba has confirmed that the table in question is using the cdc (not change tracking) method dlh io will pass alerts if the final synchronization to the target destination table fails because of this or the retrieval of data cannot be accomplished because of this microsoft sql server feature of their cdc configuration