0

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?

Improve this question
3
  • I'm not sure that I see how the proposed solution improves backup times. You'd have to back up both the archive table and the current table every time since the archive table was constantly changing and you'd duplicate all the data in the current table so you'd have more data to back up. And now you have two very closely related databases so you'd need to restore them to the same point in time to avoid data inconsistency. Plus, you can't have foreign keys that reference a parent table in a different database.
    – Justin Cave
    Nov 13, 2019 at 20:19
  • Can you post up the DDL of the big table and child table(s) and maybe a diagram showing how the relationships work?
    – HandyD
    Nov 13, 2019 at 22:48
  • @JustinCave The archive data will be in different database and the FKTable is in the same database to support FKeys
    – user1424196
    Nov 13, 2019 at 23:59

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.