Archiving high-volume time-series data in SQL Server
For a suite of oil & gas measurement products, I designed an archiving process for SQL Server tables that never stop growing — safe to stop, safe to resume, and verified on every run.
- Role
- Systems Architect
- Company
- Critical Control Technologies
- Stack
- SQL Server
- When
- 2026
The challenge
What needed solving
Measurement and monitoring products write time-series data continuously. The busiest tables grow without limit, which slows queries and maintenance, and the database also has to stay highly available. Moving old data out had to be safe on live, high-volume tables.
What I did
My approach
- Batched copy-and-delete: Data moves to the archive in batches, so each step stays small and the live tables stay responsive.
- Watermark-resumable: A watermark records progress, so a run can stop at any point and pick up exactly where it left off.
- Automated verification: Each batch is verified before the source rows are removed.
- Run logging: Every run is logged, so it can be audited and troubleshot afterwards.
- High availability: Led the wider SQL Server infrastructure design: replication, Availability Groups, HA configuration, edition migration assessment and performance tuning.
Deliverables
The work behind the result
Archiving process
Batched, resumable copy-and-delete for high-volume time-series tables.
Verification and logging
Automated checks and a log for every run.
HA architecture
Replication and Availability Group design for the product suite.
Migration assessment
An assessment of a SQL Server edition migration.
Results
What changed
runs that can stop and restart safely
every batch checked before deletion
every run auditable afterwards