Sql-server – Incremental extract strategy using SSIS and CDC

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic:

• maxtrans
• maxscans
• continuous
• pollinginterval

That doc has some information about these parameters, as does this white paper.

But really, CDC is not meant to be a real-time system, and you should use caution adjusting these parameters too much to make it so. Most people acknowledge that you need to wait to let CDC catch up (see here and here). If 5-10 seconds is not fast enough for you, you might be using the wrong technology.

As for the separate transaction question, this is something you could test, no?

I wasn't able to tune up the Oracle side of parameters, due to having limited access to the database instance, but the database load has seen improvements after stripping down the tables and columns that are being replicated.

1. Determine which tables are getting updated the most.

Try running the extractor for a few hours and let the THL grow. Then, you can count the number of changes logged for each table using a command like:

./bin/thl list | gawk -f thl_summary.awk | sort -n -r

Where thl_summary.awk is:

#!/bin/gawk
# Sum up rows from a `bin/thl list` output
BEGIN {
    OFS="\t"
}
match($0, /- TABLE = (.*)/, a) {
    table=a[1]
}
match($0, /- ROW# = (.*)/, a) {
    rows[table]++
}
END {
    for (k in rows) {
        print rows[k], k
    }
}

2. Replicate only those tables and columns that are necessary.

Looking at the list of tables, ask yourself if you can exclude any of them.

• Exclude CLOB columns.
  • They aren't really supported by synchronous CDC.
  • Even if you specify a subset of the columns in DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE, each and every change will be logged to the change table¹. But by excluding CLOB columns, we can lessen the load of costly writes and reads.
• Exclude all tables that are not really needed on the downstream database.
  • If necessary, add a fallback table with a subset of the columns that you really need, even if it means denormalizing a bit.
  • The way Tungsten configures CDC, a single update to a source row generates two rows in the corresponding change table². These extra updates can add up if you have a frequently updated table.

Once you've determined which tables and columns to replicate, you can specify them in a file named tungsten.tables:

aSubsetOfTheTable<TAB>column1,...,columnN
wholeTable

Then, you need to re-run the setupCDC.sh script with setupCDC.conf:

specific_tables=1

¹ https://community.oracle.com/thread/2288254

² Updated rows will generate a record with operation$='UU' ... and all the old values, and another record with operation$='UN' or 'UL' with new values. (source)