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 possibile options, what to pay attention to when making this choice and which database we recommend for which situation.

A question we get asked often at Intercept, when we transform an application to Azure, is what SQL database to use. Microsoft offers three distinct SQL offerings and choosing between them is not always easy. In this article we will tell you more about the possibile options, what to pay attention to when making this choice and which database 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 will be able to achieve full compatibility with legacy on-premises SQL installs.

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. If you also need full control over the operating system or compatibility with on-premises workloads such as SQL Server Reporting Services you can run SQL Server on an Azure VM. The following SQL statement provides a simple high level guidance when choosing the correct SQL product in Azure:



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 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 compliancy 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 for example 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 will need to patch regularly. Our advice is to use 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. If you have any questions regarding which SQL database fits with your situation? Feel free to contact us.

Tags

  • Azure Fundamentals
  • ISV Roadmap

Possibly interesting as well: