Migrating On-Premises Databases and SSIS Workloads to Azure

Challenge Story

Our client was exploring the option of migrating their business operational databases to the Cloud. Their existing on-premises platform and several of their applications were scheduled to go out of support soon and they were uncertain about the best approach to take, given that staying on their existing platform would require a renewal of SQL server licenses. Instead, the city government wished to modernize their architecture and experience the many advantages Microsoft Azure Cloud offered, including scalability, elasticity, and cost savings, without the need to add additional data center hardware. The client was looking for a partner to help them discover and assess the cloud migration strategy and compatibility to move to the Microsoft Azure Cloud SQL Managed Instance (MI).

Why Zelusit?

After multiple rounds of technical conversations and presentations with potential vendors, the client selected Zelusit as their partner of choice to conduct the assessment. Our experts completed the assessment phase in one month and presented the findings and our proposed approach to the client. Based on the results of our assessment, Zelusit was then brought on board to execute the migration.

Solution Story

At the start of our engagement with the client, Zelusit assessed their existing IT landscape and conducted a series of stakeholder interviews to identify the main IT requirements. Our experts used the Microsoft Assessment and Planning (MAP) toolkit to collect and analyze the client’s existing data. We developed a future state architecture that met the client’s existing and future IT needs, and leveraged our standardized migration methodology to recommend a roadmap to rehost existing on-premise databases and SSIS workloads to Azure SQL MI and Azure Data Factory respectively. Our proposed Azure-based solution architecture (shown below) was accompanied by a detailed assessment and recommendation report.

Following the assessment, Zelusit executed the migration of SQL databases for on-prem to Azure SQL MI and enabled the client to run their ETL jobs on Azure cloud. In addition to database migration, the client also wanted our support in exploring the implementation of Power BI datasets to replace on-prem SQL Server Analysis Services cubes for PBI reporting. Our team executed the migration activity and Power BI Dataset implementation in an agile manner in just five weeks. Our process included the following steps:

Migration Design

Prior to the migration implementation, Zelusit’s experts undertook a comprehensive migration design for the client’s databases. This included compiling a list of database server configurations for the databases ready for migration and identifying impacted programs that write to these databases. We also identified the configuration of the SQL Server Master Data Services and their compatibility with Azure SQL MI. Our team used Data Migration Assistant to perform the database assessment. We identified the pre-requisites for both online migration and reporting and Power BI datasets, and a few of the pre-requisites, namely the firewall and Azure Storage, were created by Zelusit. All of this was documented in a Migration Design document. Finally, with everything in place, we identified a suitable migration window and communicated it to all stakeholders to ensure minimal disruption of operations.

Migration Implementation

In this phase, we ensured that the pre-requisites for migration (VPN, VNet, Firewall, network, etc.) were in place. After verification of SQL replication, including version of source and network pipeline, we created the Azure Data Migration instance and migration project and started online migration. The migration was monitored and reviewed, and the results were validated upon completion. Following that, we repointed the Master Data Services (MDS) web front-end to the new database on Azure. As a practice, our migration implementation plan includes failsafe options, so that we can easily pivot in case of unforeseen challenges.

ETL Migration

In this step, we identified the SSIS packages and versions and set up the on-prem integration runtime for Azure Data Factory. The SSIS packages were then deployed to Azure, following which they were validated. The client’s existing SSIS scheduled jobs were also migrated to Azure, and our team set up the requisite alerts and notifications.

Power BI Datasets Setup and Report Enablement

Our team also identified use cases for the Power BI datasets and gathered requirements from SSAS cubes. Next, we set up the Power BI datasets and the security and governance frameworks required to support them. We then confirmed that the connections to existing reports were valid to the native Power BI reports and created supporting documentation for the client.

Impact

• 80% annual savings
• PaaS solution architecture
• High performance
• Unlimited scalability
• Enhanced Power BI reporting

With the migration completed, the client was able to decommission their existing on-premise infrastructure and reap the advantages of a modern Azure-based cloud platform. By migrating to the Azure cloud, the client saved on SQL Enterprise license renewal fees and the cost of future data center enhancements, resulting in close to 80% of annual savings.

The Azure cloud provides Platform-as-a-Service (PaaS) capabilities, so the client no longer needs to maintain an in-house data center, databases, and an IT team to support the environment. They can now easily use non-production and production environments to set up new servers in the data center, which can be turned on and off on-demand, and can spin up Test and Development environments as needed using scripted ARM templates.

Moreover, unlike their earlier infrastructure, the Microsoft Azure Cloud SQL Managed Instance offers unlimited scalability, elasticity, high performance, and built-in disaster recovery capabilities. Zelusit also configured role-based access control to Power BI, as a replacement to SSAS, for the client’s team to visualize data and extract insights.

Exploring the possibility of migrating your on-premise architecture to the Azure Cloud? Speak to our experts today!