Jason Shaw
asked on
SQL Replication and Archive Process Help Needed
We have an AZURE SQL DB that is a replicated copy of data from our point of sales system vendor. We are starting to use that AZURE SQL DB replicated copy to run reporting. Our vendor has provided us with a full replication so that we have all historical data in the DB but need to turn on a 90 day filter which will then write over everything on our replicated DB. I created a copy of the current DB so that we do have all historical data. I need some assistance in configuring a process that would archive data every 90 days to the db copy so that we have all data for reporting/queries in one database.
Likely this won't work.
Consider this...
1) Replication runs, which replicates source database instance (vendor) to target instance (your side).
2) This will replicate all data in source database.
3) Now let's say you prune all data older than 90 days.
4) Next replication, all data will return.
Likely best to create a material view which pulls all data in last 90 days. This way your material view will always have correct data, without playing any games with sales vendor replicated data.
Consider this...
1) Replication runs, which replicates source database instance (vendor) to target instance (your side).
2) This will replicate all data in source database.
3) Now let's say you prune all data older than 90 days.
4) Next replication, all data will return.
Likely best to create a material view which pulls all data in last 90 days. This way your material view will always have correct data, without playing any games with sales vendor replicated data.
As I knew, Azure SQL Replication doesn't support sync specified data.
Instead you can create a view for 90days of historical data and your reports will use the data. Or you have to build your own process to extract 90 days of data from client's db and import them into the db copy.
Instead you can create a view for 90days of historical data and your reports will use the data. Or you have to build your own process to extract 90 days of data from client's db and import them into the db copy.
ASKER
Additional info....Vendor controls replication schedule and caps it at 90 days. I have attempted to setup SQL Sync in the following manner:
Vendor DB -> OurDB->ArchiveDB
VEndor DB replications to OurDB and at end of 90 day window, sync runs to ArchiveDB. The problem is I am running into multiple errors when syncing with most frequent being following:
"Column OpenOrder cannot be modified because it is either computed column or result of a union operator"
Any ideas?
Vendor DB -> OurDB->ArchiveDB
VEndor DB replications to OurDB and at end of 90 day window, sync runs to ArchiveDB. The problem is I am running into multiple errors when syncing with most frequent being following:
"Column OpenOrder cannot be modified because it is either computed column or result of a union operator"
Any ideas?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
ASKER