Questions tagged [change-data-capture]

Questions under this tag are expected to relate to Change Data Capture technologies available in many RDBMS server products. Change data capture is designed to capture insert, update, and delete activity applied to Server tables, and to make the details of the changes available in an easily consumed relational format.

Filter by
Sorted by
Tagged with
0
votes
1answer
27 views

Conditionally track CDC on a table in SQL Server 2016

I have CDC working on many tables in SQL Server 2016 without any issues. I now have a requirement to capture data changes (CDC) on a table when a specific criteria is met. Is it possible to capture ...
2
votes
0answers
10 views

How can I seed all the rows through CDC

We want to add a Kafka stream to publish changes to a table. The stream will be populated through the bin log and Atunity CDC. How do I seed the change data with the initial state of the table?
0
votes
0answers
23 views

Aurora Postgres CDC

I have setup a DMS task with CDC to continue to replicate data changes from DB2 to Postgres. Users now want to read and write to the Postgres database simultaneously. Can users write to the database ...
1
vote
1answer
23 views

What are drawbacks of setting REPLICA IDENTITY FULL in PostgreSQL table

I want to use the wal2json extension to capture the changes in the PostgreSQL database. I need to find the diff between old and new rows. Having searched the Internet for an answer, I realized that ...
0
votes
1answer
39 views

Get all changed tables from CDC

I am polling CDC tables to synchronize data between SQL Server and MongoDB. The approach I have taken is below: Enabled cdc for dbo.table1 and dbo.table2 which creates cdc.dbo_table1_CT and cdc.dbo....
1
vote
0answers
20 views

Replicate records at a specific CSN in Oracle Golden Gate

Is it possible to replicate records from the source table at a sepcific CSN? Here is my table with each rows with their ORA_ROWSCN ID Name ORA_ROWSCN 1 John 37828337 2 Mike 37828337 3 ...
0
votes
0answers
56 views

Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints. We decided on the archival since the ...
2
votes
1answer
57 views

Which are the downsides of not deleting the CDC records in change tables?

If I do something like this: USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 52494800; -- (100 years) GO The records in the change ...
0
votes
1answer
109 views

Change Data Capture in MySQL/PHP by writing to a MySQL table (rather than streaming to Kafka/Rabbit/etc)?

I have a small internal web app I'm developing with PHP and MySQL. I'm looking to sync client webpages to a server by requesting all changes since a certain date. I've found a number of solutions ...
0
votes
1answer
51 views

SQL Server CDC (Change Data Capture) Existing Data Seed

I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are ...
0
votes
0answers
89 views

Can MSSQL CDC be used to capture old committed transactions?

I can successfully capture change data from mssql CDC by issuing the query cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' ) Here, from_lsn is the ...
0
votes
1answer
145 views

Where does Attunity's CDC services write error data?

We use Attunity's CDC services to move data from Oracle to SQL Server. Using the Oracle CDC Designer snap-in tool, I can see the current status of the service and an error message if a problem has ...
2
votes
1answer
936 views

Monitoring CDC (Change Data Capture) performance

We currently have in place CDC, DW team is reading data, everyone happy. Then we needed to add couple new columns. Easy peasy following this answer... well, actually we had some hiccups but that's ...
1
vote
1answer
94 views

Error on CDC enabled Table

I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message. "...
1
vote
1answer
357 views

Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC

What is a preferable way to move ETL data into Data Warehouse? Create/Update date on the OLTP application, or Change Data Capture (CDC)? Given the two are allowed on a system and resourcing is not ...
1
vote
1answer
3k views

SQL Fastest Way to find Data Difference Between Two Tables

I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that ...
0
votes
1answer
69 views

Change Data Capture SSIS not picking changes

I am trying to implement change data capture in SQL Server 2012. I enabled it at database and table level. when i try to update the table, CDC_CT captures the changes. However when i am using SSIS CDC ...
1
vote
0answers
204 views

SQL Server 2016 CDC (change data capture) missed data issue

I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains ...
3
votes
0answers
427 views

Msg 22830 level 16 on sp_cdc_enable_db_internal - could not update metadata that indicates database is enabled for Change Data Capture

I'm attempting to create an MCVE for this recent question. SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database. ...
4
votes
2answers
159 views

SQL Server Change Data Capture order of records

Does anybody know if change data capture functions return always data in the order of occurence?. In a CDC system is quite important to get the data modifications in the order they have been generated....
1
vote
1answer
330 views

CDC Capture Instance or State to ensure referential integrity

I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database. As an example, I have INVENTORY_TRANSACTION in one ...
0
votes
1answer
414 views

CDC error - Error converting data type varchar to uniqueidentifier

I have CDC and Replication configured on an Always On cluster. I had to patch the SQL Server on Node 1 so I failed over the instance to node 2 and started patching node1. After about an hour one of ...
2
votes
1answer
44 views

How can I SELECT an 'instance' in PostgreSQL?

I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code: CREATE TRIGGER cdc_table ON db.table AS ...
3
votes
1answer
271 views

Change Data Capture Use Case?

I'm working on a custom application that needs to determine changes to a particular column, on particular rows in our database (MS SQL 2008R2). When the row is created a created & modify ...
2
votes
1answer
308 views

Cannot disable CDC - sp_cdc_disable_db_internal gives error 8152: 'String or binary data would be truncated'?

Trying to disable CDC and getting this error: Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 264 Could not update the metadata that indicates database … is not enabled ...
1
vote
1answer
129 views

How to make cdc skip the point that it is stuck at?

I have an issue where the transnational log has grown to its max size. Running fn_dblog I noticed that its complaining that a drop was done. It looks like cdc is stuck there. The description column in ...
1
vote
1answer
29 views

Monitoring changes on a View

Many DBMS implement views. However, do some of them implement change data capture on views (rather than actual tables), in a near real time fashion ?
1
vote
1answer
141 views

Data Warehouse how normalized for tracking metadata changes?

I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be ...
7
votes
1answer
3k views

CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4

We had an issue with CDC where the capture job failed and wouldn't restart itself. It was in a DEV environment so no harm but has anyone seen these errors or know what exactly went wrong with CDC? ...
0
votes
1answer
1k views

Restore Error: Could not upgrade the metadata

I am trying to restore a database from a Prod environment (11.0.6020.0) to a lower environment server (11.0.3128.0). I realize the lower has an older SP, we are working on correcting that now. The ...
2
votes
2answers
4k views

Change Data Capture vs trigger for logging changes

I am going to create history table which is populated by trigger (after insert, update, delete). As only 20% of the columns are going to be updated, I decided to log only the changed values - if the ...
2
votes
2answers
146 views

Use Netbackup 7.7 `WITH KEEP_CDC`

I'm using SQL Server Change Data Capture in a production database. To my surprise, I found that when restoring that database to a lower environment the CDC data is not kept—all the tables, functions, ...
0
votes
1answer
162 views

Empty CDC scans on highly active SQL Server

We have a SQL Server which has highly active databases which will have number of transactions in an hour. CDC is enabled on this database to carry forward the committed changes to another server. But ...
7
votes
1answer
5k views

SQL Server 2016: Temporal table performance vs. Triggers and CDC

It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended ...
1
vote
0answers
559 views

Capturing table changes with CDC and In Memory tables

We had a plan to capture table changes with CDC until we realized that it doesn't support In Memory tables. Is there another way (preferably as straightforward as CDC) to capture data changes (from ...
2
votes
0answers
425 views

SQL Server 2012 CDC not working on certain tables only

I am using SQL 2012 sp3. I have CDC working on many tables and DBs without issue. Last night a change was put into the system and 2 of my CDC'd tables were dropped and modified without properly ...
1
vote
0answers
26 views

Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different SQL instances?

I want to distribute Oracle data changes to the same table name in 2 different SQL instances, on 2 different SQL servers. Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different ...
1
vote
2answers
615 views

Find changes made in the table in sql server 2012

I am using a SQL Server.I have more 1000 tables in a database which i want to track for data changes.I have read about CDC(Change Data Capture) and will enable it on all the 1000 tables.It creates ...
2
votes
2answers
3k views

CDC vs replication vs anything else

In my product we need to create a solution for the reporting team to extract and process the data. We need to send them incremental data changes from our DB. I am evaluating various solutions. Looked ...
1
vote
0answers
61 views

Incremental load of full database

Please anyone suggest best approach for incremental load of all the tables in a database to a data warehouse , I have already developed SSIS package to do that using CDC, but enabling CDC for 100 ...
4
votes
2answers
3k views

CDC with AlwaysOn Availability Groups

So I just recently got to enable CDC on an always on AG. While many sources claimed enabling CDC on alwaysON AG is as easy as it is on any standalone server, turns out it really isn't. As MS says here:...
2
votes
2answers
2k views

Best way to capture historical changes between levels in a dimensional hierarchy

What is the cleanest way to capture changes between levels in a dimensional hierarchy? I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region ...
2
votes
1answer
962 views

Incremental extract strategy using SSIS and CDC

I'm looking for a strategy for extracting data from a SQL Server database that is configured for change data capture (CDC) using SSIS. Most of the documentation I have seen around using CDC and SSIS ...
3
votes
1answer
782 views

Automatically detect table name in MSSQL Server database using stored function

I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes. In the past, changes have been made manually, but I'd like to create a way to ...
0
votes
1answer
380 views

Can SQL Server Change Data capture (CDC) log rows to an external DB?

I have not used SQL Server CDC before, but to my understanding it works like this: business data changes in source DB, e.g. SQL Server CDC has been configured to track certain table/column changes, ...
2
votes
0answers
569 views

CDC capture job failing due to trigger execution error apparently

I'm using Change Data Capture(CDC) to track DML changes that are being performed on my SQL Server Tables. However, the capture job is failing post a DDL alteration, with the following error: Msg 3616,...
6
votes
1answer
2k views

CDC system tables missing from backup

I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012. There are CDC-related tables under the "System Tables" branch in Object Explorer: cdc.ddl_history cdc....
7
votes
1answer
232 views

Capturing change deltas when database replication or CDC breaks

The company I work for is in the process of implementing change data capture on a Microsoft BI stack for the purpose of capturing incremental changes that need to be loaded into the data warehouse. So ...
2
votes
0answers
468 views

Any way to ensure that Change Data Capture clean up process does not delete the change data that you haven't processed yet?

I'm using CDC feature to track changes that are being made, since the cleanup period is set to 3 days by default, I have to run jobs to archive this change data from the CDC system tables to my tables....
11
votes
3answers
6k views

Find the identity of the client firing a query in SQL Server without using triggers?

I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different ...