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
2
votes
1answer
33 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
24 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
23 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
47 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
99 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
374 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
50 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
194 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
2k 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
57 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
134 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
294 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
115 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
242 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
336 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
42 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
234 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
270 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
100 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
28 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
134 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
919 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
99 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
134 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 ...
0
votes
0answers
486 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
369 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
542 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
2k 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
59 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
2k 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
858 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 ...
0
votes
1answer
372 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
535 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
214 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
439 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
5k 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 ...
1
vote
0answers
55 views

CDC and datamodel updates, best methodology?

We're about to start using CDC to track changes to a large number of tables in our db's. But since our datamodel is rather frequently updated, I'm wondering what is the best method for easily keeping ...
2
votes
0answers
137 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
0answers
98 views

Lookback Query utilizing Change Data Capture in SQL Server 2014? [duplicate]

I'm using SQL Server 2014 to maintain a small database and while looking for a way to keep a History of my Data I came upon the Change Data Capture Mechanism. My question now is if there is an easy ...
9
votes
1answer
1k views

How to tell if a backup file contains CDC?

Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine ...
1
vote
1answer
45 views

Change Data Capture in a services environment

Change Data Capture looks to be exactly what I need... Except. All of my database actions come from users that system accounts. We have WCF service that is called by an application. The WCF Service ...
1
vote
2answers
462 views

What is the impact on a running database when OS date is changed and set to past

My SAP back end db is db2. Need to understand the impact on this running database (db2 under HPUX) if I change the OS date and set it to a past date please. Thanks.
6
votes
1answer
895 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 ...
1
vote
1answer
286 views

Which parameters and metrics to look at when CDC based replication is causing high load?

I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while ...