Tags: consolidation, modernization roadmap, virtualization
A major Health Department was running a data system that has been expanding over the years. Eventually, they ended up with a highly outdated and inefficient environment with thousands of Microsoft Access and SQL Server databases. Such an outdated system presents a number of risks such as performance, scalability, manageability, security issues and frequent database corruptions. This leads to revenue losses because of poor performance. Moreover, such environments are usually not cost-effective with increased operating expenses due to a great number of unnecessary licenses. So, our customer decided to modernize their system, and they turned to DB Best to analyze their complex system and build the most effective Access modernization roadmap.
Let’s see how we helped our customer to improve operational efficiency and performance with reduced ownership costs and minimized business risks.
Initial analysis
Usually, a modernization project consists of
- Data collection
- Running servers’ optimization options
- Considering cloud migration
- Building the financial model
- Health monitoring.
All these tasks could be greatly facilitated by the means of our specially designed tool called DBMSys. So, using DBMSys tool we started with collecting data from all customer’s servers to analyze it and make modernization offerings.
We discovered that the current SQL Server environment is fully virtualized and 54% of servers are running on an expired operating system. As for database versions, 74% of their databases are either outdated or on extended support, and 92% of data is stored in these outdated databases.
Moreover, we find out a number of performance issues and provided recommendations to eliminate them:
- for 23% over-utilized and 33% under-utilized servers in terms of CPU performance we recommended to add and remove an appropriate number of cores respectively.
- for 53% over-utilized and 30% under-utilized servers in terms of memory we recommended to add and remove an appropriate amount of memory.
Optimization strategies
Next, we considered virtualization and consolidation strategies within Access modernization roadmap focusing on total ownership cost of the future system:
- Optimize on-premises virtualization
- Azure IaaS with consolidation
- Azure IaaS 1:1 migration.
We found out that existing on-premises servers could be virtualized into a single host with several virtual machines. As a result, that would lead to a 25% reduction of licensing cores.
Another option is to migrate physical workloads to Azure cloud. We matched on-premises workloads to Azure virtual machines basing on collected data and benchmarks. In particular, we considered two Azure adoption strategies: moving on-premises servers to consolidated virtual machines or without utilizing consolidation. Consolidated Azure could give an impressive 33% licensing cores reduction, while non-consolidated would not show any licensing optimization compared to the original system.
Ultimately, migration to the consolidated Azure environment would lead not only to substantial licensing costs reduction, but also to saving 28% of hardware and operational costs.
Financial model
We ended up optimization roadmap by comparing the overall cost of three considered modernization scenarios. At first sight migration to consolidated Azure environments looks the most promising strategy due to the highest licensing cores reduction. However, it turned out that on-premises virtualization is the most profitable strategy that would allow reducing 40% of overall operational costs compared to the current state.
Full migration to Azure cloud is at least 61% more expensive than on-premises virtualization. Even with consolidated workloads, migration to Azure could give only 2% cost reduction.
However, it is worth considering a hybrid approach, with part of the workloads virtualized on-premises and another part consolidated on Azure. That way we can combine the benefits of cloud solution with reasonable expenses.
Building Microsoft Access modernization roadmap
To build Access modernization roadmap, we started with collecting data across from Access environment using a specially developed script. Collected data included:
- Usage patterns
- Duplicated databases
- Data retention policies
- Data types in databases
- Objects count
- Access control
- Metadata
- Database schema
- Personally identifiable information.
Basing on collected data we categorized the apps according to the business value. Then we developed the best modernization strategy for each category:
- Upgrading non-critical apps to the latest Access version. The cheapest and the easiest option in terms of required effort is to upgrade all existing outdated Access apps to the latest Microsoft Access version. However, Microsoft Access is not really a good choice for mission-critical systems as it performs poorly and will not scale with the growing business.
- Migrate mission-critical apps to SQL Server. The best option for mission-critical apps is to migrate data and business logic to SQL Server to achieve the best performance, high availability, and security. We assessed every step needed to complete the migration process. In particular, we ran the SQL Server Migration Assistant (SSMA) tool to assess manual effort needed to convert Access queries into SQL Server syntax. As for the user interface, we recommended redesigning user interface as a web application for the best user experience and performance.
- SQL Server as back-end + Access as front-end for medium-critical apps. The customer can continue using Microsoft Access as a front-end solution while SQL Server will serve as back-end. That would give an opportunity to save the expenses for applications that don’t need the highest UI performance.
Additional optimization options
In addition to categorized apps, we discovered a number of databases that were no longer in use and should be decommissioned. There were also very rarely used databases that could be successfully archived on Azure storage.
Besides, it is worth considering the migration of rarely used Access databases to Azure SQL Database Basic DTU service tier. Spending only $5 per month for a single database or $73 for an elastic pool with up to 100 databases they can have higher reliability, security, with lower maintenance costs than running Access locally. This analysis was not conducted because of the limited scope of this project. However, our customer can take advantage of this option in the future.
Ultimately, our Access modernization roadmap provides the highest performance for mission-critical apps by migrating them to SQL Server. At the same time, the customer could save money by leaving non-critical apps on Microsoft Access upgraded to the latest version.
Benefits
As a result, our customer obtained agile Microsoft Access modernization roadmap that allows building highly scalable and performant system. With our instructions, they could greatly optimize and upgrade their environment with an opportunity to take advantage of the Azure cloud. Moreover, our solution allows for a substantial reduction in operational costs.
