Our client has a data warehouse hosted in an Azure virtual machine, which is used to produce business-critical reports. The data warehouse is integrated with various internal and external data sources by approximately 300 SSIS packages.
The opportunity:
With an expanding data landscape, the client needed to modernise its data warehouse and supporting infrastructure to enhance stability, reduce operational costs, and strengthen disaster recovery. The objective was to create a robust, future-ready platform capable of supporting continued business growth.
Our approach:
Following a robust audit procedure, we implemented the following changes to the system:
- Migrated web services currently running on the on a stand-alone web services virtual machine onto an Azure app services
- Built a UAT environment with a copy of the databases with much of the data removed and running on lower spec virtual hardware
- Migrated the SSIS packages onto a dedicated SSIS server running in the SSIS catalogue and using an “environment” to configure the packages. We also created a DevOps pipelines to compile and deploy the SSIS packages
- Migrated the SQL databases to an Azure managed Instance using the wizard in Azure data studio, providing high availability and cost savings.
- Consolidated and replaced the 4 FTP serves with one FTP service that is run as an Azure App Service and Azure Blob Storage. We then configured SFTP services with Azure Blob Storage
- Wrote infrastructure as code scripts using bicep to create Azure resources like virtual machines, app services for disaster recovery, which ensured business continuity.
- Standardised the source code and publish to bitbucket repositories
The results:
Our client now operates a stable, scalable, and secure Azure-based data platform with significantly improved performance and fault tolerance. The modernised architecture reduced maintenance overheads, improved disaster recovery readiness, and created a sustainable, cost-effective foundation for future data initiatives.
