SSIS CDC: sys.sp_cdc_enable_db

Category: sql server manageability

Question

GeZhangNT on Wed, 21 Sep 2016 20:28:20


When uses the sp to enable CDC for a database, it will create 5 system tables in this database:

cdc.captured_columns

cdc.change_tables

cdc.ddl_history

cdc.index_columns

cdc.lsn_timemapping

The question is:

Can we put these 5 tables in another database?

Ex: enable CDC for database SSIS_CDC however I would like to put these 5 tables in another database SSIC_CDC_METADATA

Thank you in advance.

 

 





Replies

S_NO on Thu, 22 Sep 2016 03:22:59


It's not possible, as you know the CDC can be enabled database,tables - when you enabled the CDC for specific database with SP, the system tables will be created in to it under system table and those will be the point for querying the information but we shouldnt use the system table rather than they have sp's & these tables specific to its own.

as per BOL- please be aware some conditions

Restoring or Attaching a Database Enabled for Change Data Capture
--------------------------------------------------------------------------
SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:
If a database is restored to the same server with the same database name, change data capture remains enabled.
If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.
To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.
If a database is detached and attached to the same server or another server, change data capture remains enabled.
If a database is attached or restored with the KEEP_CDC option to any edition other than Enterprise, the operation is blocked because change data capture requires SQL Server Enterprise. Error message 932 is displayed:
SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

ref-https://msdn.microsoft.com/en-us/library/bb933994.aspx