Archive Relational OLTP Database by Year

Question

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?

Answer 1

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.

To use partition switching to facilitate archivial by year, order-related tables don't necessarily need a new year column. Instead, you could add OrderDate to related tables and partition on OrderDate with yearly boundaries using a sliding window. All the tables would need the same partitioning OrderDate column and be aligned (indexes also partitioned).

This does introduce redundancy and would have significant implications on both the data model and OLTP query performance. The paritioning column needs to be part of the primary key, unique constraints, and indexes. Queries that don't specify the partitioning column (e.g. WHERE OrderID = @OrderID) would need to hit both partitions of the active tables.

My recommendation here would be to use a traditional ETL process for the archive rather than introduce significant changes that would only benefit the annual archive. If your business requirements allow, consider a daily or monthly archive to keep the process more manageable.