Access modernization roadmap for a highly-outdated environment

One of our clients, a major health company, was operating a data system. Over the years, it had expanded dramatically. However, the client had never developed a comprehensive modernization strategy for their product. As a result, they ended up with a highly outdated and inefficient environment overloaded with thousands of Microsoft Access and SQL Server databases.

The use of such an outdated system was associated with numerous performance, security, manageability, and scalability risks. In addition, there were frequent database corruptions. Therefore, the client lost significant revenue because of their product’s poor performance.

The client also had to pay for many unnecessary licenses on various components in their system. To improve their software efficiency and cut costs, they asked ABCloudz specialists to provide them with an Access modernization roadmap.

Read this article to learn how we helped the client modernize their system and realize improved performance with minimized business risks.

Preliminary analysis

First, we had to develop a detailed roadmap for implementing the modernization. The main points on which we focused were the following:

• Collecting data
• Running servers’ optimization options
• Considering cloud migration
• Creating the financial model
• Continuous system health monitoring.

We started work by collecting data from all the customer’s servers. This enabled us to see the big picture to make the most relevant modernization decisions.

During our data collection, we found out that the current SQL Server environment was fully virtualized and around 54% of servers were running on expired operating systems. We also determined that 74% of the client’s databases, storing 92% of all their data, were outdated.

Our specialists created a list of performance issues with recommended solutions. This list included the following:

Around 23% of the client’s servers were over-utilized and 33% of such servers were under-utilized in terms of CPU performance. We suggested adding and removing the appropriate number of cores respectively.
Around 53% of the client’s servers were over-utilized and 30% of such servers were under-utilized in terms of memory. We suggested adding and removing an appropriate amount of memory respectively.

Optimization strategies

For the next step, we developed virtualization and consolidation strategies to fit into the Access modernization roadmap. We focused on the total ownership cost of the future system. We considered the following:

• Optimization of on-premises virtualization
• Azure IaaS with consolidation
• Azure IaaS 1:1 migration

We also considered the possibility of virtualizing existing on-premises servers into a single host with several virtual machines. Our specialists estimated that it would cut licensing cores by 25%.
Another option identified by our specialists was migrating physical workloads to the Azure cloud. We came out with two basic strategies for Azure adoption. The first involved moving on-premises servers to consolidated virtual machines. This would deliver an impressive reduction of licensing cores by 33%. The second option was similar, but without using consolidation. We discovered it wouldn’t show any licensing optimization compared to the original system.

Our specialists also estimated that the migration to the consolidated Azure environment would allow us to substantially cut costs. This would also enable us to save 28% of hardware and operational costs.

Financial model

We compared the overall cost of three considered modernization scenarios to develop an optimization roadmap. At first, it seemed that migrating the workloads to a consolidated Azure environment would be the best solution. After all, it ensured the highest reduction of licensing cores.

Further research showed that on-premises virtualization would be even more profitable. This choice would allow us to reduce overall operational costs by 40% compared to the current state. After all, full migration to the Azure cloud would be at least 61% more expensive than on-premises virtualization. Even migration with consolidated workloads would only reduce our costs by 2%.

As a result, we decided to apply a hybrid approach. Part of the workloads had to be virtualized on-premises, while another part had to be consolidated on Azure. This would enable us to combine the benefits of a cloud solution while keeping the expenses reasonable.

Building the modernization roadmap

We collected data across the Access environment with a specially developed script. This was crucial for building an Access modernization roadmap. Our specialists focused on the following information:

• Metadata
• Usage patterns
• Duplicated databases
• Data types in databases
• Data retention policies
• Database schema
• Access control
• Personally identifiable information
• Objects count

We reviewed the collected data to categorize the apps according to their business value. As a result, we could develop the best modernization strategy for each category.

The essentials of this strategy were the following:

• Upgrading non-critical apps to the latest version of Microsoft Access. We decided to upgrade all existing outdated Access apps to the current version of Microsoft Access because it was a cost-effective solution. But we knew that this solution would not fit mission-critical systems. After all, Microsoft Access may perform poorly and lack the scalability required for growing businesses.
• Migrating mission-critical applications to SQL Server. When it comes to mission-critical apps, the best option is migrating data and business logic to SQL Server. This ensures the best performance, high availability, and top-notch security. We paid a lot of attention to the preliminary assessment to ensure the most efficient migration. Our specialists ran the SQL Server Migration Assistant (SSMA) to analyze the manual effort required for converting Access queries into SQL Server syntax. We also recommended the client redesign the user interface of a web app to ensure the best user experience.
• Implementing SQL Server backend and Access frontend for medium-critical apps. The customer could keep using Microsoft Access as their frontend solution. Meanwhile, SQL Server would serve as their system’s backend. This helped the client avoid overspending on apps that don’t require the highest UI performance.

The customer considered this strategy and decided to implement our roadmap in a slightly reduced version. The customer’s financial capabilities and their budget for app modernization was limited, so they pursued the most cost-effective solution. The client left all business logic on the application side. As a result, the customer asked our team to implement the following list of modernization tasks:

• Migrating mission-critical databases to SQL Server. This step included a wide range of actions such as, preparation of the structure for a new database on SQL Server, data migration itself, merging of archive data with operational data, etc. Our team provided the customer with a script that performs all the action in fully automatic mode. The main tool used by the script was SSMA.
• Upgrading non-critical and medium-critical apps to the latest version of Microsoft Access and switching them to the SQL Server backend. All those applications were switched and adopted to the new data sources on SQL Server. We closely cooperated with the client’s team to test the applications for compliance with business logic and performance requirements. And we prepared a set of automation scripts that allow the customer’s IT department to easily switch their apps between different environments (dev/stage/prod).

Value delivered

With our help, the customer obtained an agile Microsoft Access modernization roadmap. We participated in all the modernization processes, such as data migration tasks, app optimization and adaptation, and testing. ABCloudz specialists cooperated and consulted with the client’s team every step of the way. It helped the customer build highly scalable system with excellent performance. They can now follow our instructions to greatly optimize and upgrade their environment and embrace the essential benefits of the Azure cloud. Finally, our solution helped the client achieve better system efficiency while significantly cutting their operational costs.

Embrace our expertise

Do you want to modernize your software infrastructure? Just like the client in this case study, you can rely on ABCloudz and our tried and trusted experts in software migration and modernization. We will help you implement your most ambitious goals and cope with the most painful challenges. Apply for a consultation or contact us to discuss your needs and start leveraging software modernization.