Azure SQL Database

Azure SQL Database is a cloud-computing database service, which helps to host and use a relational SQL database in the cloud without requiring any hardware or software installation. Microsoft performs all the patching and updating of the code base, and manages the underlying infrastructure for you, so you can save time and resources.

SQL Server Database Engine architecture 

There is multiple architecture, that is adjusted for the cloud environment in order to ensure 99.99% availability even in the cases of infrastructure failures. There are three architectural models that are used in Azure SQL Database

1.       General Purpose/Standard

2.       Business Critical/Premium

3.       Hyperscale

General Purpose

A default service tier in Azure SQL Database that is designed for most of the generic workloads. If you need a fully managed database engine with 99.99% Service Level Agreement (SLA) with storage latency between 5 and 10 ms that match Azure SQL IaaS in most of the cases.

The following figure shows four nodes in standard architectural model with the separated compute and storage layer. In the architectural model for the general-purpose service tier, there are two layers.

Whenever database engine or operating system is upgraded, some part of underlying infrastructure fails, or if some critical issue is detected in SQL Server process, Azure Service Fabric will move the stateless SQL Server process to another stateless compute node. There is a set of spare nodes that is waiting to run new compute service if a failover of the primary node happens in order to minimize failover time. Data in Azure storage layer is not affected, and data/log files are attached to newly initialized SQL Server process.

This process guarantees 99.99% availability, but it might have some performance impacts on heavy workload that is running due to transition time and the fact the new SQL Server node starts with cold cache.

Business critical

Premium/Business Critical service tier model is based on a cluster of database engine processes. This architectural model relies on a fact that there is always a quorum of available database engine nodes and has minimal performance impact on your workload even during maintenance activities.

Azure upgrades and patches underlying operating system, drivers, and SQL Server Database Engine transparently with the minimal down-time for end users.

In this architectural model Azure SQL database integrates compute and storage on the single node. High availability in this architectural model is achieved by replication of compute (SQL Server Database Engine process) and storage (locally attached SSD) deployed in four node cluster.

Business Critical service tier is designed for the applications that require low-latency responses from the underlying SSD storage (1-2 ms in average), fast recovery if the underlying infrastructure fails, or need to off-load reports, analytics, and read-only queries to the free of charge readable secondary replica of the primary database.

Hyperscale

Hyperscale is the ability of an architecture to scale appropriately as increased demand is added to the system. Hyperscale service tier is only available in vCore model. To align with the new architecture, the pricing model is slightly different from General Purpose or Business Critical service tiers.

Hyperscale computing refers to an architecture that expands and contracts based on the current needs of the business. That scalability is seamless and involves a robust system with flexible memory, networking, and storage capabilities.

The hyperscale data center is built around three key concepts

1.       The infrastructure and distributed systems able to support the data center operations.

2.       Scalability for computing tasks to ensure efficient performance based on the demand

3.       Appropriate revenue

As you know in Traditional database engines that have centralized all of the data management functions in one location/process (even so-called distributed databases in production today have multiple copies of a monolithic data engine), a Hyperscale database separates the query processing engine, where the semantics of various data engines diverge, from the components that provide long-term storage and durability for the data.

Deployment models

Azure SQL Database provides the following deployment options. User can choose them based on their requirement

1.       Managed instance

2.       Elastic Pool

3.       Single Database

Managed instance

Azure SQL Managed Instance is designed for customers looking to migrate a large number of apps from on-premises or IaaS, self-built, or ISV provided environment to fully managed PaaS cloud environment, with as low migration effort as possible.

As discussed in above service tier basically architectural model relies on high availability and reliability, based on your requirement you can subscribe it. It provides 2 types of service tier

1.       General Purpose

2.       Business critical

It has vCore-based purchasing model for SQL Managed Instance gives you flexibility, control, transparency, and a straightforward way to translate on-premises workload requirements to the cloud. This model allows you to change compute, memory, and storage based upon your workload needs.

Elastic Pool

SaaS Application developers build applications on top of large scale data-tiers consisting of multiple databases. A common application pattern is to provision a single database for each customer. But different customers often have varying and unpredictable usage patterns, and it's difficult to predict the resource requirements of each individual database user. In this case we use elastic pool azure SQL database. Elastic pools solve this problem by ensuring that databases get the performance resources they need when they need it.

The price of a pool is a function of the pool eDTUs (elastic Database Transaction Units). While the eDTU unit price for a pool is 1.5x greater than the DTU (Database Transaction Units) unit price for a single database, pool eDTUs can be shared by many databases and fewer total eDTUs are needed.

In above example you can see. The aggregate DTU utilization across all 20 databases is illustrated by the black line in the preceding figure. This shows that the aggregate DTU utilization never exceeds 100 DTUs, and indicates that the 20 databases can share 100 eDTUs over this time period. This results in a 20x reduction in DTUs and a 13x price reduction compared to placing each of the databases in S3 compute sizes for single databases.

This example is ideal for the following reasons

1.       There are large differences between peak utilization and average utilization per database.

2.       The peak utilization for each database occurs at different points in time.

3.       eDTUs are shared between many databases.

There is no per-database charge for elastic pools. You are billed for each hour a pool exists at the highest eDTU or vCores, regardless of usage or whether the pool was active for less than an hour.

It has two purchasing model

1.       vCore

2.       DTU

vCore

A vCore-based purchase model is best if you are looking for flexibility, control and transparency of individual resource consumption. This model allows you to scale compute, memory, and storage based upon your workload needs and provides a straightforward way to translate on-premises workload requirements to the cloud.

DTU

In DTU-based SQL purchase models, a fixed set of resources is assigned to the database or elastic pool via performance tiers: Basic, Standard, and Premium. DTU represents a mixture of the following performance unit for Azure SQL Database.

1.       Memory

2.       CPU

3.       Reads and writes

For information regarding purchasing model you can visit below URL

https://azure.microsoft.com/en-us/pricing/details/sql-database/elastic/

Single Database

The single database resource type creates a database in Azure SQL Database with its own set of resources. Each database is isolated from each other and portable. A single database can be moved into or out of an elastic pool for resource sharing

It has two purchasing model

1.       vCore

2.       DTU

We have already discussed these purchasing models and service tier.  

In this database 2 types of compute tier if you choose service tier General purpose

1.       Provisioned

2.       Serverless 

Provisioned

In general, provisioning means "providing" or making something available quickly. The SQL Database provisioned compute tier provides a fixed amount of compute resource for a fixed price billed hourly. It optimizes price-performance for single databases and elastic pools with more regular usage that cannot afford any delay in compute warm-up after idle usage periods.

Serverless

new compute tier made for single databases. This new model automatically scales the compute based on the actual workload per second. This means that you only have to pay for the compute resources (memory & vCores) you use. Furthermore, serverless has an auto pause function. It will pause your database when it remains inactive for a period of minimum 1 hour. For a paused database, only the storage is charged.

The serverless compute tier provides compute auto-scaling and bills per second for compute used.

Comparison between both the compute tiers

All this all about Azure SQL database purchase models, service tiers and compute tiers. You have to choose the tiers very carefully based on requirement and their budget. Choosing right tiers, you can minimize the database cost with high performance. 

Keep sharing keep learning. Thank you