Azure SQL Database Service Tiers

There are two service tires for Azure SQL Database and SQL Managed Instance

General Purpose
Business Critical

General Purpose

The General Purpose service tier architecture is based on the separation of compute and Azure storage.

This service model guarantees no data loss in case of infrastructure failure.

There are two layers in case of General Purpose service tier

1- A stateless compute layer that is running the sqlservr.exe process

2- A stateful data layer with database files that are stored in Azure Blob storage

In case of infrastructure failure, Azure Service Fabric will move the stateless process to another stateless compute node. There are different set of buffer nodes available to run the new compute service in case of any failure happens

This process guarantees 99.99% availability.

Business Critical

Business Critical service tier is based on a cluster of database engine processes.

This architecture model will make sure that there is always a quorum of available database engine nodes and has very little performance impact on your workload even during maintenance activities.

Business Critical service tiers are designed for very intensive workload support even in case of any ongoing maintenance operations.

In this model, compute and storage is integrated on the single node and replication of compute and storage deployed to a four-node cluster.

Business critical service tier architecture

In this case, the primary node pushes the changes to the secondary nodes in order to make sure that the data is available on secondary replicas if by chance the primary node fails for any infrastructure failure.

How to Choose the Right Service Tier

When you are creating an Azure SQL database, the first thing that will come to our mind is which service tier to choose.

This is very common that everyone will look for the service tier one that will be very cheap.

To be honest, choosing the right Azure SQL database service tier is not a very straightforward task.

This depends on the number of factors like what is your budget?, Do you have any special requirement for your data?, Size of your data, Do you want dedicated professionals and administration? etc

This is a common fact that the more you pay the more features you will get in your service tier.

Few things are there to keep in mind is that before selecting the service tier, you need to think of what is the maximum size allowed and weather your data can fit there or not?, What is the backup retention period and weather that satisfies as per your business needs?

One more important thing you need to consider is the performance stuff before choosing the right service tier.

The service tier has two main concepts

SQL Database Edition
Database Throughput Units (DTU)

SQL Database Edition

Basically, there are three types of SQL editions are there

Azure SQL Database Tiers

Basic
Standard
Premium

Each editions have there own set of features and limitations.

Normally, the Premium version has more features compared to standard and basic versions and you need to pay more if you are going with the premium version.

All these three edition supports less than or equals to 2 GB of data. Standard and premium versions support starting from 2 GB up to 250 GB of data. Only premium version supports 250 GB to 1 TB of data.

Database Throughput Units (DTU)

Database Throughput Units (DTU) is an important factor while deciding the service tier. You should decide how many numbers of DTUs you want to go with as per your business needs. The number of DTU starts from 5 to 1750.

Another important factor to consider here is to see the business Continuity options. There are different business continuity options available like

Geo-Restore: This option is available in all the editions. This is related to the last daily back up option based on the region.

Standard Geo-Replication: This option is available for the standard and premium editions. You can define a fixed disaster recovery pair.

Active Geo-Replication: This option is available only in the premium edition. This is most powerful business continuity plan.

Point-in-Time restore: Backups are included on this particular continuity option. The retention duration depends on the edition you are selecting for Basic – 7 days back, Standard – 14 days back and for premium – 35 days back.

Performance is another important factor to consider while choosing the service tier for Azure SQL database.

In the case of the SQL database, the horsepower is measured with Database Throughput Units (DTU). You can choose between 5 to 1750 based on your business need.

When you will choose more number of DTU then yon need to pay more in that case. It is directly proportional to the price you need to pay.

Azure dtu calculator

To calculate the exact number of the DTUs you need, you can use the DTU calculator. What You need to do is you need to download a PowerShell script on the above site, and then you run it on the server where your database is present. immediately after running this script, you will get the following data in a CSV file

Processor – % Processor Time
Logical Disk – Disk Reads/sec
Logical Disk – Disk Writes/sec
Database – Log Bytes Flushed/sec

Enter the number of cores and upload the CSV file with the above data. Click the Calculate button to view your recommended Service Tier/Performance Level and DTUs.

Is Azure SQL database free

Yes, you can create a free account in Azure where you will get the below benefits.

You will get ₹13,300 credit for free for a period of 30 days.
12-month free access to Azure SQL Database.
Free access to 25 + other services.

To create a free account to get all the above benefit, follow my article How to Create Azure Free Account (Step by Step tutorial)

Azure SQL Database Limitations

Well, along with the different benefits, Azure SQL database has a few limitations as well. Those are as below

You won’t able to use the Windows authentication on Azure SQL, Only, you can able to use the Azure Active Directory tokens login as well as the SQL logins.
Another limitation is you won’t get the Database Diagrams here in the case of Azure SQL.
There is no Extensible key management functionality available in the case of Azure SQL.
There is no support for event tracking, notification feature.
Microsoft is only responsible for managing different features like backup & restore, Always-On, database mirroring, log shipping, etc. You won’t have the opportunity to do some configuration by yourself, You have to depend on Microsoft only.
Another important thing is, Replication features are not available in the case of Azure SQL.
Another major limitation is server-level permissions are not available with Azure SQL.
Azure SQL won’t support FILESTREAM which is pointing to any of the external locations.
You won’t able to manage any of the hardware-related server settings like memory, worker threads, CPU affinity, etc.
There is no support for the Export and import of a database that has an external data source, tables.
You won’t get the support for the Cross-database ownership chaining feature.
There is no support for the SSIS and MDS.
No provision to update or change the Collation settings of system objects in the DB.

Azure SQL Standard vs Premium

Let’s discuss about the Azure SQL Standard vs Premium versions. What are the key differences between the Azure SQL Standard vs Premium .

Azure SQL Standard	Azure SQL Premium
An excellent option to start with cloud-designed business applications.	Meant for the mission-critical databases, high transactional volume applications.
Provides middle-level performance.	Provides high performance.
It has access to business continuity features.	It has access to advanced business continuity features.
Transaction rates per minute.	Transaction rates per second.

Difference between Azure SQL Standard and Azure SQL Premium

Azure SQL Database VS Managed Instance

Well, here we will discuss Azure SQL Database VS Managed Instance. The main differences between Azure SQL Database and Managed Instance.

Azure SQL Database	Managed Instance
Azure SQL Database is a cloud computing database service that is basically used to host and use a relational database in the cloud without any hardware or software installation.	The new deployment option of Azure SQL database is known as Azure Managed Instance.
Azure SQL database is part of Azure and is a fully managed cloud database (PaaS).	It is 100% compatible with the latest SQL Server on-premises version (Enterprise Edition)
The interesting features are High availability, Automatic tuning, Remarkable backup and data restoring capabilities, Long-term backup retention, etc.	The interesting features are easy to lift and shift, fully managed PaaS, Strong security features with new business models.

Azure SQL Managed Instance

Azure SQL Managed Instance is one of the scalable and intelligent database services that provide you fully managed and paas. Another cool feature with the Azure SQL managed instance is, it is 100% compatible with the latest enterprise edition of SQL Server.

The most important feature of the Azure SQL Managed Instance is, it helps you to lift and shift your on-premises applications to the cloud environment with few easy steps. The migration became quite easy with the help of the Azure SQL Managed Instance.

Azure SQL Managed Instance has all the Pass capabilities like automatic version updates, patching, automated backup, high availability, etc.

The Azure SQL Managed Instance is actually designed for the customers who wants to migrate a large number of applications from onpremises or self built to the Azure Cloud environment.

Key Features of Azure SQL Managed Instance

It provides you the automatic patching and version upgrade capability.
No need to take any headache for managing the hardware and the infrastructure.
Quite easy to integrate with the other pass data services.
Most importantly, it is highly secure with single sign-on, Azure Active Directory authentication support, SQL auditing, Advanced threat protection, etc.
Build-in feature for database monitoring, metrics.
This also provides the automatic patching software capability.
It has the maximum latest database engine features.

Service tiers

There are two types of service tiers available in the case of Azure SQL managed instance and those are as below

General-purpose: Mainly used for the applications that are meant for typical performance and I/O latency requirements with high-performance Azure Blob storage (8 TB) support and high availability feature.
Business-critical: This service tier is mainly used for the applications that are meant for the low I/O latency requirements and with the highest performance with super-fast local SSD storage and with excellent features like high availability, In-memory OLTP, additional read-only database replica, etc.

Integration with Azure Active Directory

The Azure SQL managed Instance basically supports Azure Active Directory server principal logins and the SQL server DB engine logins.

The Azure Active Directory server principal logins provide you the ability to specify the users and groups from your Azure AD tenant as the principals.

Authentication Options

Below are the authentication options supported by Azure SQL Managed Instance.

SQL Authentication: Where you need to provide the username and password.
Azure Active Directory Authentication: This authentication mechanism uses the identities managed by Azure Active Directory and one of the highly secure authentication mechanisms.

Supported Security Features

As we all know that the security feature is most needed one. There are many key features in terms of security are supported for the SQL Managed Instance.

One of the important security features is the Advanced threat protection feature that provides you an additional layer of security intelligence that helps to protect your database from potentially harmful attempts.
The auditing security feature of the Azure managed instance helps you for tracking the database events and then it helps you to write them to an audit log file that actually helps you to maintain compliance, can closely analyze the database activity, etc.
Another important security feature is the dynamic data masking that helps you save your sensitive data from unauthorized access.
One more important security feature of the Azure managed instance is Row-level security. The RLS feature helps you to implement the restrictions on data rows in a database table based on the category of user.

How to create Azure SQL managed Instance

Check out the steps to create the Azure SQL managed instance now.

Azure SQL Pricing Calculator

Now, you can configure and estimate the Azure SQL pricing easily with the help of the Azure SQL Pricing Calculator. The calculation depends on the Region, type as a single database or elastic pool, Backup storage tier, Purchase model as vCore or DTU, Service tier as general-purpose or business-critical or hyperscale, Compute tier as Provisioned or serverless, hardware type and the instance type, etc.

So check out the Azure SQL Pricing Calculator now.

Azure SQL Database Pricing

Let’s discuss the Azure SQL database pricing here.

The pricing of the Azure SQL Database depends upon the Purchase model, Compute tier, Service tier, hardware type, etc.

vCore is one of the best models that you can choose. If you will consider a Gen 5, For Minimum vCores (0.5), Maximum vCores (40), Minimum Memory (2.02 GB), and Maximum Memory (120 GB), you need to pay $0.0001450/vCore-second.

In the general purpose tier, for the storage, per the GB/month, you need to pay $0.115.

For the Backup storage, in case of Point-in-time restore, for RA-GRS, you need to pay $0.20 per GB per month and For the Backup storage, in case of Long-term retention restore, for RA-GRS, you need to pay $0.05 per GB per month.

For more information, You can check out the Azure SQL Database Pricing now.

Conclusion

In this Azure tutorial, We discussed

Azure SQL Database tiers
How to Choose the Right Service Tier
Is Azure SQL database free
Azure dtu calculator
Is Azure SQL database free?
Azure SQL Database Tiers
Azure SQL Database Limitations
Azure SQL Standard vs Premium
Azure SQL Database VS Managed Instance
Azure SQL Managed Instance
Azure SQL Pricing Calculator
Azure SQL Database Pricing

Hope, you have enjoyed this article!!!