Azure OLTP Solutions Using Azure SQL Sharding, Pools, and Hybrid

Azure provides both Infrastructure as a Service (IaaS) and Platform as a Service (PaaS) services. Both these types of services provide organizations with different levels and controls over storage, compute, and networks. Storage is the resource used when working with the storing and transmission of data. Azure provides lots of options for storing data, such as Azure storage blobs, tables, Cosmos DB, Azure SQL, Azure Data Lake, and more. While some of them are meant for big data storage, analytics, and presentation, there are others that are meant for applications that process transactions. Azure SQL is the primary resource in Azure that works with transaction data.

This chapter will focus on various aspects of using transaction data stores, such as Azure SQL and other open source databases, typically used in Online...

Azure cloud services

A search for sql in the Azure portal provides multiple results. I have marked some of them to show the resources that can be used directly for OLTP applications:

The previous screenshot shows the varied features and options available for creating SQL Server-based databases on Azure.

Again, a quick search for database on the Azure portal provides multiple resources, and the marked ones can be used for OLTP applications:

The previous screenshot shows resources provided by Azure that can host data in a variety of databases, including the following:

• MySQL databases
• MariaDB databases

• PostgreSQL databases
• Cosmos DB

OLTP applications

OLTP applications are applications that help in the processing and management of transactions. These applications perform data capture, data processing, retrieval, modification, and storage. However, it does not stop here. OLTP applications treat these data tasks as transactions. Transactions have a few important properties and OLTP applications adhere to these properties. These properties are grouped under the acronym ACID. Let's discuss these properties:

• Atomicity: This property states that a transaction must consist of statements and either all statements should complete successfully or no statement should be executed. If multiple statements are grouped together, these statements together form the transaction. Atomicity means each transaction is treated as the lowest single unit of execution that either completes successfully or fails.
• Consistency: This...

Relational databases

OLTP applications have generally been relying on relational databases for their transaction management and processing. Relational databases typically come in a tabular format consisting of rows and columns. The data model is converted into multiple tables where each table is connected to another table (based on rules) using relationships. This process is also known as normalization.

As mentioned before, Azure provides multiple relational databases, such as SQL Server, MySQL, and PostgreSQL.

Deployment models

There are two deployment models for deploying databases on Azure:

• Databases on Azure virtual machines (IaaS)
• Databases hosted as managed services (PaaS)

Databases on Azure virtual machines

Azure provides multiple SKUs for virtual machines. There are high-compute, high-throughput (IOPS) machines that are also available along with general-usage virtual machines. Instead of hosting a SQL Server, MySQL or any other database on on-premises servers, it is possible to deploy these databases on these virtual machines. The deployment and configuration of these databases is no different than that done for on-premises deployments. The only difference is that the database is hosted on the cloud instead of on on-premises...

Azure SQL Database

Azure SQL provides a relational database hosted as a PaaS. Customers can provision this service, bring their own database schema and data, and connect their applications to it. It provides all the features of SQL Server that you get when deploying on a virtual machine. These services do not provide a user interface to create tables and its schema, nor do they provide any querying capabilities directly. You should be using SQL Server Management Studio and the SQL CLI tools to connect to these services and directly work with them.

Azure SQL Database comes with three distinct deployment models:

• Single instance: In this deployment model, a single database is deployed on a logical server. This involves the creation of two resources on Azure:
  • SQL logical server
  • SQL database
• Elastic pool: In this deployment mode, multiple databases are deployed on a logical server...

Single instance

Single-instance databases are hosted as a single database on a single logical server. These databases do not have access to the complete features provided by SQL Server.

High availability

Azure SQL, by default, is 99.99% highly available. It has two different architectures for maintaining high availability based on the SKUs. For the Basic, Standard, and General SKUs, the entire architecture is broken down into the following two layers. There is redundancy built in for both of these layers to provide high availability:

• Compute layer
• Storage layer:

For the Premium and business-critical SKUs, both compute and storage are on the same layer. High availability is achieved by replication of compute and storage...

Elastic pools

An elastic pool is a logical container that can host multiple databases in a single logical server. The SKUs available for elastic pools are as follows:

• Basic
• Standard
• Premium

The following screenshot shows the maximum amount of DTUs that can be provisioned for each SKU:

All the features discussed for Azure SQL single instances are available to elastic pools as well; however, horizontal scalability is an additional feature it provides with the help of shading. Shading refers to the vertical or horizontal partitioning of data and the storing of it into separate databases. It is also possible to have auto-scaling of individual databases in an elastic pool by consuming more DTUs than are actually allocated to that database.

Elastic pools also provide another advantage in terms of cost. We will see in a later section that Azure SQL is priced using the concept of DTUs...

Managed Instance

Managed Instance is a unique service that provides a managed SQL server similar to what's available on on-premises servers. Users have access to master, model, and other system databases. Use of Managed Instance is suitable when there are multiple databases and customers migrating their instances to Azure. Managed Instance consists of multiple databases.

Azure SQL Database provides a new deployment model known as Azure SQL Database Managed Instance that provides almost 100% compatibility with the SQL Server Enterprise Edition Database Engine. This model provides a native VNet implementation addressing the usual security issues and is a highly recommended business model for on-premises SQL Server customers. Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database...

SQL database pricing

Azure SQL previously had just one pricing model – a model based on Database Throughput Units (DTUs) and a more recent alternative pricing model based on vCPUs have also been launched.

DTU-based pricing

The DTU is the smallest unit of performance measure for Azure SQL. Each DTU corresponds to a certain amount of resources. These resources include storage, CPU cycles, IOPS, and network bandwidth. For example, a single DTU might provide three IOPS, a few CPU cycles, and an IO latency of 5 ms for read operations and 10 ms for write operations.

Azure SQL provides multiple SKUs for creating databases, and each of these SKUs has defined constraints for the maximum amount of DTUs. For example, the Basic...

Summary

Azure SQL is one the flagship services of Azure. Millions of customers are using this service today and it provides all the enterprise capabilities that are needed for a mission-critical database management system. There are multiple deployment types for Azure SQL, such as single instance, Managed Instance, and elastic pools. Architects should do a complete assessment of their requirements and choose the appropriate deployment model. After they choose a deployment model, they should choose a pricing strategy between DTUs and vCPUs. They should also configure all the security, availability, disaster recovery, monitoring, performance, and scalability requirements in Azure SQL with regard to data.