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
0answers
12 views

Missing records in cdc capture instance - MSSQL Server

We have a Source table (MSSQL Server database) and cdc is enabled on it. Most of the time cdc captures all changes that happened in Source table correctly, but time to time changed records are missing ...
0
votes
0answers
29 views

SQL Server Availability Groups and CDC

I have a simple SQL Server 2014 Availability Group, one database on a two-node cluster. We need to use CDC to move data to another environment, in my case AWS. This is a super-critical OLTP very large ...
0
votes
0answers
18 views

PostgreSQL 9.2 CDC implementation

I need to implement Change Data Capture(CDC) for PostgreSQL 9.2. I planned to use Debezium for this purpose but looks like it is only supports PostgreSQL 9.4+ https://debezium.io/documentation/...
1
vote
1answer
119 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
1answer
184 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 ...
2
votes
0answers
9 views

Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?

Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be ...
1
vote
2answers
64 views

SQL Server 2019 standard edition CDC with SSIS standard edition

The primary question is: Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise ...
1
vote
1answer
86 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 ...
0
votes
0answers
14 views

SQL Server CDC: Can/should there be more than one variable value in the cdc_states table

Question: Should there be multiple CDC state values (i.e., rows) in the cdc_state table when there are CDC packages running that are dependent on the result of other CDC packages? None of the ...
0
votes
1answer
456 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 ...
3
votes
1answer
889 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 ...
6
votes
1answer
1k views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
4
votes
2answers
3k views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
2
votes
1answer
3k views

In Change Data Capture (CDC), how to configure retention with success?

We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar). When we first configured it (with default options), it worked ...
0
votes
0answers
14 views

SQL Server Bi-direction replication with different schemas

I have a requirement when upgrade legacy system, the legacy database schema is quite different from the new database schema, we need to change user back and forth to use the system since the new one ...
0
votes
0answers
106 views

CDC capture Job Step-2 is not running when enabling CDC on table in SQL Server and Change table is empty

My Database has CDC enabled and the table i want to track changes using the below T-SQL, EXEC sys.sp_cdc_enable_db; GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'...
7
votes
5answers
15k views

Does MySQL have a version of Change Data Capture?

We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until ...
1
vote
1answer
208 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. "...
0
votes
0answers
46 views

Database mirroring with change history and least performance impact

I have a production DB which I need to effectively mirror and in addition stream changes to applications for their own purposes. (using something like AWS DMS) The catch is that the source database ...
1
vote
1answer
60 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
16 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?
1
vote
0answers
66 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
195 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
184 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 ...
0
votes
1answer
92 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....
0
votes
0answers
39 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
159 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
79 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
195 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 ...
2
votes
1answer
170 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 ...
0
votes
0answers
120 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 ...
27
votes
4answers
16k views

When to use CDC to track history?

SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table. Through the use of special table value functions (TVF) ...
2
votes
1answer
2k 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 ...
2
votes
0answers
140 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
1
vote
1answer
414 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 ...
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:...
1
vote
1answer
4k 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 ...
1
vote
0answers
276 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
723 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
264 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
410 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 ...
7
votes
2answers
2k views

What tables are affected by Change Data Capture

We're looking at using Change Data Capture in SQL 2012. We're trying to work out if there is an easy way to find out what tables have been changed by a given lsn. Is there an inbuilt function, or ...
2
votes
1answer
399 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 ...
2
votes
1answer
47 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 ...
7
votes
1answer
6k 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 ...
3
votes
1answer
316 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 ...
3
votes
1answer
527 views

Change Data Capture not running when Mirror is Suspended

We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are ...
1
vote
2answers
706 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 ...
7
votes
1answer
4k 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? ...