I have a requirement of a syncing set of tables (with data) to a different database from our production database. This is an Azure SQL DB.
I created replicated DB (using Azure Geo Replication) and it is a read-only DB. My plan was to enable SQL Server Change Tracking (CT) in replicated DB and query those changes from Change Tables, so that Production DB will not have any impact because of change tracking. But then I found out it is not possible to enable Change Tracking or even access Change Tables in the DB replica I created.
Then I saw Azure 'Sync to other databases' feature and tried it out with the replicated DB. But it is also not possible since this feature does not support syncing data from a read-only db.
1) What is the solution for this? I cannot afford using 'Sync to other databases' feature on my Production db, because it uses DB Triggers to track these changes. On the other hand, I cannot afford to enable CT in Production DB either.
2) Is there a way of enabling and tracking changes using CT from a replicated DB?
3) Or is there a way to use 'Sync to other databases' feature with a replicated DB?
The application trying to build is an analytics application. So I am trying to get data I want from a couple of other production DBs.
Thank you.
