The Azure cloud platform was created by Microsoft to provide enterprises with the benefits of distributed storage and computing while moving their data into the cloud. The key component of that platform is the Azure SQL Database that allows enterprises to experience the benefits of distributed storage and computing like flexible scaling, saving cost by outsourcing their in-house IT infrastructure, and maximizing data availability. Microsoft Azure SQL database is a cloud-based relational database service offering better durability, availability, and scalability compared to on-premises databases. It is reliable and secure and gives high performance without worrying about any infrastructure. Currently, this database is used by several applications as a part of their data strategy.
The database limits for a single database are defined by the database service tier and performance level. Thus when an enterprise chooses a specific database service tier, then they are selecting a pre-defined set of allocated resources across CPU, storage type, memory, storage limit, and more. Ideally, an enterprise chose a service tier that caters to the workload demands of their application. However, if their service tier selection is over or under-size then they can accordingly easily scale up or down. With their service tier selection, the enterprise is inherently selecting a set of resource usage boundaries or limits. On increasing the resources in their tier, the enterprise may also experience changes in limits, and over time these limits can be automatically relaxed but never restricted without penalty.
The database service tier allocates resources to clients depending upon the service tier chosen by them. The service tier that is of more prices allocates more resources to clients. Database resource management and its limits are subjects in which clients often lack clarity and hence Microsoft is increasing transparency around these mechanisms. Clients need to understand the motive behind database resource management as a service and they need to put the effort into achieving a balanced system. A database is considered balanced if all resources are effectively maximized without experiencing bottlenecks. The balanced database comprises interplay of resources like CPU, memory, IO, and network paired with an application’s workload characteristics, desired throughput, and maximum tolerated latency. In Azure SQL Database the balanced system takes a broader perspective in meeting DBaaS requirements and customer expectations.
The key resources that are managed within the Azure SQL database include CPU allocation, memory allocation, degree of parallelism, session maximum, data and log storage maximum, network IO, log generation rate maximum, IOPs limit, and worker thread maximum. The balanced system should be provided in a way that leads to the continuous improvement of the service over time. The need for continuous improvement implies a necessary level of over-arching management and component abstraction. Azure SQL Database management ensures that requirements are properly balanced around high availability, scale, recoverability, disaster recovery, and predictable performance.
Resource Management Mechanisms in Azure SQL Database
Resource management in Azure SQL database is carried out through two different mechanisms called resources governance and enforcement of limits.
Resource governance
In Azure SQL Database, the primary design goal of the Basic, Standard, and Premium service tiers is to behave as if the database is completely in isolation from other databases and is running on its own machine, and this behavior is emulated by the resource management. If the gathered resource utilization reaches the maximum CPU available, log I/O, data I/O, and memory resources assigned to the database, then queries in execution will be queued by the resource management and resources will be assigned to the queued queries as soon as they free up. Utilization of all available resources on dedicated machine results in a longer execution of executing queries that may result in command timeouts. Thus applications that execute queries against the high-frequency database may encounter error messages while executing new queries on reaching the limit of concurrent requests. Clients while reaching the maximum utilization of a database need to monitor the resource utilization along with the average response times of queries. On experiencing higher query latencies clients can either reduce the number of incoming requests to the database thereby preventing timeout and the pile-up of requests, or assign a higher performance level to the database, or they can optimize queries to reduce the resource utilization of every query.
Enforcement of limits
Besides CPU, Memory, Log I/O, and Data I/O, all the other resources are enforced by denying new requests on reaching the limits, and clients will receive an error message. In an SQL database, the number of connections as well as the number of concurrent requests that can be processed is restricted. To support connection pooling, the database allows the number of connections to the database to be greater than the concurrent requests number. Though the number of available connections can easily be controlled by the application but the number of parallel requests is difficult to estimate and control. During the peak loads errors can be experienced when too many requests are sent by the applications or the database reaches its resource limits and work threads start to pile up due to longer-running queries.
Summary – Azure SQL database allows businesses benefit from distributed storage and computing, as it offers better durability and scalability compared to on premise databases. Resource management in Azure SQL database could be carried out either through resources governance and enforcement of limits.