Does CDC service keep sync between source and target Databases ?

Question

Hello Everyone,

I am new to CDC and wondering before we start implementing this if CDC in sql server will keep my target DB intact with data from source systems in all cases.

for example - if i set up CDC service in sql server to read data from X no of tables from oracle and say this service is working fine and then one fine day this service is crashed for say 20 mins due to any reasons but source system (oracle which is a TX DB) is up and still having transactions getting logged in, in such case when my CDC service came back up, will it pick only new changes coming after those 20 mins or is it intelligent enough to compare both source and target and will update target DB from the time it lost the connection to source.

I would hope CDC is smart enough to do this but in my POC I don't find it working. am I missing something here or is there any configuration to do in CDC service ?

I am running sql server enterprise 2017.

Any help here is greatly appreciated.

Thanks

Answer 1

Hi @Saket Arora ,

Before a capture instance can be created for individual tables, a member of the sysadmin fixed server role must first enable the database for change data capture. This is done by running the stored procedure sys.sp_cdc_enable_db (Transact-SQL) in the database context. To determine if a database is already enabled, query the is_cdc_enabled column in the sys.databases catalog view.

When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database. The cdc schema contains the change data capture metadata tables and, after source tables are enabled for change data capture, the individual change tables serve as a repository for change data. The cdc schema also contains associated system functions used to query for change data.

Please refer to the following links:
1.Enable and Disable Change Data Capture (SQL Server)
2.Change Data Capture for auditing SQL Server

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Since are asking about Oracle to SQL Server, I assume you are talking about Oracle GoldenGate to SQL Server. This is a better question for the Oracle forum.

However, GoldenGate CDC reads the redo logs in Oracle. It knows what has been transferred and what has not, and will fix itself after an outage.