Options of SQL inside Azure (Azure SQL)

We have so many options of SQL inside Microsoft Azure that you need to be careful when you decide which one to use. In this post, I would like to present the most important options of SQL that you can have inside Azure. Let's check out one by one !!!

As per what i understand there are 3 “base” flavours of SQL Server in Azure according to their SQL Server capabilities:

1. SQL Server on Azure VM

2. Azure SQL Database

   • Singleton [Resource Type]

   • Elastic [Resource Type]

   • DataBase Server [Resource Type]

3. Azure SQL Database Managed Instance

SQL Server inside Azure VMs (IaaS)

The classical IaaS approach where customer needs to manage the VMs and the SQL Server instance that is running on top of Azure. The list of tasks that you need to do when you run your SQL instances on top of Azure VMs are the same as you would have the SQL Server instances inside your own data-centers. From license, to backup and restoration procedures, all of this needs to be managed by yourself.

You get the option to select SQL image from drop down.

SQL Database (PaaS)

The current offer of Azure related to SQL on the PaaS environment is more complex than you would expect. In this moment there are 4 options available, that cover most of the uses cases that you might have.

• SQL Database (Singleton)

• Elastic Pool

• DataBase Server

• Managed Instance (MI)

A short overview on each of one can be found below.

1. Azure SQL Database (Singleton)

   Azure SQL Database (singleton), or Single Database is a fully managed Database-as-a-Service that provides the most important database features available in SQL Server and includes the Azure SQL database PaaS capabilities. Some instance-level features such as SQL Agent, CLR, Service Broker are not supported in Single Database. It’s fully hosted and managed by Azure, client is responsible only for the content that is pushed inside the storage. All the maintenance and complex procedures to have a high SLA and good backup and restore procedures and fully managed by the platform.

   You just specified the tier and the DB size and from that moment, you don’t care about anything else. You pay based on your use and you can scale up or down dynamically.

   There is no management of infrastructure cost, being out of the box fault tolerance. Features like Point-In-Time restoration, geo-restoration and replication are included. It’s a good option when you start from scratch and you want to focus on your business value.

   Each instance of database has its own virtual resources. It is important to remember that even if each tier has a specific number of resources reserved this does not mean that physical servers are dedicated to your own SQL database instance. Because of this, you cannot share available resources between different SQL database instances. In comparison with this, Elastic Pool allows us to share resources between database instances

2. Elastic Pool

   In comparison with Azure SQL Databases (Singleton), elastic pool is a little bit different. Even if you still create SQL Database instances, you put all of them in the same pool, where all resources are shared. These means that all your DB instances that are part of the same Elastic pool are using and sharing the same CPU, IO and memory.

   This is a model that works great if you have multiple DBs, where the pick of each instance is in different time periods. When you are using Elastic Pool don’t forget that all the DB instances are having the same tier and performance of database instances can be affected by the other databases that are in the same pool.

   You can reserve computation power that it is shared between database instances, but without caring about backup, pooling and other stuff that are coming for free.

   Just do not forget that things like SQL Agents, SSIS and Service brokers are not available inside Elastic Pool.

3. DataBase Server

   Database Server runs your databases. You can't create "Single Database" without a server.You pay only for databases/elastic pools, but you manage them through database server.One server can host many databases.Database Server is free and is mostly a logical/management tool, something like a "Resource Group".

4. Managed Instances

   

   In comparison with Elastic Pool you have all the features that you have on-premises (like SQL Agents, SQL CLR, cross-database querying), but at the same time you can use the full power of Azure SQL offering you out of the box support for automatic backups, high-availability and many more.

   An interesting thing of Managed Instance is how you can configure it. You can put it inside a VNET, using his own private IP, without direct access to internet. It’s like having an Azure SQL Database with all the features inside a private network.

   It is the right place when you start a migration from on-premises to Azure.

   Size Limitation

   

Conclusion

Which one to use ?? Depends on use cases which is mentioned below in a chart.

My experience was good with SQL on Azure VM [traditional way] and Azure SQL Managed instance.

Kindly share your valuable experience here !!!

Hope this article help !!!

📢 Stay tuned for my next blog.....