Azure and Database Optimization leads to Sixty Percent Savings

Project Start – Growth Evaluation and Knowledge Management

Microsoft Azure contains an abundance of technologies and services available for any business. There are IaaS, PaaS, and FaaS options across the board. These often provide overlapping functionality, varying cost models and security concerns, and different scalability. Microsoft has done extremely well in making development and deployment to these options simple. It does not take an Azure expert to start or grow a product using Azure.

The various services Azure provides generally start off at a low cost per month, and easily scale upward as business needs expand. Capitol Energy Systems had followed a typical lifecycle for a growing company. They had two major PaaS services, an App Service Plan and an Elastic SQL Pool. They supplemented this with various FaaS services to support their products. As their business grew, they simply increased their resource configuration to handle a larger workload. When a pain point occurred, CES developers would evaluate and adjust the code as needed for the issue. New features would be implemented in a similar fashion, with optimizations only occurring if the additional resource cost seemed out of balance to the dev team.

No one on the CES team was an Azure expert. Indeed, expertise is not needed to implement or maintain a basic Azure environment. But as the monthly costs grew CES wanted an evaluation of their Azure framework to determine if it was being used optimally. Their assumption was not that they could reduce their resource needs with this project. They were looking to define their expansion into the next year and beyond; and determine future costs.

Capitol Tech Solutions approached this project by first documenting their current resource use. Like many companies, the institutional knowledge was largely undocumented. While the scope of the project did not encompass developing a full Knowledge Management System (KMS) some simple documentation was needed. Azure cost and resource reports provided the basis quickly and easily. Some short scrums then allowed the details to be filled in. This process immediately found unused services, one with a monthly cost that paid for this project within two years (This was both unusual and not the main driver of cost reductions discussed below, but a nice bonus for CES at the very start of the project).

Once basic knowledge was documented and understood by the CTS reviewer, a deeper evaluation of the service was performed to understand potential growth. The main driver of system resources was assumed to be the heartbeats of thousands of IOT devices. Each device chimes in with its status. The system then evaluates and performing further actions based on that heartbeat.

While a review of the App Service Plan and FaaS support services did support this pattern, the SQL server did not. CTS discovered two main issues when evaluating the SQL: The heartbeat was not as efficient as it could be, and the main resource consumer was not following the pattern of the heartbeat.

While these issues were understood by CES developers, the underlying cause was not. They believed that the issues were simply the products of a large workload and datasets. Another project was in place to reduce the historical data contained in the database. But the SQL database itself was not optimized appropriately to the workload. Our specialist determined that significant performance improvements could be implemented without a large project.

Project Expansion – Knowledge Transfer

With the large performance gains already made, CES expressed a desire for a deeper review. Our specialist believed there was more to be achieved. But the opportunity cost was changing. Further reductions would involve code changes and performance monitoring. It would take more evaluation for likely less performance gains. Ultimately it was decided to expand monitoring and provide some knowledge transfer. This would allow the CES development team to more naturally discover and implement performance improvements themselves.

Proper SQL structure design is vital to a healthy server. Experienced full stack developers understand the basics of SQL, and generally have solid skills at getting data from it. Azure has useful tools to further analyze, and even automatically update the structure of the database. But these tools and experience often lead to individual optimized pieces, not whole system health. Data Adapters, Entity Framework, and other database interaction technologies often cause additional complexities.

Our specialist worked with CES developers to define performance goals for the whole system. Then they developed a plan to identify limitations in SQL monitoring. Once goals and limitations were defined and identified, the development team was able to make decisions on what could be changed and what type of performance increase to expect from the work. This allowed management to define a workload that could be cost/benefit compared to other needs such as new feature requests and bug fixes.

This new understanding of the system allowed CES to obtain a significant additional reduction to cost. Additionally, they were able to do so with advanced knowledge of what would be achieved, before time was committed to implementing the solution. Most importantly, CES is able to continue using the techniques developed into the future.