Case Studies

Our client approached us with a task of improving the search function of a complex database — a cloud distributed solution with a variety of web and mobile clients containing 14 different services each with its own database in Azure SQL. The database stores a huge number of entities with multiple properties each and multiple connections to smaller databases.

The search function allows setting variable filters with a large number of different conditions. Due to the complex nature of the database, search queries often produced unsatisfactory results: the database allows importing data from external systems, periodically processing the data and updating entity properties, thus resulting in search queries intersecting with batch data updates, database high load, records blocking, and indexes rebuilding.

We have developed Search Engine — a separate service for implementing an effective search function. We have moved the search feature into a new service deployed in its own Azure App Service Plan, which allows it to provide more memory and vCPU and scale it separately, as well as provides support for scaling instances count that helps improve critical feature’s reliability.

Additionally, we have implemented an API Gateway to implement the ability to smoothly switch between the main database and the Search Engine service.

Database Search During Mass Updates

Ensuring stable Search Engine operation during mass database updates was an important and complex problem.

After in-depth research of possible solutions, we have decided on a Geo-replication feature which allows implementing a read-scale out approach by reserving an additional Azure SQL Server with a live replica of the main database.

Cross-Database Search

Implementing cross-database search was another important step as search queries could contain conditions which had to be processed by the main servers and additionally those that had to be addressed to Interaction Service.

Initial implementation included communication between two services, requiring the generation of two sub-results and assembling them into a final one. We changed this approach by configuring a new Azure SQL Server with the replicas of both databases and adding a link to an Interaction Replica inside the main Recruiting DB. As a result, Recruiting Replica included a link to an Interaction Replica, which allowed to address all search conditions into a single Recruiting Replica DB that was using cross-DB joins to the Interaction Replica DB without involving any of the original Databases.

Our Search Engine and the general redesign of the search system has greatly improved the stability, efficiency and reliability of the function.

We split read and write queries between two databases keeping the possibility of the separate scaling. Our read-only replica uses a lower tier, which helps us keep costs under control.

We avoided building and integrating a separate cluster (Elastic) that would require significantly more development time and didn't switch to Premium tiers thus saving our client from increased cloud bills.