What's the best database archival strategy for incident management module with 5+ years of data?

QMS ETQ Reliance
, , , , , , , ,
1

Our incident management database has grown to over 850,000 records spanning 7 years, and we’re seeing significant performance degradation in searches and report generation. Query times that used to take 3-5 seconds are now hitting 45+ seconds, especially for trending reports.

We need to implement an archival strategy that meets our compliance requirements (7-year retention) while improving system performance. I’m curious what approaches others have taken. Some specific questions:

  • Have you implemented tiered storage (hot/warm/cold data)?
  • How do you handle audit trail preservation for archived records?
  • What’s your approach to automated archival scheduling?
  • How do you monitor database performance post-archival?
  • Any regulatory gotchas with moving data out of the primary database?

We’re on ETQ 2022 with SQL Server 2019. Looking for real-world experiences and lessons learned.

2

That dependency checking is a great point I hadn’t fully considered. How do you handle records that are old but still have active related items? Do you exclude them from archival or move everything together?

3

From a compliance perspective, the critical thing is maintaining the complete audit trail even after archival. We’re in pharma, so FDA 21 CFR Part 11 applies. Our approach was to keep archived records in a separate read-only database that’s still accessible through ETQ’s interface. This way, auditors can still pull historical records with full traceability. Make sure whatever solution you choose maintains electronic signatures and timestamps intact.

4

One thing to add: make sure your backup strategy accounts for the archived database too. We made the mistake of focusing all our backup resources on the primary database and then had a near-disaster when the archive database had corruption. Now both databases are on the same backup schedule with the same retention policies. Also consider that your archive database will keep growing, so plan storage capacity accordingly.

5

Based on implementing archival strategies across multiple ETQ 2022 deployments, here’s a comprehensive approach that addresses all your focus areas:

Tiered Archival Architecture: Implement a three-tier model that balances performance with accessibility:

  • Active Tier (Primary DB): Last 24 months of data - full read/write access, optimized indexes
  • Archive Tier (Secondary DB): 2-7 years of data - read-only access through ETQ interface, materialized views for reporting
  • Cold Storage Tier: 7+ years for extended compliance - compressed backup format, restored on-demand for audits

The key architectural decision is keeping the Archive Tier accessible through ETQ’s standard interface. This requires maintaining the same table schema and creating database links or synonyms so ETQ queries can span both databases transparently.

Compliance Retention Policies: Your 7-year requirement is common in regulated industries. Critical compliance considerations:

  • Maintain complete audit trail in archived records (all workflow history, approvals, electronic signatures)
  • Preserve original timestamps and user attribution
  • Ensure archived records remain tamper-evident (use read-only databases with checksums)
  • Document your archival procedure as part of your QMS documentation
  • Validate that archived records can be retrieved and displayed with full context during audits

For FDA/ISO compliance, the archived data must be as accessible as active data, just potentially slower to retrieve. Plan for auditors to request historical trending reports that span archived data.

Automated Archival Scheduling: Create a robust automated process:

  1. Monthly archival window (typically weekend/off-hours)
  2. Pre-archival validation: Check for open dependencies (linked CAPAs, active workflows, pending approvals)
  3. Archival execution: Move records meeting age + closure criteria
  4. Post-archival verification: Validate record counts, audit trail integrity, FK relationships
  5. Performance baseline: Run standard query set to measure improvement

Implement these safety checks:

-- Dependency validation before archival
SELECT i.Incident_ID, i.Status,
  COUNT(c.CAPA_ID) as Open_CAPAs
FROM Incidents i
LEFT JOIN CAPA c ON i.Incident_ID = c.Related_Incident_ID
  AND c.Status NOT IN ('Closed', 'Cancelled')
WHERE i.Created_Date < DATEADD(month, -24, GETDATE())
GROUP BY i.Incident_ID, i.Status
HAVING COUNT(c.CAPA_ID) > 0;

Database Performance Monitoring: Establish comprehensive monitoring:

  • Baseline query performance metrics before archival (capture top 20 slowest queries)
  • Set up SQL Server Extended Events to track query duration and resource usage
  • Monitor table sizes and index fragmentation monthly
  • Track archival database growth rate to predict storage needs
  • Create automated alerts for queries exceeding performance thresholds

Key metrics to track:

  • Average query response time for incident searches
  • Report generation time for standard compliance reports
  • Database size growth rate (should flatten after initial archival)
  • Index maintenance duration
  • Backup/restore times

Audit Trail Preservation: This is critical and often overlooked:

  • Archive entire record graphs, not just parent records (include all child tables: comments, attachments, workflow history, notifications)
  • Preserve the Audit_Log table entries related to archived records
  • Maintain user and lookup table data needed to interpret archived records
  • Test retrieval of archived records quarterly to ensure accessibility
  • Document the archival process itself in your audit trail

Implementation Lessons Learned:

  1. Start with read-only archive database - don’t try to build a full read/write tiered system initially
  2. Test archive retrieval extensively before going live - nothing worse than archived data you can’t actually access
  3. Plan for archive database to eventually need its own archival strategy (cold storage tier)
  4. Consider regulatory requirements for system validation - archival process changes may require revalidation
  5. Budget for increased storage - you’re not deleting data, just moving it
  6. Train users on how to access archived records (may require different search interface)

For your 850K records over 7 years, I’d estimate moving records older than 24 months to archive will reduce your primary database by 60-70%, which should bring query performance back to acceptable levels. The initial archival will take several hours, but subsequent monthly runs should complete in under 30 minutes.

6

We exclude them from archival until all dependencies are closed. Our rule is: an incident can only be archived if its status is Closed and all related CAPAs, changes, and investigations are also in closed/completed states. We run the archival check monthly, so records eventually get moved once everything is resolved. It’s a bit conservative, but it prevents data integrity issues.

7

We implemented a three-tier approach last year for our incident data: Active (last 18 months), Archive (18 months to 5 years), and Cold Storage (5+ years). The key was creating indexed views in the archive database so searches still perform reasonably well. Our query times dropped from 60+ seconds back down to under 10 seconds for most reports.

The tricky part was the automated scheduling - you need to be careful about archiving records that might still have open CAPAs or active investigations linked to them. We built dependency checks into our archival stored procedures to prevent orphaning related records.

8

For performance monitoring, we use SQL Server’s Query Store feature to track query performance before and after archival. This gives you concrete metrics on improvement. We also set up automated alerts if query times start creeping back up, which usually indicates it’s time for another archival cycle.

One unexpected benefit: after moving old data out, index rebuilds on the primary database became much faster, so maintenance windows shortened significantly.