Case Study: Major Fast Food Retailer Performance Tuning and Database Migration to Azure
Project Overview
This customer is one of the largest fast-food restaurant franchises in the world by the number of locations, with over 30,000 restaurants in more than 100 countries. The company has made it a top priority to drive revenue per location by launching innovative digital experiences and enabling restaurant operators with state-of-the-art technology.
As part of these initiatives, the company was attempting a major update to their mission-critical Menu Management software used in all stores globally but was experiencing poor performance and frequent timeouts while moving the related Windows Servers, apps, and databases to Azure SQL and Azure App Service, despite using significantly over-provisioned resources. The primary system involved a new ASP.NET application based on .NET Core 2.0 and Entity Framework 2.0, which was to run in Azure and utilize Azure SQL. Despite positive performance tests against an on-premises SQL Server database, the application was performing at an unacceptable level in early tests against Azure SQL. The issues with the application and database required immediate remedy.
Atmosera was brought in to examine both the application and the Azure SQL server environment to identify the source of the performance issues. The goal was to debug, optimize, and improve the performance ahead of the launch, as well as to move the application itself along with the on-premises SQL Server database to Azure PaaS. Once this was complete, Atmosera was to work with the FWH team to verify the results. Atmosera led the effort leveraging our world-class debugging team to tackle the remediation aspects of the project and using best practices for the app and database migration aligned with the discipline of the Microsoft Cloud Adoption Framework (CAF).
NEED AND IMPACT
The customer wanted to enable franchisees and restaurant operators to drive their own localized pricing campaigns. In order to do this, restaurant operators needed to be able to quickly update their menus and populate the latest menu simultaneously across all ordering channels. Reasons for a menu update can be manifold: the restaurant runs out of a food item, a restaurant wants to drop the price on a food item because of a local college football game, etc. Prior to the launch of the new system, updating and deploying a menu could take a full day, especially when thousands of restaurants were publishing simultaneously. For example, this bottleneck would be hit whenever a new food item was being launched nationwide, or a nationwide campaign was triggered.
Details
Atmosera focused the initial phase of the effort on debugging and optimizing the performance of the application and related databases. Much of the refactoring involved optimizing stored procedures to make the indexing strategy more efficient, as well as implementing needed changes with Queuing in the application.
For the database migration, the databases were moved from on-premises SQL Servers into Azure SQL. The Database Migration Assistant (DMA) tool was used after an initial earlier attempt using BacPac files showed poor efficiency. Visual Studio and Visual Studio Code, along with their integrated debugging tools, were used as the core dev and debugging platform. The applications were migrated to Azure App Service as a PaaS solution. The charter from the company was a basic two-phase effort:
- Application and SQL Performance Diagnostics. Identify the root cause of the performance issues, including a review of the Azure SQL instance, the application configuration, and the related application code. Additionally, recommend and assist in implementing appropriate telemetry and instrumentation to enable current and future discovery of performance issues.
- Azure Optimization. Atmosera was to analyze the cloud architecture, the code is used to query the database, the application code, settings and configuration, and the configuration and settings of cloud resources to identify performance optimization areas. Based on this analysis, implement the identified performance optimizations. Finally, migrate the remediated application and databases to Azure.
The choice of Azure Functions for a core part of the solution was also a key success component. Serverless on Azure proved to scale amazingly well.
USER FLOW
The basic user flow is as follows:
- A franchisee drafts a new menu in the new Menu Manager portal application, accessible from anywhere.
- The administrator clicks Publish, which triggers a series of Azure functions that will validate the menu against inventory, corporate policies, tax information, and more.
- Once validation is successful, the new menu is staged in a set of respective databases.
- Once all databases have reported a successful stage, the current menu version is incremented to the new one. All new order transactions are directed to the new menu.
All of these tasks used to be performed using a monolithic application hosted on Windows servers that did not scale efficiently and could take many hours to process. This processing time has now been reduced to sub-minutes.
Figure 1 – High-Level Conceptual Architecture
Why Atmosera?
The customer needed a partner that was expert at providing the architectural, debugging, performance optimization, and application/database skills needed to take a mission-critical enterprise system and make it flexible and performant, as well as the implementation expertise to deploy the production system to the Azure cloud.
- Atmosera has deep experience in application and SQL Database debugging and performance tuning, as well as in the development of these solutions.
- Atmosera is a Microsoft Gold Cloud Platform, Application Development, Data Platform, DevOps, and Data Analytics partner.
- Atmosera is a recognized leader in software architecture and implementation on the web, mobile, and cloud platforms.
Conclusion
The new application and databases were rolled out on time and under budget, allowing franchisees to update their restaurant menu and publish it with a single click. Once published, an API triggers a series of parallel functions to update all menus and place messages on a Service Bus to alert all order channels (App, GrubHub, UberEats, local restaurants, etc.) that a new menu is ready for publishing.
- All timeouts and performance issues were resolved, greatly exceeding performance goals.
- The new system provides for 4,500+ simultaneous menu updates, successfully updating all menus within minutes.
- The company achieved a 4x to 5x performance improvement, leveraging the use of Azure App Service and SQL Azure.
- Atmosera worked with the company and Microsoft to assess the Azure app and Azure SQL implementations and identified key fixes to queries that showed excessive numbers of locks and long execution times. Atmosera then worked closely with company to review the app code and the deployment configuration, helping eliminate performance issues from the web application and optimize it for Azure.
We deliver solutions that accelerate the value of Azure.
Ready to experience the full power of Microsoft Azure?
Start Today