Choose your SQL Database option on Azure

 Microsoft Azure provides various option for SQL Database on Azure platform and it is often confusing what to choose when. Here I’ll be trying to answer the questions and help you to choose the right SQL database on Azure.

Microsoft provide below options for SQL Database options on Azure Platform:

1. SQL Server on Azure VMs
2. SQL Server Managed instances:
   - Single Instance
   - Instance Pool
3. Azure SQL:
   - Single Database
   - Elastic Pool

Now lets see what has been offered with these different options.

Azure SQL Database
A PaaS deployment option and it is a highly scalable, intelligent, relational database service built for the cloud with the industry’s highest availability SL of 99.995% and huge database storage up to 100 TB.. It is best to support modern cloud applications on an intelligent, managed database service, that includes server less compute.

What you won’t have with this options are:
- No Backup commands supported but you have Automatic Backup options from Azure.
- Common Language Runtime (CLR) not supported.
- Linked Server (Cross-database queries or transactions) not supported.
- Database Mirroring and snapshots features are not supported.
- No Event Notification but you can set Alerts
- Extended Stored Procedures are not supported. Anyway this feature will be removed from future version of SQL Servers so not expecting it to be here too, by the way CLR integration is an alternative here but that too not supported in this version.
- Filestream not supported. This features enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system.
- Time Zone Choice is not available if you are looking for it and it obvious as it is fully managed resource.
- SQL Server Profiler is not available but Extended events are available as an alternatives
- Machine Learning Services are not supported.
- No Attaching/De-attaching database feature.

There are other features too which are not available but I’m mentioning above as these are the important feature which can help you on decision making.

Elastic pool: In Azure SQL database you can set Elastic Pool which will enable you to buy a set of compute and storage resources that are shared among all the databases in the pool. Each database can use the resources they need, within the limits you set, depending on current load.

SQL Server Managed Instances
This also a PaaS deployment option of Azure SQL and provides an instance of SQL Server, but removes much of the overhead of managing a virtual machine. Most of the features available in SQL Server are available in SQL Managed Instance.

SQL Managed Instance is ideal for customers who want to use instance-scoped features and want to move to Azure without re-architecting their applications.

SQL Managed Instance instance-scoped features include:
- SQL Server Agent
- Service Broker
- CLR
- Database Mail
- Linked Servers
- Distributed Transactions (currently in Preview in the time of this article)
- Machine Learning Services

What are not supported compare to Azure SQL Instance:
- Filestream
- No Attaching/De-attaching database feature.
- Database Mirroring and snapshots features are not supported.
- Event Notifications
- Extended stored procedures

And the most important point to note here is, Azure SQL Server managed instances supports databases up to 16 TB only.

With Azure SQL and Azure SQL Managed Instance, the database software is automatically configured, patched, and upgraded by Azure, which reduces your administration costs. In addition, its built-in backup capabilities help you achieve significant cost savings, especially when you have a large number of databases.

SQL Server on Azure VMs
It is a version of SQL Server that runs on Azure VM instead of your on-prem server. So you have all the capabilities offered by SQL Server in this case.

SQL Server on Azure VMs gives you full control including OS control like on-prem infrastructure.

Now lets conclude the options, when to choose what.

SQL Server on Azure VMs : When considering migrations and applications requiring OS level access.
SQL Server Managed Instances : When considering Lift and Shift migrations to the cloud.
Azure SQL : When considering modern cloud applications solution.

To know more about features comparison read here Azure SQL Database and Azure SQL Managed Instance.

Hope you enjoyed the content, follow me for more like this and please don’t forget to like/comment for it. Happy programming.