Choosing the right SQL Database Option in Azure

Microsoft Azure offers a variety of database options to support and fit the requirements of an organization of any size and domain. The three main types of database options that Azure supports are as follows:

1. Relational Databases: Azure SQL Database, Azure SQL Managed Instance, SQL Server on Virtual Machine, Azure Database for PostgreSQL, Azure Database for MySQL, Azure Database for MariaDB

2. Non-Relational Database: Azure Cosmos DB

3. In-Memory Database: Azure Redis Cache

Microsoft SQL Server is one among the leading database platforms that most organizations use to store their relational databases. With several organizations modernizing their existing infrastructure to migrate over to cloud, it is important to understand the various SQL offerings provided by Microsoft Azure.

What to expect?

In this blog, I will explain the various SQL offerings that are available in Microsoft Azure suite along with its key features, which would eventually help organizations to make the right decision, while performing database migration.

Azure SQL Options

The three main SQL offerings that are available in Microsoft Azure are:

1. Azure SQL Database: This is a Platform-as-a-Service (PaaS) offering by Azure that can be used to create a high-performance storage layer. They can be deployed either as a standalone single database or as an elastic pool, which is a collection of several single databases with shared resources such as CPU, memory etc.

2. Azure SQL Managed Instance: This is a fully-managed PaaS offering by Azure that provides SQL Server capabilities such as Database Mail servers, Linked Servers, Service Brokers, SQL Server Agents, SQL Server Auditing etc., which are not available in Azure SQL Database, hence making it easier for customers to migrate SQL databases with minimal changes. The Virtual Network integration feature allows the service to access resources within an Azure Virtual Network securely.

3. SQL Server on Virtual Machine: This is an Infrastructure-as-a-Service (IaaS) offering by Azure, where the virtual instance comprises of a SQL Server edition that can be accessed through SSMS. This service is used by organizations who would prefer to lift-and-shift their on-premises SQL Servers into Azure and have more control over the compute infrastructure of the databases.

Choosing the right option

The following 7 key areas will help an organization to decide the right SQL database service in Azure, based on their requirements.

Administration: In terms of administration and management, both Azure SQL Managed Instance and Azure SQL Database require reduced time as they are PaaS services, hence backups, patching, updates etc. are regularly performed by Microsoft. SQL Server on Virtual Machines on the other hand would require more administration, as the database engine infrastructure is managed by the customer.

Availability: Azure SQL Managed Instances have a service-legal agreement or SLA of 99.99% for every managed database. Azure SQL Databases have an SLA of 99.99 - 99.995%, whereas SQL Server on Virtual Machines will have an SLA of 99.9 - 99.99% depending upon the virtual machine being hosted within an availability set, zone, or none.

Backup: Both Azure SQL Managed Instance and Azure SQL Database provides automatic backups. These backups cannot be accessed by the user, as they are managed by Microsoft. The data is encrypted at rest with no possibility of any ransomware attacks affecting the databases. The backups are of 3 types:

• Full Backup: Complete backup including database and transaction log files taken every 7 days.

• Differential Backup: Captures only latest changes or inclusions that were made since the last full backup. This backup is taken every 12 hours.

• Transaction Log Backup: Captures log files every 5 to 10 minutes in an incremental format.

SQL Server on Virtual Machine also provides three types of backups:

• Automated Backup: Scheduled automated backups, that are stored in Azure storage for up to 30 days. Supports SQL versions 2014, 2016, 2017 and 2019.

• Azure Backup for SQL VMs: Provides an enterprise class backup capability where backups of several databases can be managed and restored to a specific time, with retention policy to maintain backups for years. Supports SQL versions 2008, 2012, 2014, 2016, 2017 and 2019.

• Manual Backup: Backups of SQL Servers can be performed manually using SSMS and or using SQL commands. Supports all SQL Server versions.

Capacity: Azure SQL Databases support database capacity of up to 100 TB. Azure SQL Managed Instance has a database capacity of 16 TB, whereas SQL Server on Virtual Machines provides 256 TB of storage capacity.

Cost: Being an IaaS option, SQL Server on Virtual Machines would require additional time and resource for managing databases. The cost here will depend upon the virtual machine’s size and SQL Server license. Azure SQL Database provides database transaction units or DTU-based purchasing models, which is a measure of CPU, memory, number of reads and writes. Using elastic pools for shared resources, costs for Azure SQL Databases can be significantly reduced. Both Azure SQL Managed Instance and Azure SQL Database provides vCore-based purchasing model, where the organization pays for compute resources, type and amount of data being stored. This model also provides pricing discounts with Azure Hybrid Benefits and Reserved Instance.

Data Accessibility: Data that resides on any database within Azure can be accessed from an on-premises application using appropriate networking services such as an Azure VPN Gateway or Express Route. SQL Server on Virtual Machines and Azure SQL Managed Instance provides linked server capabilities allowing the service to read data from remote data sources.

Ease of Migration: SQL Server on Virtual Machine has similar compatibility with on-premises SQL Server editions, hence making it easier for organizations to ‘lift-and-shift’ their existing on-premises servers to Azure cloud. However, this comes at an ongoing management overhead, cost, and time, as the backend infrastructure would be managed by the customer. Azure SQL Managed Instance brings the best of both on-premises and Azure SQL Database servers, hence being a prime choice for majority customers. For a cost effective and less management overhead option, Azure SQL Databases will be a good choice as it provides both isolated and elastic pool options for database deployments.

Summary

Azure SQL Database – Suitable option for organizations that require an isolated or even shared databases (elastic pool option) that is cost effective, less management overhead, provides automated backups, regular updates & patching. This option might require minimal code refactoring while performing migration.

Azure SQL Managed Instance – Combined features of both Azure SQL Database & on-premises SQL Server. It’s VNet integration feature allows the service to access resources within an Azure Virtual Network securely. Provides on-premises SQL Server features such as Database Mail Servers, Linked Servers, Service Brokers, SQL Server Agents, SQL Server Auditing etc. This option might require minimal code refactoring while performing migration.

SQL Server on Virtual Machine – Suitable option for organizations who require complete compatibility with on-premises SQL Servers and control over the compute infrastructure of databases. Best for lift-and-shift migrations with minimal to no changes.