Azure SQL vs SQL Server

Microsoft SQL Server is a popular database solution chosen by organizations because of its performance, scalability and enterprise-ready features. When you need to migrate or create a Microsoft SQL Server project to Azure, there are three different options:

• Azure SQL Server Managed Instances
• Azure SQL Database
• SQL Server on Azure Virtual Machines

Generally, the Azure SQL options help reduce complexity while the SQL Server option increases control. However, there’s no one-size-fits-all answer when choosing Azure SQL vs. SQL Server for an application. Each approach comes with tradeoffs, and organizations need to weigh the pros and cons to decide what’s right for them. 

In this article, we will review all three options in detail, explain their differences, and help you decide which approach is best for your environment. 

Differences between Azure SQL Database vs. SQL Server on Virtual Machines.

Before we get into the details of each solution, let’s highlight the main feature differences between Azure SQL Server vs. SQL Server on virtual machines (VMs). We summarized information from Microsoft’s official docs (1 & 2) in the table below to create a quick reference for Azure SQL Server vs. SQL Server features.

Feature	Azure SQL Server Managed Instances	Azure SQL Server Database	SQL Server on Virtual Machines (IaaS)
Example use cases	Lift & Shift* Legacy Applications that do not require OS-level access to the database server.	Modern cloud-native applications.	Lift & Shift* Rebuilding legacy applications that require OS-level access to the database server. Applications that require a specific version of SQL Server.
Choose this option if	You want Microsoft to manage the SQL Instance Tier fully. You need compatibility with SQL Server for your databases. You need enhanced security over the database instance network.	You want fast provisioning of databases. You need scalability and elasticity on the database capacity.	You require DTC and Polybase functionality. You require SQL Server Analysis Services, Reporting Services, and Integration Services. You require access to Install 3rd party applications on the OS.
Management	Full database management. Partial SQL Server instance management. No OS management.	Full database management.  No SQL Server instance management. No OS management.	Full database management. Total SQL Server instance management. Full OS management.
Authentication Methods Supported	SQL Server Logins Azure AD	SQL Server Logins Azure AD	SQL Server Logins Azure AD
Versions	New features are always available first	New features are always available first	New features come after the Azure SQL Server release
Upgrade Options	Always up-to-date database engine (no need to upgrade).	Always up-to-date database engine (no need to upgrade).	Manual upgrade of the database and the OS.
Virtual Network Support	Yes	No	Yes
Management Cost	$$	$ (Least expensive)	$$$ (Most expensive)
High-Availability and Disaster Recovery	Built-in high availability (HA) and disaster recovery (DR).	Built-in high availability (HA) and disaster recovery (DR).	Manual setup of high availability (HA) through Microsoft SQL features like Database Mirroring or Availability Groups.
Backup/Restore of databases	Administrator-triggered backups are available as well as automated backups from Microsoft.	Microsoft manages all backup/restore operations. No options for incremental or differential backups. All restores are full restores.	Manual backup/restore configuration setup.
Collation Setup	Yes, you can select your collation at setup. It cannot be changed later.	No, LATIN1_General_CI_AS is always used.	Yes, you can select your collation at setup.
Auditing	Azure auditing tool for Azure SQL Database	Azure auditing tool for Azure SQL Database	C2 audits, extended events, SQL default tracing. You can also install third-party software if needed.
Other Unsupported Features	SQL Server Reporting Services, Integration Services, Analysis ServicesDTC, Polybase SQL backups (.bak).	SQL Server Reporting Services, Integration Services, Analysis ServicesDTC, Polybase SQL backups (.bak)SQL Server AgentSQL Browser ServiceFilestreamCommon Language RuntimeResource Governor Global Temporary TablesSQL Trace and ProfilerSystem Stored ProceduresUSE Statement.	All features supported.

Azure SQL Server Managed Instance Option 

The Azure SQL Server Managed Instance option is a Microsoft Azure offering where you have access to a full SQL Server instance without managing the OS and underlying services.

This option gives you the advantage of having nearly 100% compatibility with Microsoft SQL Server Enterprise Editions, which is required for most lift & shift applications.

Key features:

• Quick provisioning and service scalability
• 99.99% Uptime SLA 
• You can select between having a single instance or an instance pool
• You have almost 100% compatibility with SQL Server Enterprise, which will make Lift & Shift Migrations easier
• You get a fully-managed SQL Server Instance, including upgrades, backups, patch management, High Availability, and Disaster Recovery Setup and Operation. However, the administrator manages some operations at the instance level, like TempDB, Logins, and audit logs. 
• You can migrate your databases from any SQL Server 2008 to 2019 using Azure Database Migration Service or Log Replay Service for near-zero downtime.

Security Features: 

• VNETs and Network Security Groups (NSG) to improve security at the network level. 
• Transparent Data Encryption (TDE) support is a built-in feature for Azure SQL Server Managed Instance option and Azure SQL Database, which encrypts data without changing the application. 
• Azure AD or SQL Server Logins Support.
• SQL Server Auditing – Requires configuration 
• Microsoft Advanced Threat Protection Support – Azure SQL Server Managed Instances support Microsoft Advanced Threat Protection out of the box, so you can configure to protect against threats in SQL servers like SQL Injection or brute force SQL password cracking. 

Design your Azure SQL Managed Instance

 Now, let’s look at the configuration options for an Azure SQL Managed Instance. 

VCores required

For Azure SQL Managed Instances, you will use a vCore pricing model. This model is the best option for current SQL Server deployments because you can select the number of cores, memory, and storage needs for your existing applications. 

The available options in this model include: 

• Gen5 (Standard) 
• Premium-Series 
• Premium-Series, Memory Optimized

You can select the number of cores required after selecting the hardware model based on the hardware requirements for your applications. 

Instance Type

You can select if you need a single instance for your managed SQL Server or if you want an instance pool. An instance pool allows you to add multiple instances to a single SQL Server managed pool.

You should use SQL Server Instance Pools to consolidate multiple SQL Server instances from different applications and manage them in a single pool in Azure. 

Here are some guidelines on how to select each option: 

Single Instance	Instance Pool
Only one SQL Server instance is created. It cannot scale up after it is created. Ideal for single-application migrations or testing	Allows creation of  multiple SQL Server instances and assigns vCores based on application needs. It can scale up as needed. Ideal for multiple application migrations where each application has its instance, but you want to manage them all in the same instance pool.

Service Tiers

For the service tiers, you can select between these options: 

• General Purpose- Applications with standard performance and I/O operations.
• Business-Critical- Applications requiring low latency on I/O operations and minimal performance impact for maintenance operations. 

For each service tier, it is essential to understand the critical differences for storage and how high availability is managed, so you can select which option suits your application requirements. Here is a comparison of features.

Feature	General Purpose	Business-Critical
Storage Provisioning	High-Performance Azure BLOB Storage. Up to 16TB maximum storage capacity per instance.	Local SSD (Solid State Drives) for higher I/O performance.
High Availability	Based on Azure BLOB Storage Backup. Provides high availability based on Azure BLOB Storage replication, which gives an enterprise-ready solution for high availability (HA) and disaster recovery (DR).	Based on AlwaysOn Availability Groups and Azure Service Fabric. Provides an enterprise-ready solution to replicate the data into different regions and deliver the best HA solution possible with SQL Server.

Azure SQL Server Database Option

The Azure SQL Database option is the most modern solution from Microsoft for SQL databases, and it is available only through Microsoft Azure. Unlike the Azure SQL Server Managed Instance Option, in this implementation, you only have to manage the databases and some of the features in SQL Server like logins. 

Microsoft handles most instance management, including backups, managing instance memory, sizing data files, working tempdb, and applying patches. You are no longer a sysadmin of the instance, and you administer  database, user, and audit management only.

Key Features: 

• You get a fully-managed SQL Server Instance, including upgrades, backups, patch management, High Availability, and Disaster Recovery setup and operations. 
• Quick provisioning and service scalability
• 99.99% Uptime SLA 
• You can select between a vCore pricing model, DTU Model, or a Serverless model (we will cover these in detail in the next section). 
• You can migrate your databases from the traditional SQL Server using Microsoft’s Data Migration Service, using a .bacpac file or SQL Server Replication. 
• Automatic performance monitoring and tuning with zero intervention from the administrator. 
• Built-in intelligence that allows customers to reduce costs of enhancing security for databases.

Security Features: 

• Firewall security for database access
• Transparent Data Encryption (TDE) support is a built-in feature for Azure SQL Server Managed Instance option and Azure SQL Database, which encrypts data without changing the application. 
• Azure AD or SQL Server Logins – Azure SQL Database can be configured to allow only Azure AD logins for enhanced security. 
• Microsoft Advanced Threat Protection Support – Azure SQL Server Managed Instances support Microsoft Advanced Threat Protection out of the box, so you can configure to protect against threats in SQL servers like SQL Injection or brute force SQL password cracking. 
• In-depth auditing capabilities, including Microsoft operations, configured at Azure Portal. 
• Advanced monitoring and alerting using Microsoft Azure Monitor. 

Designing your Azure Database Model: 

When you decide to use the Azure Database Model, you have different options to enable the service. Below are the options you need to choose from when setting up the service.

Resource Type

The first option you need to select is what resource type you want to use for your Azure Database Service. You can choose between these two:

• Single Database- Select this Option if you want to deploy a single database with dedicated resources for it.  
• Elastic Pool- Select this Option if you want to deploy a pool of resources for multiple databases that share a pool of resources. It is good for shared workloads with variable usage patterns. 

Pricing Model

In Azure Database, there are two pricing models: 

VCore Model– The traditional pricing model works the same way as the Microsoft SQL Server Managed Instance Option. The available options in this model include the following:

• Gen5 (Standard) 
• Premium-Series 
• Premium-Series, Memory Optimized

You can select the number of cores required after selecting the hardware model based on the hardware requirements for your applications. 

For single databases, you have two additional options to choose from:

• Serverless- Compute resources are autoscaled as the application needs them. The price is based on the number of vCores used per second. In this option, you can select the maximum number of cores to scale up and the minimum number of cores to scale down the database. You also have an option to auto-pause the database, which will save money when the database is not in use. However, use this option carefully because it may not perform well at scale. 
• Hyperscale- This Option supports up to 100TB of data. It is reserved for huge database workloads and supports multiple read-scale replicas. Use this option only if you need to have a database server larger than 16TB. Remember you cannot change this option after the database is created. 

Distributed Transaction Unit Model (DTU)– You can select DTU as the pricing model for your single database or elastic pool using Azure SQL Database. A DTU is a measurement of these three main components of the database performance: 

• CPU
• Memory 
• Disk I/O. 

Microsoft created the concept to facilitate the sizing of a database based on this metric, enabling more precise metrics to size a database properly and scale up or down as needed. 

The service tiers available for the DTU model are the following:

• Basic- Used for less demanding workloads. It is often used only for dev and test purposes, and it can only scale up to 5 DTUs.
• Standard- Used for most applications. It is common for cloud applications, such as workgroups or web applications. You can select between 10 to 100 DTUs in this service tier. 
• Premium- Used for high-transaction applications that require high-level business continuity. DTUs can be configured between 125 up to 4000 in this service tier. 

To help you select the best option if you decide to go with the DTU model, the table below breaks down the differences between the three service tiers. 

Description	Basic	Standard	Premium
Best used for	Dev/test databases and low-demand applications.	Most cloud applications, such as workgroups or web applications	High-transaction applications that require high-level business continuity.
Database Size limit	2 GB	250 GB	500GB – 1TB
Point-in-time restore	0 – 7 days back	0 – 35 days back	0 – 35 days back
Max concurrent workers	30	60 – 200	200 – 6400
Max concurrent logins	30	60 – 200	200 – 6400
Max concurrent sessions	300	600 – 2400	2400 – 32000

Here is an example of how to configure the DTU tier and number of DTUs for your single database or database pool: 

If you selected Elastic Pool, you can also set the DTU option. However, sizing is different.

Below is a table per each tier for the elastic pool model for DTU.

Basic
eDTUs per pool	Max Storage	Max Databases	Max eDTUs per database
100	10 GB	200	5
200	20GB	400	5
400	39GB	400	5
800	78GB	400	5
1200	117GB	400	5

Standard
eDTUs per pool	Max Storage	Max Databases	Max eDTUs per database
100	100 GB	200	100
200	200 GB	400	100
400	400 GB	400	100
800	800 GB	400	100
1200	1200 GB	400	100

Premium
eDTUs per pool	Max Storage	Max Databases	Max eDTUs per database
125	250 GB	50	125
250	500 GB	50	250
500	750 GB	50	500
1000	750 GB	50	1000
1500	750 GB	50	1000

DTU Frequently Asked Questions

Before we proceed, let’s address some common DTU questions.

How do I know how many DTUs and service tiers I need for my databases?

Microsoft has created a DTU calculator tool to help you determine which service tier you should use and how many DTUs you need for your database workloads. You can find the Azure SQL Database DTU Calculator here.

How to choose between VCore or DTU?

If you need more control over the VCores assigned to a database or database pool, the VCore option is ideal. However, the DTU model works better if you have pricing constraints because it allows you to purchase a smaller capacity than a full VCore.

As a general rule, 100 DTUs in the Standard tier is equivalent to 1 VCore in the same tier, and 125 DTUs in the premium tier is equal to 1 VCore in the same tier. Also, pricing in DTUs is better when it scales because it is measured in smaller metrics than a VCore.

Our general recommendations are:

• Select the VCore model if your database requires CPU allocation as the primary performance component
• Select the DTU model if your database requires a tight mix of resources between CPU, Memory, and I/O or requires less than 1 VCore.
• Always select the DTU model if you require less than 1 VCore of processing, for example, for Dev/Test databases.

SQL Server on Virtual Machines Option

The SQL Server on Virtual Machines option is the most compatible solution for on-premises migrations to the cloud. You can deploy it with the same configurations as your current on-premises SQL Server instances.

Key features:

• You have complete control of the infrastructure where SQL Server runs.
• You get all the functionality of SQL Server Standard or Enterprise, so you can migrate applications that require features not supported in Azure SQL Database to Azure.
• You get all auditing capabilities at the OS and SQL Server levels.
• You have more granular control over backup and restore operations.
• High-availability and disaster recovery from SQL Server Enterprise included.

Security Features:

• VNet and Network Security Groups (NSGs) can isolate the SQL Servers from the network.
• Azure provides infrastructure-level security.
• Administrators must configure and manage OS-level security and SQL Server security.
• Administrators are responsible for patch management.

Designing your SQL on Virtual Machines Model:

If you decide to go with SQL on the Virtual Machines Model, you will need to design a complete solution that includes:

• Virtual machines
• Managed disks
• Virtual networks
• Storage accounts
• Availability Zone design
• SQL Server high availability setup design
• OS and SQL Server Licenses

Best practices

In summary, here are our recommendations for each of the SQL Server deployment scenarios and deployment options.

Best practice	Azure SQL Server Managed Instances	Azure SQL Database	SQL Server on Virtual Machines
Resource Type	Single Instance: To deploy or migrate from one single instance to Azure. Use it for simple deployments or if you only have one database instance on-premises. Elastic Pool: To deploy multiple SQL Server Managed Instances that can share resources between them. Use it if you have multiple instances on-premises that need to be consolidated in Azure.	Single Database: Use if you want to enable a single application to work with Azure SQL Database or you need HyperScale or Serverless. Elastic Pool: Use if you have multiple applications that can share resources between them.	Resource Types are not available.
Service Tier	General Purpose: For traditional applications that do not require extensive I/O operations. Business Critical: For mission-critical applications that require high performance and high disk I/O.	Basic: Use for dev/test databases. Standard: Use for standard web application databases and workloads. Premium: Use it for mission-critical applications that require high performance and disk I/O.	No service tier is available.
Pricing Tier	VCore Pricing model available only.	VCore model: Use if your database requires CPU performance, and you need control over VCore assignment. DTU model: Use if your Database requires a tight mix of resources between CPU, Memory, and I/O or requires less than 1 VCore.	No pricing tiers are available. Select between each component’s pricing options for virtual machines, managed disks, storage accounts, virtual networks, and licenses.
Security Features	Select it for applications with standard security requirements that can use built-in Azure Security and VNet network security.	Select it for applications with standard security requirements that can use built-in Azure Security Features.	Select it for applications that require special security requirements, like using a specific agent at the OS-level.
Auditing Features	Select it for applications that can use Standard Azure tools for auditing.	Select it for applications that can use Standard Azure tools for auditing.	Select it for enhanced auditing features at the OS and SQL Server levels.
Monitoring and Alerting	Select it for applications that can use standard Azure tools for monitoring and alerting	Select it for applications that can use standard Azure tools for Monitoring and Alerting	Select it for applications that require special software for monitoring and alerting.
High Availability and Disaster Recovery	Select it if you want Microsoft to manage high availability and disaster recovery  with 99.99% SLA.	Select it if you want Microsoft to manage high availability and disaster recovery with 99.99% SLA.	Select it if you want to deploy your own high availability solution.

Conclusion

Selecting between Azure SQL vs. SQL Server is not easy. From performance to cost to operational complexity, many factors influence the decision. Fortunately, with the information in this article, you can make an informed decision for your next SQL Server project.