I'm implementing ingestion job from SQL Server 2016. For performance reasons we want to implement incremental loads. Few facts:

  1. Customer has read replica as a part of always on availability group used for analytical purpose.
  2. OLTP model doesn't have enough information to track changes (no columns like updated_at, is_deleted etc).
  3. Read replica is currently used to extract data to DW and this is always full load.

So far we have considered:

  1. Standard SQL Server CDC - we have descoped this, as this will have to be enabled on primary node and without performance tests implemented we don't understand impact of it.
  2. Create another SQL Server node with transactional replication outside of 'always on AG' and implement CDC / triggers on top of it. Potentially decommission existing read replica.

Right now we are aiming with point 2. Are there any other options worth to consider?

1 Answer 1


One option is to put an AG replica on the target server, to serve as a staging database, and then upsert from there to the DW.

  • Target DW is Synapse, however this concept may help in other areas. thanks
    – czajek
    Mar 29 at 7:00

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.