0

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.

We decided on the archival since the daily full backup is taking a long time, and so we are planning to keep the archival table in different drive.

We tried the below ways, and all are taking really long:

  • We can’t go for inserting latest data to another table and then rename the tables accordingly because of the foreign key constraints.

  • SSIS package to insert to archive table old data is taking ~6 days in batch insert

  • BCP is also taking ~7- days.

Please suggest any best idea/suggestions considering CDC and foreign keys.

Improve this question
6
  • 1
    If it's partitioned, why can't you switch out partitions you want to move?
    – Erik Darling
    Sep 10, 2019 at 17:25
  • Thank you Erik ! Even if we moved the partition to another drive, the daily full backup job will take the same long time. We want to minimize this time
    – user1424196
    Sep 10, 2019 at 17:42
  • when you say ssis and bcp takes days - how much data you are inserting ? Is this incremental load or full load everytime ?
    – Kin Shah
    Sep 10, 2019 at 17:48
  • 1
    If you move it to a read only file group, you could skip it during backups.
    – Erik Darling
    Sep 10, 2019 at 18:01
  • 1
    Also it sounds, generally, like you've let this database grow beyond the capabilities of your infrastructure, and the procedure you propose won't really change that.
    – David Browne - Microsoft
    Sep 10, 2019 at 18:36

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.