Microsoft Azure Cloud offers broadly 3 different options to deploy Microsoft SQL server on Azure:
- SQL Server on Azure VM (IaaS),
- Azure SQL Database (PaaS) and
- Azure SQL Managed Instance (PaaS).
To choose the right Azure deployment option for your database needs, you need to review your requirements and the level of effort you want to put into managing your SQL server.
Three Options for Deploying SQL on Microsoft Azure
SQL Server on Azure VM is based on the Infrastructure as a Service (IaaS) model. SQL Server on Azure Virtual Machines offers full administrative control over the SQL Server instance and underlying OS for migration to Azure. As an IaaS solution, SQL Server on Azure Virtual Machines runs on standardized hardware that is owned, hosted, and maintained by Microsoft. However, while Microsoft manages the infrastructure, you (the customer) must manage the SQL server completely. This deployment model is comparable to an on-premises SQL server. All patches, upgrades, and maintenance of SQL server are the responsibility of the customer.
You can either pay-as-you-go for a SQL Server license already included in a SQL Server image or use existing SQL Server and Windows Server licenses.
Azure SQL Database is a Platform as a Service (PaaS) offering that provides pre-provisioned or serverless compute and Hyperscale storage to meet demanding workloads requirements. This can be the right choice for a variety of modern cloud applications because it enables you to process both relational data and non-relational structures, such as graphs, JSON, spatial and so on.
Azure SQL Database offers the following deployment options:
- As a single database with its own set of resources managed via a logical SQL server. A single database is like a contained database in SQL Server.
- An elastic pool, which is a collection of databases with a shared set of resources managed via a logical server. Single databases can be moved into and out of an elastic pool. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.
Azure SQL Managed Instance is a Platform as a Service offering (PaaS), which is a hybrid of the previous two options, designed to provide easy compatibility with the latest SQL Server (Enterprise Edition) Database Engine used for on-premises databases. The benefit is that you can lift-and-shift on-premises SQL Servers and applications to Azure with minimal changes. These instances enable you to deploy a native virtual network (VNet) that imitates on-premises deployments and increases security, while Azure SQL Database enables restricted Virtual Network (VNet) access using VNet Endpoints.
While Azure takes care of the management of the resources with SQL server latest editions, patching and backup, it also provides a few monitoring options that helps you to determine whether your database has excess capacity or is having trouble because resources are maxed out, and then decide whether it’s time to adjust the performance level and service tiers of your database. However, it has limited visibility to identify the application access issues on the database.
eG Innovations supports monitoring models for all Azure SQL options:
|Microsoft Azure Services||eG Monitoring Component|
|Azure SQL Databases||Microsoft Azure SQL|
|Azure SQL Managed Instance||Microsoft SQL Managed Instance|
|SQL Server on Virtual Machines||Microsoft SQL|
When to Choose SQL Server on Azure VMs vs. Azure SQL (Azure SQL Database or Azure SQL Managed Instance)
Microsoft has some very helpful guidance available (see: Tips to select the best Azure SQL deployment option – Microsoft SQL Server Blog) to help you make this decision. In brief, their advice is to use Azure SQL Database or Managed Instance for your modern cloud applications, or when you have time constraints in development and marketing.
There are some scenarios where SQL Server on Azure Virtual Machines may suit some users best and Microsoft recommend you use SQL Server on Azure Virtual Machines when you are migrating applications that require OS-level access or you are moving applications that require a fast migration and minimal changes. SQL Server on Azure Virtual Machines is also ideal for rapid development and test scenarios when you don’t want to invest in on-premises non-production SQL Server hardware. In particular, you may also benefit from using SQL Server on Azure Virtual Machines if you have one of the following specific scenarios:
- You need control over your database and operating system and have dependencies on SQL Server Reporting Services.
- You have independent software vendor (ISV) applications certified on particular SQL Server releases, particularly legacy versions that may be years old.
It is worth investigating some of the detailed advice on the SQL options available on Microsoft Azure, particularly the information in What is Azure SQL? – Azure SQL | Microsoft Docs, which will guide you through factors that should affect your decisions including SLAs, cost, administration overheads and time to deploy. It is worth noting that Azure manages your databases and guarantees their high-availability. Some features that might affect high-availability or can’t be used in PaaS world have limited functionalities in SQL Database and SQL Managed Instance, and experts familiar with T-SQL may like to check out:
If you have decided to use a SQL PaaS option and are trying to decide between Azure SQL Database and Azure Managed Instances, you should follow the guidance in, Compare the database engine features of SQL Database and SQL Managed Instance – Azure SQL Database & SQL Managed Instance | Microsoft Docs.
If you are planning an SQL deployment in Azure, you may like to read my other article covering how to monitor SQL performance on Microsoft Azure and how eG Enterprise can help troubleshoot issues.
- There is a wealth of community information around SQL deployment options on Microsoft Azure available, articles such as Azure — Difference between Azure SQL Database and SQL Server on VM | by Ashish Patel | Awesome Azure | Medium and Choosing between Azure SQL, SQL Managed instance or SQL Server | Intercept | Empower software driven companies with Microsoft Azure may be of particular interest.
- SaaS vs PaaS vs IaaS: Examples, Differences and How To Choose | eG Innovations
- For more information on eG Innovations wider portfolio of monitoring support for Azure services, please see: Azure Cloud Monitoring Tools & Solutions | eG Innovations
- For information on eG Enterprise’s support for on-premises Microsoft SQL Server deployments, see: Microsoft SQL Server Performance Monitoring | eG Innovations
- Tips to select the best SQL deployment option – Microsoft SQL Server Blog
- Compare the database engine features of SQL Database and SQL Managed Instance – Azure SQL Database & SQL Managed Instance | Microsoft Docs