Data Archiving Process

Owned by Vincent Josset
Last updated: Oct 12, 2023
6 min read

To ensure best performance of the database, Softrip includes a tool to delete old data. This process deletes Reservation, Product, and Accounting data based on their relevant dates. This process removes old data, ensuring that the main production database only includes data that is relevant to daily operation of the system. All archived records are copied to an “Archive” database before they are deleted from the main production database.

Inputs

The archiving process is a stored procedure named proc_ArchiveAllData. This stored procedure should be run on the main production database.

  • @Server: The database server that hosts the archive database (e.g. MSSQL-ARCHIVE). Archived records will be copied to this database before they are deleted from the main production database.

  • @DBName: The database name for the archive database (e.g. SoftripNetArchive). Archived records will be copied to this database before they are deleted from the main production database.

  • @ResCutoffDate: The last departure date for which to keep reservation data.

  • @ProdCutoffDate: The last product date for which to keep Product data.

  • @MrkCutoffDate: The last date for which to keep CRM/Marketing data.

  • @AccCutoffDate: The last date for which to keep Accounting data.

Process

Input Validation

  1. Make sure there's no outstanding contingent liability in the period we last closed, for bookings in the archived period.

    1. Process stops here if we have outstanding contingent liability.

Copy Data to the Archive Database

Reservation Data

  1. Copy Credit Card information (ResCCards) for reservations that departed before the @ResCutoffDate to the archive database

  2. Copy reservation documents (ResDocs) for reservations that departed before the @ResCutoffDate to the archive database

  3. Copy reservation history (ResHhistory) for reservations that departed before the @ResCutoffDate to the archive database

  4. Copy reservation services/itinerary (ResItinerary) for reservations that departed before the @ResCutoffDate to the archive database

  5. Copy reservation memos (ResMemos) for reservations that departed before the @ResCutoffDate to the archive database

  6. Copy reservation queue records (ResQueues) for reservations that departed before the @ResCutoffDate to the archive database

  7. Copy reservation vouchers (ResVouchers) for reservations that departed before the @ResCutoffDate to the archive database

  8. Copy reservation services/itinerary (ResItinerary) for reservations that departed before the @ResCutoffDate to the archive database

Product Data

  1. Copy product upgrades (ProductUpgrades) for records that end before the @ProdCutoffDate to the archive database

  2. Copy pricing profiles (PricingProfiles) for records that end before the @ProdCutoffDate to the archive database

  3. Copy air allowance history (ProdAirAllowHistory) for records whose departure date is before the @ProdCutoffDate to the archive database

  4. Copy product design (ProdutDesign) for records that end before the @ProdCutoffDate to the archive database

  5. Copy product detail/services (ProductDetail) for records that end before the @ProdCutoffDate to the archive database

  6. Copy product detail exceptions (ProductDetailException) for records whose departure date is before the @ProdCutoffDate to the archive database

  7. Copy product events (ProductEvents) for records that end before the @ProdCutoffDate to the archive database

  8. Copy product guide assignments (ProductGuides) for records that end before the @ProdCutoffDate to the archive database

  9. Copy product history (ProductHistory) for records that end before the @ProdCutoffDate to the archive database

  10. Copy product prices (ProductPrices) for records that end before the @ProdCutoffDate to the archive database

  11. Copy product shop history (ProductShopHistory) for records whose departure is before the @ProdCutoffDate to the archive database

  12. Copy products (Product) for records that end before the @ProdCutoffDate to the archive database

  13. Copy inventory (Inventory) records whose Inventory Date (IDate) is before the @ProdCutoffDate to the archive database

  14. Copy services (SupplierServices) records that end before the @ProdCutoffDate to the archive database

  15. Copy rates (SupplierRates) records that end before the @ProdCutoffDate to the archive database

  16. Copy supplier promotions (SupplierPromo) records that end before the @ProdCutoffDate to the archive database

  17. Copy supplier promotion details (SupplierPromoDetail) records that end before the @ProdCutoffDate to the archive database

  18. Copy CMS data (CMSData) records whose active date range ends before the @ProdCutoffDate to the archive database

  19. Copy CMS items (CMSItem) records whose active date range ends before the @ProdCutoffDate to the archive database

  20. Copy CMS parent-child relationships (CmsParentChild) records whose CMS Items (or parent CMS Items) have an active date range ends before the @ProdCutoffDate to the archive database

CRM/Marketing Data

  1. Copy conversions (MrkConversion) records that occurred before the @MrkCutoffDate to the archive database

  2. Copy ad hits (MrkHitLog) records that occurred before the @MrkCutoffDate to the archive database

  3. Copy completed lead imports (MrkLeadImportDone) records that were imported before the @MrkCutoffDate to the archive database

Accounting Data

  1. Copy transaction detail (AccTransDetail) records whose transaction date is before the @AccCutoffDate to the archive database

  2. Copy transactions (AccTrans) records whose transaction date is before the @AccCutoffDate to the archive database

  3. Copy month end (AccMonthEnd) records whose transaction date is before the @AccCutoffDate to the archive database

Delete Archived Data

All records that were copied to the archive database are deleted from the main production database.

Reservation Data

  1. Delete Credit Card information (ResCCards) for reservations that departed before the @ResCutoffDate from the main production database

  2. Delete reservation documents (ResDocs) for reservations that departed before the @ResCutoffDate from the main production database

  3. Delete reservation history (ResHhistory) for reservations that departed before the @ResCutoffDate from the main production database

  4. Delete reservation services/itinerary (ResItinerary) for reservations that departed before the @ResCutoffDate from the main production database

  5. Delete reservation memos (ResMemos) for reservations that departed before the @ResCutoffDate from the main production database

  6. Delete reservation queue records (ResQueues) for reservations that departed before the @ResCutoffDate from the main production database

  7. Delete reservation vouchers (ResVouchers) for reservations that departed before the @ResCutoffDate from the main production database

  8. Delete reservation services/itinerary (ResItinerary) for reservations that departed before the @ResCutoffDate from the main production database

  9. Update ResGeneral records for archived reservations to mark them as “Archived” (Archived = 'Y')

Product Data

  1. Delete product upgrades (ProductUpgrades) for records that end before the @ProdCutoffDate from the main production database

  2. Delete pricing profiles (PricingProfiles) for records that end before the @ProdCutoffDate from the main production database

  3. Delete air allowance history (ProdAirAllowHistory) for records whose departure date is before the @ProdCutoffDate from the main production database

  4. Delete product design (ProdutDesign) for records that end before the @ProdCutoffDate from the main production database

  5. Delete product detail/services (ProductDetail) for records that end before the @ProdCutoffDate from the main production database

  6. Delete product detail exceptions (ProductDetailException) for records whose departure date is before the @ProdCutoffDate from the main production database

  7. Delete product events (ProductEvents) for records that end before the @ProdCutoffDate from the main production database

  8. Delete product guide assignments (ProductGuides) for records that end before the @ProdCutoffDate from the main production database

  9. Delete product history (ProductHistory) for records that end before the @ProdCutoffDate from the main production database

  10. Delete product prices (ProductPrices) for records that end before the @ProdCutoffDate from the main production database

  11. Delete product shop history (ProductShopHistory) for records whose departure is before the @ProdCutoffDate from the main production database

  12. Delete products (Product) for records that end before the @ProdCutoffDate from the main production database

  13. Delete inventory (Inventory) records whose Inventory Date (IDate) is before the @ProdCutoffDate from the main production database

  14. Delete services (SupplierServices) records that end before the @ProdCutoffDate from the main production database

  15. Delete rates (SupplierRates) records that end before the @ProdCutoffDate from the main production database

  16. Delete supplier promotions (SupplierPromo) records that end before the @ProdCutoffDate from the main production database

  17. Delete supplier promotion details (SupplierPromoDetail) records that end before the @ProdCutoffDate from the main production database

  18. Delete CMS data (CMSData) records whose active date range ends before the @ProdCutoffDate from the main production database

  19. Delete CMS items (CMSItem) records whose active date range ends before the @ProdCutoffDate from the main production database

  20. Delete CMS parent-child relationships (CmsParentChild) records whose CMS Items (or parent CMS Items) have an active date range ends before the @ProdCutoffDate from the main production database

CRM/Marketing Data

  1. Delete conversions (MrkConversion) records that occurred before the @MrkCutoffDate from the main production database

  2. Delete ad hits (MrkHitLog) records that occurred before the @MrkCutoffDate from the main production database

  3. Delete completed lead imports (MrkLeadImportDone) records that were imported before the @MrkCutoffDate from the main production database

Accounting Data

  1. Delete transaction detail (AccTransDetail) records whose transaction date is before the @AccCutoffDate from the main production database

  2. Delete transactions (AccTrans) records whose transaction date is before the @AccCutoffDate from the main production database

  3. Delete month end (AccMonthEnd) records whose transaction date is before the @AccCutoffDate from the main production database

Notes

  • The archive process can take a VERY long time, especially if it is run for a large date range (for example, running it for the first time with cutoff dates that include many years of data). While this process is running, your Softrip applications may run into timeout errors and other errors or performance issues.

  • Once the archive process has completed, we recommend that SQL maintenance tasks are run:

    • Back up and truncate the main production database’s SQL transaction log file

    • Rebuild all table indexes on the main production database