Runtime HRMS is now fully migrated to Azure Serverless Cloud Database. This has resulted in significant performance improvements. Here, we will talk about how and why it was done.
In mid-July 2023, we started facing some SQL timeout errors during peak hours. Peak hours are usually office start and end times (9AM to 11AM and 6PM to 8PM). During the peak hours, there are multiple punch submissions by employees which include Remote Punch, Travel Punch, QR Punch and Biometric Sync as well.
A careful investigation revealed that our Database server was unable to fulfil the peak load and deadlocks were occurring. Deadlock is a situation where multiple writes are performed on same database table and each of them is trying to lock the table for writing. In such cases, one of the connection is unable to get lock and fails. When such deadlock occurs, the victim connection’s user will get an error and they have retry submitting the punch.
Current Database Structure
We run our workloads on Virtual Machines (VMs) which dedicated database servers. One machine can handle up-to 100 client databases. Once the capacity is exhausted, we spin up a new database server. Although this strategy was working find till this time, it looked like as we scale with multiple machines, it will become harder and harder to manage multiple database VMs.
Switching to Azure Serverless
One of the solution to avoid deadlocks was to upgrade the VM capacity. But that would not solve the scalability issue as described above. Therefore, we started looking for a more robust solution and came across Serverless database service from Azure, AWS and Google. Since our most workload is with Azure, we went ahead and reserved some capacity with Azure by creating a Database Pool. A Pool is a collection of databases with a fixed capacity in terms of CPU and Memory usage. We migrated only a few databases to the Azure Db Pool to check the results in mid-July 2023. The performance was indeed super-fast and all the dead-locks went away.
By end of July 2023, we migrated all database workloads onto the Azure Db Pool. Ever since, we haven’t faced a single timeout or deadlock exception. This Db Pool is fully managed by Azure and we no longer need to worry about server patching, security, firewalls etc.
It looks like Serverless is the future of hosting. This learning made us more open to opt for Serverless solutions as we scale our services.
