Sql-server – Is it possible to have change tracking ONLY on a replication server, not original

change-trackingsql servertransactional-replication

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.

Best Answer

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:

capture transactions record info on source system (EXTRACT process)
deliver to the target independent system (it can be SQL Server, or whatever supported by GoldenGate) - (PUMP process)
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.

Related Solutions

Sql-server – Change Tracking across several Tables

My plan for this, is to add a rowversion to each of the source tables, and display the max rowversion from the source tables.

This is making the assumption that rowversion is unique across the entire database and is not incremented in each table (I need to check if that assumption is correct).

SELECT 
 ft.[col1]
,ft.[col2]
,ft.[col3]
,ft.[col4]
,dt.dim_id
,dt.name
, (SELECT MAX(rv) 
   FROM (VALUES (ft.row_ver), (dt.row_ver)) AS value(rv)) AS max_row_ver
FROM [dbo].[myFirstTable] ft
INNER JOIN [dbo].[dim_table1] dt ON ft.[col5_fk] = dt.dim_id

Obviously this only gives you an identifier to use to track changes, you would still need to create a table to track it.

SQL Server – Troubleshooting Transactional Replication Issues

The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool.

The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name explicitly, SQL Server picks one for you, because ultimately a constraint must have a name. The names on the right are the ones chosen by SQL Server.

So, the two sets of constraints most likely are the same in both databases. It is only the DDL statements that differ, because yours does not assign constraint names.

You can assign your own names if you use the syntax shown in the query on the right. That is, instead of declaring a default value like this:

DEFAULT (default_value)

use the following syntax:

CONSTRAINT constraint_name DEFAULT (default_value)

That way the names will always be predictable and, when comparing the schemas, you will not run into this issue again.

The same applies to all types of constraints in SQL Server (defaults, primary keys, foreign keys, check constraints).

Best Answer

Related Solutions

Sql-server – Change Tracking across several Tables

SQL Server – Troubleshooting Transactional Replication Issues

Related Question