Choosing between Azure SQL, SQL Managed instance or SQL Server

A question we get asked often at Intercept when we transform an application to Azure, is what SQL database to use. In this article, we will tell you more about the possible options, what to pay attention to when making this choice, and which database we recommend for which situation.

Microsoft offers three distinct SQL offerings and choosing between them is not always easy. In this article, we will tell you more about: 

  1. The possible options;
  2. Where to pay attention to when making this choice;
  3. Which database do we recommend for which situation;

The following SQL deployment options are available

Azure SQL

Azure SQL is a cloud-based service (PaaS) built along the lines of SQL Server, and thus shares many features with on-premises SQL Server;

SQL Managed instance

Azure SQL Managed Instance is a managed database service that offers a near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine;

SQL Server

Azure fully supports running any edition of SQL Server on IaaS (VM). Combined with ‘Always On’ availability groups, you can achieve full compatibility with legacy on-premises SQL installs.

Azure SQL database vs Managed Instance vs SQL server

Running SQL in Azure makes a lot of sense and over a million on-premises SQL servers have already moved to Azure. Modernizing your application provides the opportunity to transform your data architecture. Azure SQL allows you to benefit from almost unlimited cloud scalability and has high availability out of the box. You can run SQL Server on an Azure VM if you also need complete control over the operating system or compatibility with on-premises workloads such as SQL Server Reporting Services.

The following SQL statement provides simple high-level guidance when choosing the correct SQL product in Azure:

Benieuwd wat we voor u kunnen betekenen?

Sign up for our Azure Fundamentals workshop for ISVs

All the knowledge you need to get started with Azure in one day.

Azure vs the competition

Azure emerged as the clear leader across both Windows and Linux for mission-critical SQL workloads. Microsoft SQL Server on Microsoft Azure shows a 3.6x better performance on Windows than SQL Server on AWS EC2 and up to 84.2% better price performance:


Source

Benefits of each deployment option

We do have some takeaways when investigating the different SQL options available in Azure.

  • For Azure SQL have a look at Failover groups to manage replication and failover of databases to another region. This feature was designed to simplify the deployment and management of geo-replicated databases at scale.
  • Also when you have a lot of databases, placing them in Elastic pools can be a simple and cost-effective solution. When looking at compliance have a look at Azure SQL Auditing and Transparent data encryption.
  • When you transform your application and would like to use Azure SQL, do not forget to mitigate connection errors and transient errors that your client application encounters when it interacts with Azure SQL Database. Adding robust retry logic should be top of mind.
  • If your application doesn’t support Azure SQL and you are looking to use SQL Managed instance, please have a close look at the resource limits. We have some great experience with SQL Managed instance compatibility, but only when you are not hitting IOPs limits.
  • Full compatibility is achieved when running SQL Server on a Windows or Linux VM. When exploring this option, make sure you are using Always On availability groups to ensure high availability. As you will now be using VMs, you must patch them regularly. We advise using Cluster-Aware Updating from the get-go as adding that later might be much more difficult.
  • To keep cost in control consider using Azure Reservations and Hybrid Use Benefit.

Execute a complete migration with near-zero downtime

If you want to analyze and automate your database migration to Azure, Microsoft has your back. The Azure Database Migration Guide offers guidance when moving from on-premises SQL Server, Oracle, MySQL, MongoDB, and more. From the pre-migration steps (discover, assess, and convert) to the actual migration.

Microsoft also offers the Azure Database Migration Service which enables seamless migrations from multiple database sources to Azure Data platforms with minimal downtime. Choose your database by filling in this questionnaire. 

Questions about SQL Database? Come to Intercept

If you have any questions regarding which SQL database fits your situation? Feel free to contact us. Or come to our Azure Fundamentals workshop where we will tell you more about Azure SQL Databases.

Tags

  • Azure Fundamentals
  • ISV Roadmap

Written by

Rinie Huijgen

Rinie Huijgen

CTO at Intercept

Possibly interesting as well: