We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship
We have decided to archive the huge table but not the child tables(since they are not that big )
Reason : To improve the performance and to decrease the time taken to backup.
Can’t make it as partition and make the old files as read only.
Reason: no backup for the old data in case of any disaster/data loss
The approach we have tested is :
Copy the column(it’s an identity column) which has dependency on the child table to another newly created FKtable
Drop the FKeys on the huge table
Create and Divert the Fkeys on the newly created table FKtable (since we are not archiving the child tables, also we want to schedule the regular archival in future )
Start archiving the huge table in batches to another database.
Also update the SPs where ever insert to huge table is happening should also insert in FKTable(since we moved the FKeys to this table)
Also update the SPs where ever update to huge table is happening should also check in FKTable in case they are touching the old data.
Also update the SPs where ever select to huge table is happening should also select in FKTable in case they are touching the old data.
I'd be very grateful for any feedback. Am I on the right track or should I be considering a different approach?