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.
133
questions
1
vote
1
answer
21
views
CDC Error - NULL Source Schema and Source Table - Error msg on both enable and disable table
Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error:
Could not delete change table entries made obsolete ...
- 11
0
votes
0
answers
8
views
SQL Server 2019 sys.sp_cdc_cleanup_change_table procedure not working
Although CDC works and all transactions are logged, we cannot clear the respective CDC table using the builtin procedure sys.sp_cdc_cleanup_change_table which should remove all the transaction except ...
Shadman Saif Anonno
0
votes
0
answers
20
views
CDC on postgreSQL to Redshift
I am trying to implement CDC on PostgreSQL to Redshift and looking for some approaches. I had a look at Debezium but it looks like it's not ideal for my requirement. I want to read all delta changes ...
- 1
1
vote
0
answers
34
views
DR - Missing binlogs in Maxwell
We have a standard db.r6g.2xlarge RDS MySQL instance running in production with 2 replicas. Maxwell CDC is setup to publish binlogs to a Kafka topic.
From a RPO point of view: in the worst case (say ...
- 111
1
vote
1
answer
68
views
Oracle query taking a very long time to execute
I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical ...
- 67
2
votes
1
answer
138
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41
0
votes
0
answers
45
views
Is there a way of identifying transaction boundaries from MongoDB ChangeStream?
The MongoDB documentation (https://www.mongodb.com/docs/manual/reference/change-events/) specifies that for a change event, the transaction associated with that event can be uniquely identified by the ...
0
votes
0
answers
114
views
CDC Primary Key error
We are using CDC to feed our application. However sometimes, out of the blue, the following error occurs:
Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'.
Cannot insert duplicate ...
- 1,795
1
vote
1
answer
412
views
SQL Server DB Log keeps growing / CDC enabled database
I've enabled change data capture on a SQL Server database.
The database is in full recovery mode.
The LOG backups of the database are being done hourly.
The capture jobs was running without any issues ...
- 243
2
votes
1
answer
76
views
Merging data from identical tables from multiple databases into one larger table. N-1 table replication
There are N databases with identical table structures in a multitenant, multi-database system. The desire is to replicate a table(s) from these databases into a single larger table in a OLAP database, ...
- 453
0
votes
1
answer
68
views
Can we keep change-tracking on a replicated table going after changing the distribution database?
We have an MSSQL replicated table which has change-tracking set up on the subscribed table.
The change tracking is used to send changes into Snowflake.
However - now we need to reconfigure replication ...
- 133
0
votes
0
answers
36
views
Resuming incremental SSIS CDC sync with big CDC "backlog"
We're using an SSIS package which syncs changes from one (main) database to another (reporting) using CDC. The package runs periodically syncing the data in small increments.
After having an issue ...
- 119
0
votes
1
answer
231
views
sp_rename column and CDC
I have a table in a SQL Server 2019 instance which is being CDC tracked.
However when I want to rename a column it failes with the error:
Cannot alter column 'Columnn2' because it is 'REPLICATED.
I ...
- 1,795
0
votes
1
answer
59
views
postgres 11.3 checksum validation post migration
I am in the process of migrating our AWS RDS postgresql from one account to another with zero downtime. I am going with pg_dump/pg_restore + AWS CDC for ongoing replication.
I am looking for a way to ...
- 61
2
votes
0
answers
164
views
Sensor data from Postgres to Redis
I have a Postgres database with a table that is updated with new data every 5 minutes from a weather station.
Now, as soon as the table is updated with a new record from the sensor, I would like this ...
- 21
1
vote
1
answer
225
views
CDC - I cannot disable that on the second instance
I have change data capture enabled for most of the tables in one of my databases. Database is hosted on SQL Server instance with version 2016.
I have some testing to be done, so I wanted to restore ...
- 147
-1
votes
1
answer
188
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....
- 129
0
votes
1
answer
92
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 ...
- 101
1
vote
1
answer
149
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. ...
- 21
0
votes
1
answer
227
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 ...
- 3
0
votes
2
answers
557
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 ...
- 27
0
votes
1
answer
388
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 ...
- 103
0
votes
0
answers
63
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 ...
0
votes
1
answer
120
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 ...
- 212
0
votes
0
answers
137
views
MSSQL CDC for views and stored procedures
is it possible to enable CDC for Views or Stored procedures?
BR,
Val
- 27
1
vote
2
answers
411
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 ...
- 11
2
votes
1
answer
586
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 ...
- 349
0
votes
3
answers
385
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 ...
- 101
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 ...
- 3
1
vote
2
answers
483
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 ...
- 417
1
vote
2
answers
1k
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 ...
- 141
3
votes
3
answers
14k
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 ...
- 141
2
votes
2
answers
690
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....
- 131
0
votes
2
answers
238
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 ...
- 21
0
votes
0
answers
179
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
...
- 1
3
votes
0
answers
209
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 ...
- 141
1
vote
2
answers
943
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 ...
- 13
1
vote
1
answer
407
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 ...
- 11
2
votes
0
answers
33
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?
- 121
1
vote
0
answers
133
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 ...
- 13
2
votes
1
answer
2k
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
1
answer
660
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....
- 101
0
votes
0
answers
529
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 ...
- 89
2
votes
1
answer
375
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 ...
- 217
0
votes
1
answer
614
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 ...
- 103
2
votes
1
answer
294
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 ...
- 21
0
votes
0
answers
238
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 ...
- 143
0
votes
1
answer
285
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 ...
- 7,071
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 ...
- 2,816
1
vote
1
answer
833
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.
"...
- 147