SQL Server in Azure database choices and what they offer users

SQL Server on Azure VMs

The most straightforward way to deploy SQL Server in Azure is to put it on an Azure VM. To do so, you must have an Azure account. The step is to create and size an Azure VM. Next, you'll need to install an OS and SQL Server on the VM and then migrate your databases to it.

To speed up this process, Azure Marketplace has several VM images with SQL Server and either Windows Server or Linux preinstalled. For example, the Windows-based SQL Server 2017 image is highlighted in the Azure portal screen shown in Figure 1.

Selecting this option enables you to quickly create a SQL Server 2017 VM running on Windows Server 2016 without needing to manually install either the OS or the database software. There are also images for SQL Server 2017 on Linux and the preview release of SQL Server 2019, as well as earlier versions of the database management system (DBMS).

Azure VMs offer up to 99.95% availability, and you have full control over the VM's configuration and the SQL Server instance. Because you're deploying your own SQL Server instance, there's complete feature parity with the on-premises version of SQL Server -- they're essentially the same thing. You're also able to deploy other applications or services on the VM host that's running SQL Server, and you can provide your own private IP addresses to the VM using Azure Virtual Network (VNet).

While SQL Server systems running on Azure VMs provide the same degree of control as on-premises ones, they also have the same type of operational and management requirements. You don't have to – and, in fact, can't -- manage the underlying hardware, but you do need to manage database backups and software updates.

You also need to implement whatever high availability (HA) and disaster recovery tools your SQL Server instance requires, and you're responsible for reconfiguring system resources, like CPU and storage, in an Azure VM, which will result in database downtime. You can manage SQL Server VMs in Azure using SQL Server Management Studio (SSMS), the more lightweight Azure Data Studio tool, SQL Server Data Tools (SSDT) in Visual Studio or the Visual Studio Code editor with the mssql extension.

Azure VMs are priced under a pay-as-you-go model that charges by the second for the use of compute capacity or an Azure Reserved VM Instances option that lets users make an advance purchase of a VM for one year or three years at a reduced capacity rate. VMs are available in a number of sizing categories that are designed for different budgets and workloads.

Migration from on-premises SQL Server to Azure VMs is relatively easy and can be accomplished in a variety of ways:

• copying a database backup to an Azure VM;
• using Microsoft's Backup to URL feature, in which a database is backed up in Azure Blob storage and then restored to a VM;
• detaching the data and log files, copying them to Azure Blob storage and then attaching them to a VM;
• converting an on-premises physical system or VM to a Hyper-V virtual hard disk, uploading the VHD to Azure Blob storage and then deploying it on a Azure VM;
• using an Always On availability group to create a replica database in an Azure VM; and
• running Data Migration Assistant (DMA) to migrate a database to an Azure VM.

Azure SQL Database

Azure SQL Database, Microsoft's PaaS option for SQL Server users in the cloud, is more specifically a database as a service (DBaaS) offering. With Azure SQL Database, you needn't be concerned with configuring the underlying system, creating VMs or installing an OS and the database software itself. Instead, Microsoft handles all of the platform management and is responsible for security, availability, and backups.

One of the big advantages of the PaaS approach is that you can get cost savings on both capital and operating expenses. Azure SQL Database also takes a lot of the heavy lifting out of a SQL Server in Azure implementation. Microsoft provides database monitoring and automatic performance tuning, and the cloud service automatically does full, differential and transaction log backups in databases and supports point-in-time recovery.

Azure SQL Database guarantees 99.99% availability and uses the latest stable version of SQL Server Database Engine, although Microsoft's default compatibility level settings can be used to align existing databases with older versions of the DBMS if you want to do so.

You can implement Azure SQL Database as a single database or an elastic pool. As its name implies, a single database consists of just one database with its own set of resources -- similar to using an on-premises contained database. An elastic pool is a collection of databases with a shared set of resources. Elastic pools are designed for managing multiple databases with usage levels that vary in unpredictable ways -- they make sure that the databases get needed processing resources when demand increases.

You can scale up both single databases and elastic pools with no interruption of service. Both also enable you to configure up to four readable secondary databases to use as a failover group for HA and load balancing. As in SQL Server on Azure VMs, you can use Azure VNet to assign private IP addresses to Azure SQL Database systems.

For database development and management in Azure SQL Database, you can use the Azure portal in addition to SSMS, Azure Data Studio, SSDT and Visual Studio Code. The Azure portal screen for creating a database is shown in Figure 2.

Azure SQL Database has a high level of compatibility with on-premises SQL Server. However, a number of on-premises features aren't supported in the cloud service. For example, you can't attach a database to an Azure SQL Database instance or run manual backup commands. Change data capture, Common Language Runtime extensions, Database Mail, database snapshots, distributed partitioned views, linked servers and SQL Server Agent are some of the other features that aren't supported.

Microsoft offers two pricing models for Azure SQL Database. The original one is a pay-as-you-go model based on Database Transaction Units (DTUs), a resource utilization metric created by Microsoft. The DTU-based model includes three service tiers to support light to heavy database workloads. Different system sizes within each tier provide a preconfigured mix of compute, and storage resources.

In 2018, Microsoft added a vCore-based pricing model that allows you to choose the number of virtual processor cores and the amount of and storage needed to run your database workloads. Microsoft now recommends use of the vCore model, saying it offers a more straightforward way to translate on-premises SQL Server workload requirements to Azure SQL Database. Under the vCore approach, customers with existing SQL Server licenses and Software Assurance maintenance contracts can also use the Azure Hybrid Benefit pricing discount to get cost savings; additionally, a reserved capacity option enables companies to prepay for vCores for one year or three years at a lower rate.

The recommended options for migrating on-premises SQL Server databases to Azure SQL Database are more limited than the ones for moving to SQL Server on an Azure VM. You can use DMA, either separately or as part of Azure Database Migration Service, or you can use transactional replication, in which Azure SQL Database is set up to act as a subscriber to an on-premises SQL Server instance that continuously publishes changes to database tables.

Azure SQL Database managed instance

The Azure SQL Database managed instance deployment option became available in 2018. It's a fully managed DBaaS offering that gives SQL Server in Azure users all the PaaS benefits of Azure SQL Database. However, it also includes most of the additional on-premises capabilities that were previously available to cloud users only in SQL Server on Azure VMs.

Azure SQL Database managed instance provides near-100% compatibility with on-premises SQL Server, according to Microsoft; a managed instance in Azure is similar to an on-premises instance. There are still some differences in functionality, though -- for example, in backups, HA and security. Also, tools like SQL Server Analysis Services, Integration Services and Reporting Services aren't built in, although their functions can be provided through other Azure services.

There are two different service tiers for Azure SQL Database managed instance -- a General Purpose one for applications with typical workloads and a Business Critical tier for workloads that require high performance and low I/O latency. A managed instance can host up to 100 databases on as much as 8 TB of storage in the General Purpose tier and 4 TB in the Business Critical one, depending on the selected performance level and the number of vCores configured in a system. Like the single database and elastic pool versions of Azure SQL Database, the managed instance offering provides 99.99% availability and supports private IP addresses through Azure VNet

The primary goal of Azure SQL Database managed instance is to make it easier to migrate your SQL Server instances to Azure. Currently, that could be a particularly appealing option to companies still running SQL Server 2008 and SQL Server 2008 R2. Microsoft ended support for those two versions of the DBMS on July 9, 2019, and many of the remaining installations run on aging hardware platforms. Azure SQL Database managed instance supports migration from SQL Server 2008 and 2008 R2, as well as newer SQL Server versions, with minimal or no database changes.

As shown in Figure 3, you can create a new managed instance in the Azure portal by selecting Create a resource, then Databases, followed by Azure SQL Managed Instance.

Azure SQL Database managed instance uses a vCore-based pricing model with two compute levels built around different processors, one supporting eight to 24 vCores and the other four to 80. As with the other Azure SQL Database deployment options, you can take advantage of the Azure Hybrid Benefit discount if you have Software Assurance with existing SQL Server licenses. You can also pay upfront for reserved capacity at a discount rate.

Microsoft supports two methods of migrating databases to Azure SQL Database managed instance for users looking to run SQL Server in Azure. You can restore a database from a native SQL Server backup that you copy to Azure storage, or you can use Azure Database Migration Service with DMA.