Cloud Migration
Data Engineering
2024
7 min read
SQL Server Data Warehouse to Azure Synapse: 10TB Migration Case Study
Industry
Retail (500+ Stores)
Region
USA
Data Volume
10TB · 2,000 Tables
Platform
Azure Synapse Analytics
A large US retail chain with 500+ stores was running a 10TB on-premises SQL Server data warehouse that had become a bottleneck for analytics and reporting. Leadership made the decision to execute a full SQL Server data warehouse to Azure Synapse Analytics migration as part of a digital transformation programme. Numlytics managed the complete data warehouse cloud migration consulting engagement - 2,000 tables, 1,000 views, 500 stored procedures, minimal downtime, full BI tool continuity.
The Challenge: A 10TB Migration at Enterprise Scale
The scale of this migrate SQL DW to Azure engagement was exceptional. Every stage of the process carried compounding risk - data quality, system integrations, user disruption, and regulatory compliance all had to be maintained throughout the entire migration window.
-
Scale: 2,000 tables, 1,000 views, 500 stored procedures, and 10TB of data requiring methodical phasing across many weeks
-
Deep integrations: ERP, CRM, POS systems, and web services all feeding the warehouse - every dependency had to be identified and maintained
-
Extensive customisations: Partitioning, indexing, compression, and encryption configurations required careful recreation in Azure Synapse
-
Minimal downtime requirement: 500 stores could not experience significant disruption a hard constraint that shaped the entire migration plan
The Numlytics Solution:
Numlytics structured this data warehouse modernisation engagement into five gated phases, treating minimal business disruption as the primary constraint around which all technical decisions were made.
-
Cloud Readiness Assessment
Numlytics conducted a comprehensive assessment of the on-premises SQL Server warehouse identifying all dependencies, compatibility issues with Azure Synapse, and migration risks before a single table was touched. Critical for any Azure Synapse Analytics migration of this scale.
-
Data Preparation & Schema Standardisation
Data sources validated and cleansed. Warehouse schemas simplified and standardised using SQL Server Management Studio and Azure Data Studio, then validated for Azure Synapse compatibility.
-
ADF Migration Pipeline
Data loaded to Azure Synapse via Azure Data Factory and Azure Blob Storage. ADF pipelines managed the full ETL process with data quality checks at each stage, the backbone of the migration of SQL DW to Azure execution.
-
BI Tool Reconfiguration
Power BI, Tableau, and Excel connections reconfigured to Azure Synapse via Azure Active Directory and Azure Analysis Services. All reports and dashboards were validated against the original SQL Server output before cutover.
-
Phased Go-Live
Staged rollout by region - each batch of stores was migrated and monitored before proceeding to the next. All 500 stores live with improved query performance and no significant downtime.
The Results
10TB
Data Migrated
Complete SQL Server data warehouse moved to Azure Synapse Analytics.
↑ Speed
Query Performance
Significantly faster analytics and reporting post-migration to Synapse.
↓ Cost
Infrastructure Savings
On-premises hardware upgrades and maintenance costs eliminated.
500+
Stores Live
All US locations operational with full BI continuity through migration.