1

My colleagues and I are working on a solution revolving around change tracking…

Our goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system.

Our database is a subscriber (transactional replication). Numerous publishers are pushing transactions into numerous tables.

During our testing, we observed that the entries recorded by change tracking has records grouped into ‘chunks’. I.E. There are many records with the same value of SYS_CHANGE_VERSION.

This is based on the query of select * from changetable(CHANGES my_table, 0)

Observation: When we execute the above query, some of the rows are ‘interleaved’.

For example, we may have 50 row with SYS_CHANGE_VERSION = 100, 1 row with SYS_CHANGE_VERSION = 101 and then more rows with SYS_CHANGE_VERSION = 100.

Our question:

When rows are inserted into the change tracking table/mechanism, can rows be inserted with a lower value of SYS_CHANGE_VERSION than the current maximum value?

We are trying to determine if all we need to do is have a sort order on SYS_CHANGE_VERSION and simple ignore records for the current max value of SYS_CHANGE_VERSION.

Furthermore, once data is read from the change tracking table, can subsequent reads contain entries which have the same ‘maximum’ SYS_CHANGE_VERSION which was read previously.

We are worried about whether or not we can continue/resume the process of pushing transactions to the non-sql system.

As an alternative, we have considered using the replication stored procedures on my subscriber to push changes to my non-SQL system.

Any answers greatly appreciated.

  • Did you ever resolve this? I am looking at doing the same thing to push changes into a queue (Kafka) – Erick T Jul 23 '16 at 21:01
  • We went with another approach. We developed our own mechanism which does not use the change tracking functionality. – Angelo Carzis Jul 24 '16 at 21:34
  • Did you run into additional issues with change tracking? I'm curious as to why you went with another approach (your problem sounds almost identical to mine) – Erick T Jul 27 '16 at 21:59
  • We developed our own mechanism to have more control over the process. – Angelo Carzis Jul 30 '16 at 13:23
1

My understanding of Change Tracking is that it is per database transaction. This means that all changes that are part of the same transaction get the same SYS_CHANGE_VERSION.

I also understood that the SYS_CHANGE_VERSION is set during the commit of the transaction, so numbers are a) always ascending and b) may be interleaved. You should always process the records in transaction completion order.

See Obtaining Changes by Using the Change Tracking Functions for confirmation that VERSION is ever increasing.

  • Toby, Thank-you for the reply. Let me further expand on my previous post. – Angelo Carzis Dec 10 '15 at 15:15
  • Consider this scenario: I perform the following: select * from changetable(CHANGES mytable, 500) as change_columns. Let us say the query returns only 10 records with SYS_CHANGE_VERSION 501. If I perform the same select statement sometime later, would there be a change in the number of records for SYS_CHANGE_VERSION 501 - my reasoning that replication may still be inserting the records assigned to SYS_CHANGE_VERSION 501. – Angelo Carzis Dec 10 '15 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.