Quick Track: Beginner’s Guide to Azure SQL

As I started learning Azure couple of months ago, I was confused in the beginning of where to start and was overwhelmed by looking at the amount of information that I not only have to learn but also remember what I learn.

I believe you can treat this series as my consolidated learnings put together in a simple way. A quick guide to understanding the concepts if you are a beginner or to revise the concepts you already learnt to make your foundations stronger.

Let’s start with a Why Question. Why should you even start learning Azure or any cloud platform?

Azure is one of the cloud platforms available in the market which provides services that you can use to host your applications, build your databases, virtual machines, storage, analytical and many other services without having to buy or manage any hardware. As the Azure supports Infrastructure and platform as a service, you can build, configure and run your databases and related services pretty quickly and with ease of deployments through Azure portal online/command line interface/PowerShell.

Deployment options

IaaS: Infrastructure as a Service

SQL Server on Azure VM
- SQL Server hosted on Azure Infrastructure
- Automated backups and patches
- Extended security updates for the older versions of SQL Server (SQL Server 2008)
- You will have access to operating system and can configure instance level features for SQL Server
- Easy Install- through Azure portal using images (various version combinations of available packages for both Windows and SQL Server together)

PaaS: Platform as a Service

SQL Managed Instance
- OS is taken care by Microsoft
- You will not have access to the OS
- Use and configure all features of the SQL Server instance
- Useful for lift and shift scenarios where applications don’t have to change their code in order to move to this service
Azure SQL Database
- You will just get the database
- Database level features can be configured
- OS and Instance capabilities taken care by Microsoft
- very useful for Modern cloud application built
- Very quick to deploy

For the full list of feature comparison between Azure SQL Database and SQL managed instance, read the Microsoft doc here.

Unlike the SQL Server on Azure VM, Azure SQL database and SQL managed instance are versionless. Databases, OS are upgraded, patched behind the scenes by Microsoft.

Purchasing models:

There are two purchasing models, DTU and vCore.

In the DTU (database transaction per unit) model, we choose the number of DTU’s and the resources comes in bundle together (compute and storage resources). vCore model has the advantage of using the Azure hybrid benefit and reserved capacity if you can pay for the resources ahead of time up to three years. When you use vCore model, it is easy to convert the on-prem workloads to the azure as you can independently choose the compute, storage and other resources like memory.

DTU	vCore
Bundle together	Individual compute and storage
Easy and simple	Customize and control
Not flexible	Flexible
Azure Hybrid benefit – NO	Azure Hybrid benefit – YES
Available for Azure SQL Database only	Available for MI and Azure SQL Database

The cost of the vCore model depends on the service tier, compute, storage, hardware you choose, backup storage you choose.

Note: you can convert the DTU model to vCore model. Know more here.

Service Tiers

We have three Service tiers for Azure SQL database and Managed instance – General purpose, Business critical and Hyperscale. Especially under the General purpose tier, we have two compute tiers. We will learn that in coming section. For the main differences among the service tiers, please follow below table.

Service Tier	General purpose	Business critical	Hyperscale
Purpose	General workloads	Low latency workloads	OLTP, large databases
Compute	80 vCores	80 vCores	80 vCores
Storage type	Remote 5 GB – 4 TB	Local SSD 5 GB – 4 TB	Local SSD cache Up to 100 TB
Backups	1 to 35 days (7 as Default)	1 to 35 days (7 as Default)	1 to 35 days (7 as Default)
Availability	1 replica, no Read Scale-out, zone-redundant HA (preview), no local cache	3 replicas, 1 Read Scale-out, zone-redundant HA, full local storage	Multiple replicas, up to 4 Read Scale-out, zone-redundant HA (preview), partial local cache
In-memory	Not supported	Supported	Not supported
IOPS	500 per vCore with max 7,000	5,000 with max 200,000	Multi-tiered architecture Effective IOPS will depend on the workload

Compute Tiers

Especially under the General purpose service tier with vCore model, we have two Compute tiers, Provisioned and Serverless.

Provisioned	Serverless
Predictable workloads	Unpredictable workloads
Manual scaling	Automatic scaling
No pause	Pause the database
Pay for the fixed resources you choose	Pay only while database in use
Per hour billing	Per second billing

For example, you will need to chose provisioned compute tier if your database has users connected most of the times having more usage of the compute resources and you cannot predict the traffic to the database. On the other hand, if you have predictable workloads with the specific timings of the users connecting to the database, you can use the serverless compute model.

You will be choosing the max amount of resources ahead of time and scale manually and you will be paying even if you are not using the resources in full. With the serverless, it is automatic scaling. You will be choosing the min and max number of compute resources so the automatic scaling happens only in between those min and max limits.

The advantage you have by using the serverless is to pause the database during the specific timings you choose. These are the timings when the users will not be connected to the database. Once the database goes to the pause mode, you will be only paying for the storage and not for the compute. If you are using Serverless option and do not place your database in auto pause mode, then you will be charged for the min amount of resources when users are not connected and charged per second basis on the resources you use during the activity. (Source)

For the provisioned compute tier, the billing is per hour basis where as for the serverless compute tier, billing is per second basis.

Summary:

In this Quick track of basics to Azure SQL, we learnt about the overview about the deployment options, purchasing models, service tiers, compute tiers and how they differ from each other. In the next blog posts, we are going to learn more about what Azure SQL has to offer.

Thanks for reading!

References:

Share this:

Like this:

Leave a Reply Cancel reply