Recycling

Scrap Recycling & Consolidation

Data platform optimization for a large scrap collection & recycling system.

Client: US-based scrap collection, recycling & consolidation firm

The Problem

The client's scrap collection, recycling and consolidation product was self-built without a professional DBA — and started experiencing serious performance issues as data grew. A previous DBA couldn't find the root cause; our DBAs came on board and fixed it end-to-end, including recovering data from corrupted databases after frequent power outages.

Challenges

  • Severe performance issues from the database layer after data growth
  • No root-cause diagnosis from previous attempts
  • Frequent power outages causing database corruption
  • History of data loss with no reliable backups

Solution Design & Implementation

01

Immediate Performance Optimization

Tuned stored procedures and added indexes for significant gains — while recognizing that lasting scalability needed more.

02

Archival & Data Warehousing

An archival solution offloading history from OLTP, an analytics-optimized warehouse, and an archival database for audits and recovery.

03

ETL & Automation

Automated data movement between OLTP, archival and warehouse during non-operational hours, with self-adjusting archival policies.

04

Administration & Recovery

Scheduled maintenance and corruption monitoring — plus advanced retrieval techniques that recovered data even without backups.

Architecture

Sources

SaaS Application Databases

Collection & Sales Data

Ingestion & Integration

Automated Nightly ETL

OLTP Optimization & Warehouse

SQL Server — 3 Servers

Transformations

Tuned Stored Procedures

75+ New Indexes

Self-adjusting Archival

Data Zones

OLTP — 200+ DBs
Archival Store
Data Warehouse

Additional Stores

Audit & Recovery Store

Consumption

Analytics

Fast Report Generation

Orchestration & Monitoring

Scheduled Maintenance Jobs

Corruption Monitoring

Results

200+

Databases handled on a single SQL Server instance

6TB+

Data managed across 3 SQL servers

90%

Fewer data corruption issues after monitoring

60%

Reduction in SQL Server wait times

30%

Overall database server performance gain

75+

New indexes created; 20 unused dropped