I'm implementing ingestion job from SQL Server 2016. For performance reasons we want to implement incremental loads. Few facts:
- Customer has read replica as a part of always on availability group used for analytical purpose.
- OLTP model doesn't have enough information to track changes (no columns like updated_at, is_deleted etc).
- Read replica is currently used to extract data to DW and this is always full load.
So far we have considered:
- 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.
- 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?