Our company is seeking to Archive old data.
After two years, we place from source: OLTP FinanceDB into ArchiveDB. We want to archive by Year of Customer Order Date. (eg Current year is 2018, any 2016 or below)
Order dates are different from Etlcreatedate and Etlupdatedates. Only one table dbo.SalesOrder has Order date. The rest of the related (parent, child, bridge) tables, do not have Order date. This is a Inmon OLTP Relational database, so redundancy in order date will not occur.
Eg:
CustomerApplicationtable (shows customer information/name),
MktgTable (shows marketing material related to order),
Phonecall table (customer relation table),
FeeCommission table
These do not have order dates. How would I archive this? We have 100+ million rows?
I thought partition switching would not work unless every table has year column.
Should I add an archive partition indicator to each table and update, to find partition?
Should I utilize traditional etl, find all order dates with a join to SalesOrderdate table, and archive?