Hosting SQL Server workloads in the Azure Cloud

Introduction

This article provides a high-level overview of services for hosting SQL Server workloads in the Azure Cloud. Microsoft Azure offers a handful of cloud services for hosting your SQL as well as NoSQL databases and in-memory databases. The supported SQL databases are SQL Server, Oracle database, MySQL, PostGreSQL and MariaDB. Hosting SQL databses in Azure comes in the form of either IaaS or PaaS service. An example of IaaS implementation is running Oracle on Azure. An example of PaaS service is Azure SQL database. Hosting SQL Server workloads in the Azure Cloud comes down to the following options: Azure SQL database (single database or elastic pool), Azure SQL managed instance (MI) and Azure SQL Edge.

Available Azure-managed database solution architectures are presented at: https://azure.microsoft.com/en-us/solutions/databases/#solution-architectures.

Choosing the right option for hosting SQL Server workloads in the Azure Cloud

Hosting SQL Server workloads in the Azure Cloud primary consideration is to choose the proper services which matches your workload requirements. Microsoft offers an article in which they consolidate a comparison of the available SQL, NoSQL and in-memory database options, depending on the usage scenario of each organization. Microsoft also offers a very useful interactive decision making application which asks a few questions to assess you scenario and then comes up with the ideal SQL-based solution in Azure: https://azure.microsoft.com/en-us/products/azure-sql/#choose-your-database.

In a nutshell, the available SQL Server hosting options for hosting SQL Server workloads in the Azure Cloud are the following:

  1. SQL Server on Azure Virtual Machines. Use this option if you already have significant investment in Azure IaaS infrastructure, either fully on Cloud or hybrid on-premise and cloud (e.g. with an existing Active Directory infrastructure you wish to extend). Also use this options if you desire maximum compatibility with older versions of SQL Server which are supported on the Azure-supported VM operating systems. Also choose this IaaS option if you have an existing SQL Server license which you need to retain and make use of the Bring Your Own License option (BYOL) for SQL Server which is called Azure Hybrid Benefit.
  2. Azure SQL Database. Use this option to have a PaaS managed service to host your SQL Server databases at a value for money, without the need for a dedicated instance. An Azure SQL database can fall under the following categories: single database (one database only) and elastic pool (for multiple databases under the same SQL Server instance).
  3. Azure SQL Managed Instance. Use this to have all the benefits of an Azure PaaS service alongside higher availability and better SLA as compared to the Azure SQL database (which of course comes with a higher price than the Azure SQL Database).
  4. Azure SQL Edge. Use this if you need a small-footprint SQL Server instance running in an Internet of Things (IoT) infrastructure.

Bear in mind that it is important to compare the features of Azure SQL database vs Azure SQL managed instance before deciding which is the right option for your scenario (https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison). One such difference is that Azure SQL does not support setting a custom SQL server-level collation. Default server collation SQL_Latin1_General_CP1_CI_AS is always used. Also bear in mind that Azure SQL database can be run in on-premises infrastructure by utilizing Azure Arc for hybrid workloads. See Microsoft article Azure Arc-enabled data services for more details.

SQL Server workload migration design considerations

Hosting SQL Server workloads in the Azure Cloud also brings about discussions on SQL workloads migration to Azure. Migrating SQL Server workloads to Azure can be accomplished by utilizing the Azure Migrate service and the Azure Database Migration Service. The Azure migrate service should not be confused with Azure Site Recovery (ASR) which covers DR scenarios. A comparison between Azure Migrate and ASR is presented in the following article: https://techcommunity.microsoft.com/t5/itops-talk-blog/azure-site-recovery-v-azure-migrate-server-migration/ba-p/997689.

First-off there are certain Azure SQL service limitations which come by design and you should be aware of them. You should take into account the following limitations:

  1. Azure SQL resource limits for a single database (vCore model)
  2. Azure SQL resource limits for a single database (DTU model)
  3. Azure SQL resource limits for an elastic pool (vCore model)
  4. Azure SQL resource limits for an elastic pool (DTU model)
  5. Azure SQL resource limits for a logical server
  6. Azure SQL Managed Instances (MI) resource limits

Secondly, you need to follow the Microsoft wizard for choosing the proper Azure SQL Server service, depending on your scenario. If you go for the IaaS model, then choose the VM size wisely depending on your needs. If you go for a PaaS SQL solution, choose your pricing model wisely (vCore vs DTU model). Each pricing model has its limitations, as mentioned in the above section. You need to properly size your implementation, either with the vCore or the DTU puchasing model. The Database Transaction Unit (DTU) purchasing model is in essence a combination of the concepts of CPU vCores, RAM memory, and storage IOPS into a standardized measurement unit which is used for benchmarking and billing Azure SQL database instances. Microsoft DTU calculator is a handy tool which helps administrators calculate the costs of an Azure SQL hosted instance. Further guidance on using the MS DTU calculator can be found at: https://www.mssqltips.com/sqlservertip/5606/how-to-use-the-azure-sql-database-dtu-calculator/.

Finally, you need to utilize the Azure Database Migration Service to make the migration process as smooth and error-free as possible. The first thing to do before planning your migration is to run the Data Migration Assistant (DMA). The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.

More detailed Azure SQL migration planning articles and design considerations for Azure SQL will follow as separate blog posts.