Cloud-based database software adds new IT options for users

geometrix - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

How to choose the right SQL Server on Azure deployment option

There are two options for deploying SQL Server on the Azure cloud. Here's a look at how they differ to help you choose between using Azure SQL Database and running SQL Server on an Azure VM.

Organizations looking to run SQL Server on Azure have two options: installing SQL Server on an Azure virtual machine or using Azure SQL Database. Although both approaches can be used to host SQL Server databases in the Microsoft Azure cloud, there are some real differences between them.

Azure SQL Database is Microsoft's platform as a service (PaaS) offering for SQL Server users. As such, it's primarily geared toward organizations that want to run databases in the cloud without having to worry about all the administrative tasks that are normally associated with managing and maintaining SQL Server. Microsoft handles all the routine maintenance tasks, such as patch management, as part of the cloud service.

The tradeoff for this convenience is that SQL Database isn't as feature-rich as SQL Server. For example, Microsoft doesn't expose the underlying Windows Server operating system or SQL Server itself. As such, database administrators (DBAs) can't make configuration changes to the OS, and the options to manage SQL Server are very limited.

It's also worth noting that SQL Database is designed to be managed primarily from a command line, although some GUI-based management is possible. Microsoft released a browser-based query editor for the cloud platform in early 2017, and you can use SQL Server Management Studio to connect to it and then query and manage data via Transact-SQL statements.

In addition, Azure SQL Database is limited with regard to the maximum database size that it can support. Until recently, the size limit was 1 TB, although a larger database could be partitioned to fit. Microsoft increased the size limit to 4 TB in March 2017, but that's still much smaller than SQL Server's maximum size, both on premises and in the cloud.

Configuration and cost calculations

Because SQL Database is sold as a service, it doesn't require traditional software licensing. Instead, subscribers are billed at a flat hourly rate, with an additional charge for outbound data transfers to Azure systems in different geographic regions. However, its flat rate billing isn't as simple as it might seem.

Microsoft offers four different service tiers: Basic, Standard, Premium and Premium RS. To select the correct tier and the proper configuration, an organization must determine the storage capacity, system availability and performance level required by the workload.

Performance is based on the number of Database Transaction Units (DTUs), a mixed measurement of CPU, memory and I/O usage. Different combinations of DTUs and storage are available at varying hourly prices. Users can also group multiple databases into elastic pools, with pricing pegged to an overall measurement of elastic DTUs, or eDTUs.

An example of an Azure SQL Database
An example of an Azure SQL Database.

There are two main use cases for deploying SQL Server on Azure SQL Database. First, an organization might choose to use the cloud service if it lacks the IT staff to support a traditional SQL Server deployment, or if it is trying to reduce the administrative burden on its DBAs. Second, SQL Database is the platform of choice for organizations that need to reduce development time on an application.

Running SQL Server on Azure virtual machines (VMs) is similar to running SQL Server on premises. As with any other VM, you'll have full access to the virtual machine's contents, including the operating system. Microsoft makes SQL Server VM images readily available through the Azure portal under either a new pay-per-minute license or reuse of an existing on-premises one. Alternatively, you can opt to install SQL Server on a VM yourself, utilizing one of your own licenses.

Looking at the good side of using VMs

Although running SQL Server on an Azure VM is clearly the best choice for organizations that need to maintain full control over the database software and its underlying operating system, there are a few other advantages to running SQL Server within a VM.

The first is familiarity. A SQL Server instance running on an Azure VM looks and behaves similarly to an on-premises SQL Server system. DBAs are able to use all the same management tools that they're already used to, and in the same ways.

A second advantage to running SQL Server on Azure VMs is that this approach can accommodate larger databases than Azure SQL Database. An Azure VM can be provisioned with up to 64 TB of database storage, a 16-fold increase over SQL Database's 4 TB maximum.

Finally, running SQL Server on a VM is often the best choice for organizations that have existing SQL Server databases they want to migrate to the cloud. It may be possible to migrate an on-premises database to Azure SQL Database, but moving to a SQL Server instance that is running on an Azure VM is almost always an easier option.

As you can see, there are fundamental differences between Azure SQL Database and SQL Server running on an Azure VM. Neither approach is superior to the other in every situation. Even within a single organization, it's common for some cloud-based workloads to be run on SQL Server, while others run on SQL Database. The decision as to which platform to use should ideally be based on a particular workload's requirements rather than a blanket corporate policy.

Next Steps

SQL Azure infrastructure as a service or PaaS?

How has Azure on SQL evolved?

Migrate to Azure SQL database as a service

Dig Deeper on Microsoft SQL Server Tools and Utilities