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
-1 votes
1 answer
46 views

how to read from cdc tables?

I wanted to try out the change data capture feature of sql server, but I cannot get it to work What I did -- enable cdc EXEC sys.sp_cdc_enable_db -- enable it on a table, for testing EXEC sys....
user avatar
  • 119
0 votes
1 answer
31 views

SQL Server modification timestamps

I'm implementing ingestion job from SQL Server 2016. For performance reasons we want to implement incremental loads. Few facts: Customer has read replica as a part of always on availability group ...
user avatar
  • 101
1 vote
1 answer
25 views

Create Audit Trail from SQL CDC

I have a few tables on which I have enabled CDC. As each row in CDC table contains all the columns regardless if they were changed or not, so it becomes difficult to figure out only the changed data. ...
user avatar
0 votes
0 answers
38 views

Postgres Citus with Outbound CDC (like Debezium)

I'm trying to set up a Debezium with Citus Database. However it seems that the standard postgresql plugin is not able to read the "streaming WAL". "connector.class": "io....
user avatar
  • 111
0 votes
0 answers
69 views

CDC Error Log Scan process failed in processing log records

I have an error in my CDC in SQL Server 2017: error_Number: 22859 error_severity: 16 error_Message: Log Scan process failed in processing log records. Refer to previous errors in the current session ...
user avatar
0 votes
1 answer
83 views

Do we need additional license to dump goldengate trail logs to kafka?

We have a Oracle (12c) GoldenGate license only (we do not have an oracle goldengate for big data). We need to transfer the gg trail logs to kafka then to hdfs. Do we need to buy the oracle goldengate ...
user avatar
0 votes
2 answers
87 views

Auditing via change data capture (CDC) for MSSQL

I want to ask the DBA community about best practices for auditing the MSSQL database. I set up CDC MSSQL and experience some difficulties with it. It's working all fine until I change the structure of ...
user avatar
0 votes
1 answer
40 views

insert and update triggers do not fire on replicated table

I have created a publication and a subscription on two servers with wal_level = logical in my postgresql.conf. Replication is functioning normally. My problem is to track changes in the replicated ...
user avatar
0 votes
0 answers
36 views

In SQL Server, how to determine when CDC Pub/Sub is finished

I have two SQL Servers that are linked through Pub/Sub CDC. The Pub Server has a CDC-enabled table. This table receives a nightly batch of data (CRUD via Informatica ETL). The Sub Server has an ...
user avatar
  • 175
0 votes
0 answers
52 views

SQL Server 2014 any way to suppress KB3030352?

I'm upgrading from SQL Server 2008 R2 to SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1. Compatibility mode is set to 120. Trace Flag 9481 is enabled. The problem still is present in lower ...
user avatar
0 votes
1 answer
41 views

Is the WAL data text format documented anywhere?

Looking around the Web, I can find plenty of information explaining how to set up WAL replication, and how to get the changes with pg_logical_slot_get_changes or pg_logical_slot_peek_changes. This ...
user avatar
0 votes
0 answers
25 views

CDC on secondary from different replication schemas - analysis

We have a mission critical database in Sql Server 2016 (Std) and we are not authorized to alter the schemas because of legal issues with the software provider. We need a way to have CDC in that ...
user avatar
  • 166
0 votes
0 answers
40 views

MSSQL CDC for views and stored procedures

is it possible to enable CDC for Views or Stored procedures? BR, Val
user avatar
1 vote
2 answers
70 views

Identify and delete specific data from Usertable_CT captured from a CDC enabled table

Scenario We recently performed a heavy update operation updating two columns on a 2 tb user table. The User table is CDC enabled, which caused Usertable_CT table to bulk up. The User table is a heavy ...
user avatar
2 votes
1 answer
246 views

SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a ...
user avatar
0 votes
3 answers
155 views

Replicating contained SQL databases to customer in near real-time

We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are ...
user avatar
0 votes
2 answers
41 views

T SQL, Get new customer added or deleted from historical table

I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or ...
user avatar
  • 3
1 vote
2 answers
228 views

CDC on Sql Server 2017 with HA cluster stopped working [closed]

I am currently using Sql Server 2017 (14.0.3294.2) running on Windows Server 2016 as an HA cluster, one primary with two secondaries. A few days ago, the primary was rebooted and I think the person ...
user avatar
  • 397
1 vote
2 answers
635 views

How does AWS DMS on-going replication works internally?

From the documentation it's mentioned that "DMS collects changes to the database logs using the database engine's native API" and replicates to target. But, I didn't see anywhere that, at ...
user avatar
  • 131
2 votes
2 answers
6k views

AWS DMS Task getting failed after completion with error as AlwaysOn BACKUP-ed data is not available

I have created DMS task for migrating data from one RDS MS SQL Server instance to another RDS MS SQL Server instance with "Full load + ongoing replication", and data copied to target ...
user avatar
  • 131
2 votes
2 answers
374 views

identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared [closed]

I am using oracle 11g express edition and I am getting the below error when trying to create a change set. Error report: ORA-06550: line 2, column 3: PLS-00201: identifier 'DBMS_CDC_PUBLISH....
user avatar
  • 121
0 votes
2 answers
101 views

Why am I getting error 35353 when enabling CDC on a table with a CCI?

I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command ...
user avatar
0 votes
0 answers
87 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 ...
user avatar
  • 1
0 votes
0 answers
87 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 ...
user avatar
0 votes
0 answers
59 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/...
user avatar
  • 101
3 votes
0 answers
80 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 ...
user avatar
  • 141
1 vote
2 answers
601 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 ...
user avatar
  • 13
1 vote
1 answer
243 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 ...
user avatar
  • 11
2 votes
0 answers
18 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?
user avatar
  • 121
1 vote
0 answers
103 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 ...
user avatar
  • 13
2 votes
1 answer
956 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 ...
user avatar
0 votes
1 answer
321 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....
user avatar
  • 101
0 votes
0 answers
405 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 ...
user avatar
2 votes
1 answer
229 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 ...
user avatar
0 votes
1 answer
361 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 ...
user avatar
  • 103
1 vote
1 answer
229 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 ...
user avatar
  • 11
0 votes
0 answers
184 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 ...
user avatar
0 votes
1 answer
252 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 ...
user avatar
  • 7,006
3 votes
1 answer
3k 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 ...
user avatar
  • 2,796
1 vote
1 answer
512 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. "...
user avatar
1 vote
1 answer
572 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 ...
user avatar
2 votes
1 answer
5k 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 ...
user avatar
2 votes
1 answer
121 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 ...
user avatar
1 vote
0 answers
415 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 ...
user avatar
3 votes
0 answers
1k 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. ...
user avatar
  • 67.1k
4 votes
2 answers
607 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....
user avatar
1 vote
1 answer
536 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 ...
user avatar
0 votes
1 answer
561 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 ...
user avatar
  • 1,678
3 votes
1 answer
55 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 ...
user avatar
3 votes
1 answer
395 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 ...
user avatar
  • 141