Case Study
Clinical Solutions (NHS 111 Provider)
A national NHS emergency services provider operating a critical 24/7 platform supporting NHS 111 services across England. Their infrastructure comprised 150+ SQL Server instances across three data centers, managing a 2TB data warehouse growing 500MB daily, where ETL processing delays were preventing timely access to critical healthcare data.
Engagement Summary
The client engaged us to provide comprehensive 24/7 database support and urgently redesign their failing ETL processes that were missing critical reporting deadlines.
Over the course of the engagement, we reduced ETL runtime by 75%, ensured reliable daily reporting for healthcare operations, and provided robust support across their entire multi-data center infrastructure.
This case study highlights the core workstreams delivered:
- Critical ETL Process Redesign & Organisation
- 24/7 Enterprise Database Support & Infrastructure Management
- High Availability & Disaster Recovery Implementation
Workstream 1: Critical ETL Process Redesign & Organisation
Challenge / Scope
NHS Direct's main ETL process was failing to meet the critical 9am deadline required for operational reporting and healthcare decision-making. With a 2TB database growing 500MB daily, the existing 10-hour ETL runtime meant reports were unavailable when healthcare professionals needed them, potentially impacting patient care and service delivery.
What Was Delivered
- Complete ETL process redesign and reengineering
- Performance organisation of data warehouse processing
- Parallel processing implementation for large-scale data loads
- Query and index organisation for VLDB environment
- Process monitoring and alerting implementation
- Validation and testing across all environments
The Outcome
- Reduced ETL runtime from 10 hours to 2.5 hours (75% reduction)
- Successfully met 9am deadline requirement consistently
- Reliable, predictable daily reporting for healthcare operations
- Improved data freshness for operational decision-making
- Scalable solution supporting continued database growth
- Enhanced confidence in critical reporting systems
Workstream 2: 24/7 Enterprise Database Support & Infrastructure Management
Challenge / Scope
The organisation required round-the-clock support for 150+ SQL Server instances across three data centers, supporting critical NHS 111 emergency services. With OLTP systems handling live emergency calls and a 2TB+ VLDB data warehouse, any downtime or performance degradation could impact emergency healthcare delivery.
What Was Delivered
- 24/7 database administration and support across entire estate
- Management of clustered, physical, and virtual infrastructure
- OLTP system organisation and performance tuning
- VLDB data warehouse management (2TB+ growing 500MB daily)
- Support across production, UAT, load test, development, and DR environments
- Proactive monitoring and issue resolution
The Outcome
- Continuous 24/7 availability for critical NHS 111 services
- Reliable performance across all 150+ SQL Server instances
- Improved system stability and responsiveness
- Reduced operational incidents through proactive management
- Strong operational resilience across three data centers
- Consistent support across all environment types
Workstream 3: High Availability & Disaster Recovery Implementation
Challenge / Scope
Supporting critical NHS emergency services required robust disaster recovery capabilities and high availability architecture across all environments and data centers. With patient care depending on system availability, comprehensive DR capabilities were essential to ensure service continuity.
What Was Delivered
- Disaster recovery support across all environments
- High availability configuration and management
- DR testing and validation procedures
- Failover planning and execution
- Increased security measures across the estate
- Improved resilience through architectural enhancements
The Outcome
- Increased security and improved resilience across infrastructure
- Proven disaster recovery capabilities with tested procedures
- Enhanced high availability for critical healthcare systems
- Reduced risk of service disruption
- Improved confidence in business continuity
- Stronger operational resilience for emergency services
Long-Term Impact
Across all workstreams, the organisation achieved a reliable, high-performing, and resilient database infrastructure with critical ETL processes meeting healthcare deadlines, enabling NHS 111 services to operate confidently with timely access to operational data across 150+ database instances.
Technologies & Skills Used
- SQL Server (150+ instances, clustered, physical, virtual)
- ETL process design and organisation
- VLDB management (2TB+ data warehouse)
- OLTP systems organisation
- Performance tuning (queries, indexes, infrastructure)
- High Availability configuration
- Disaster Recovery (multi-data center, testing, failover)
- 24/7 support and operations
- Multi-environment management (production, UAT, load test, development, DR)
- Data warehouse design and organisation
- Monitoring and alerting
- Healthcare systems compliance and support
Interested in discussing any points raised in this case study?
Contact us today and speak to one of our team who will be happy to assist you with your enquiry.
View All Case Studies
Make an Enquiry