we want to replicate our Production SQL Server DB to a historical ("reporting") DB, AND purge oldest data from Production. (The Production DB will only have the latest data, and the historical DB all data.)
We have looked at SQL Data Sync for Azure, but we do not want to sync the purging of the Production DB (DELETEs). So we basically want to control that changes to data older than a specific date are not included in the sync. Does anyone know if that is poosible? (Or if there is an alternative to SQL Data Sync that can do this?)
Thanks!
SQL Data Sync is not recommended in this scenario as SQL Data Sync does not support conditional filtering. My suggestion is to use Azure Data Factory to do the incremental loading based on the last modified date, using methods like watermark, change tracking, and slices.