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 scope of the project was to audit the existing systems to recommend and implement the following to have a stable and cost-effective platform for growth 

1. Modernise the data warehouse and ETL packages
2. Modify the ETL packages to work with the changes to the internal and external source systems.
3. Improve fault tolerance and disaster recovery processes.

 – Migrate web services currently running on the on a stand-alone web services virtual machine onto an Azure app service 

– Build a UAT environment with a copy of the databases with much of the data removed and running on lower spec virtual hardware 

– Migrate the SSIS packages onto a dedicated SSIS server running in the SSIS catalogue and using an “environment” to configure the packages. Create DevOps pipelines to compile and deploy the SSIS packages to the 

– Migrate the SQL databases to an Azure managed Instance using the wizard in Azure data studio. This provides high availability and cost savings. 

– Consolidate and replace the 4 FTP serves with one FTP service that is run as an Azure App Service and Azure Blob Storage 

– Configure SFTP services with Azure Blob Storage 

– Write infrastructure as code scripts using bicep to create Azure resources like virtual machine, app services for disaster recovery and ensure business continuity. 

– Standardise the source code and publish to  bitbucket repositories