Is it possible to have change tracking ONLY on a replication server, not original?

Question

I am working on a system where we are pulling records from a replication server and need to know when changes have occurred.

We would like to not have to touch the original server and only read from it, including not being allowed to enable change tracking. Can this be done?

If need be, we will simply pull all Record IDs every night and compare the two but it's certainly not the ideal solution considering we have tens of million of records.

I have seen this question:

• Is it possible to use Change Tracking on a Replication target?

The answer to it seems to be suggesting that the way to do this is to enable change tracking on the original server that is then replicated, which in my case is not an option.

Answer 1

if you can consider not only SQL Server options take a look at GoldenGate. We have a project to deliver content (ongoing changes) + change tracking from Oracle to Greenplum database. GoldenGate supports SQL Server as well, so idea and even parameter files for the processes are the same. On a high-level it looks like:

1. capture transactions record info on source system (EXTRACT process)
2. deliver to the target independent system (it can be SQL Server, or whatever supported by GoldenGate) - (PUMP process)
3. apply changes not as DML operations but as full records with additional attributes like "operation name", "commit timestamp" and else you can get from trail files generated on source (REPLICAT process). You can record only PK values or full row values into change tracking. It's just a matter of design.

EXTRACT and PUMP processes are quite simple. The most interesting here is REPLICAT config:

REPLICAT <PROCESS NAME>
INCLUDE ./dirprm/DBLOGIN.inc
INCLUDE ./dirprm/REPORT.inc
MACRO #map_history
PARAMS (#table)
BEGIN
IGNORETRUNCATES
MAP <SRC_SCHEMA>.#table, TARGET <TGT_SCHEMA>.#table,
  INSERTALLRECORDS,
  COLMAP (USEDEFAULTS,
    OPERATION = @GETENV('GGHEADER', 'OPTYPE'),
    CSN = @GETENV('TRANSACTION', 'CSN'),
    RSN = @COMPUTE(@COMPUTE(@NUMSTR(@GETENV('RECORD', 'FILESEQNO')) * 100000000000) + @NUMSTR(@GETENV('RECORD', 'FILERBA'))),
    CSN_TIMESTAMP = @GETENV('GGHEADER', 'COMMITTIMESTAMP')
  );
END;
INCLUDE ./dirprm/DML_REPLICAT.inc
#map_history(<TABLE1>)
#map_history(<TABLE2>)
...
#map_history(<TABLEN>)

This works well for hundreds of millions operations per day (including LOBs).

Hope it helps to get an idea of the one of the potential solution.