The Azure SQL portfolio

Microsoft’s Azure SQL is a portfolio of products and services powered by the SQL Server database engine. The portfolio is made up mostly of cloud services that enable customers to store and manipulate their relational data, much like they can with on-premises SQL Server. The services offer secure, scalable environments that support many of the same features as SQL Server, while providing a high degree of compatibility across products, making it easier to migrate workloads from on-premises systems to the cloud.

The Azure SQL offerings are built on the same database engine as SQL Server 2019 and share the same core components. As a result, developers and DBAs can use many of the tools and resources they’re already familiar with to work in these environments. They can also use T-SQL to store and manipulate data and define the schema. Although there are minor differences in how the language is implemented, most applications can interface with Azure SQL platforms much like they do with on-premises SQL Server.

The Azure SQL portfolio currently includes the following four products:

• SQL Server on Azure Virtual Machines
• Azure SQL Managed Instance
• Azure SQL Database
• Azure SQL Edge

Although the names provide some indication of what each product offers, the differences between them are not always apparent, especially when it comes to SQL Managed Instance and SQL Database. Unfortunately, the information coming from Microsoft is often mired in marketing hype and embellished descriptions, making it difficult to understand exactly what each product offers or how to distinguish one from another. This article attempts to bring clarity to the discussion by providing an overview of the products, while digging into some of the details about how they work.

SQL Server on Azure Virtual Machines

SQL Server on Azure Virtual Machines—also referred to as Azure SQL VMs—is infrastructure as a service (IaaS) that provides a virtualized platform for running SQL Server. Microsoft manages the hardware, and you manage the software, much like a DBA manages an on-premises SQL Server environment. With Azure SQL VMs, you control the platform down to the operating system (OS) level.

Azure SQL VMs also provides several features to help streamline operations. For instance, you can use Automated Patching to define a maintenance window for installing Windows and SQL Server updates, or you can use Automated Backups to copy your databases to blob storage. Microsoft also offers business continuity features that provide disaster recovery and ensure high availability.

When setting up your Azure SQL VMs environment, you can choose from an extensive collection of VM images. Each image provides a specific environment based on the SQL Server version and edition and the guest OS. For example, you can choose an image that includes the SQL Server 2019 Web edition running on Windows Server 2019 or the SQL Server 2019 Standard edition running on Red Hat Enterprise Linux (RHEL) 7.4.

Microsoft offers images for multiple SQL Server versions, starting with SQL Server 2008 R2 SP3 and all the way up to SQL Server 2019. In fact, SQL VMs is the only way to run out-of-support SQL Server versions and still get Microsoft security updates. Each version is tied to one or more specific guest OSes. For example, if you want to use SQL Server 2019, you can choose an image based on Windows Server 2019, Ubuntu 18.04, RHEL 8, or SUSE Linux Enterprise Server (SLES) v12 SPS. The following table shows the OSes available to each SQL Server version.

SQL Server version	Supported operating systems
SQL Server 2019	Windows Server 2019, Ubuntu 18.04, RHEL 8, SLES v12 SP5
SQL Server 2017	Windows Server 2016, Ubuntu 16.04 LTS, RHEL 7.4, SLES v12 SP2
SQL Server 2016 SP2	Windows Server 2016
SQL Server 2014 SP2	Windows Server 2012 R2
SQL Server 2012 SP4	Windows Server 2012 R2
SQL Server 2008 R2 SP3	Windows Server 2008 R2

SQL Server on Linux supports most of the core components you get with SQL Server on Windows, such as SQL Server Agent and full-text search. However, only RHEL and Ubuntu come with SQL Server Integration Services (SSIS).

Edition availability also depends on the SQL Server version and licensing model. For example, the Enterprise and Standard editions are available to all versions and licensing models, including the bring your own license (BYOL) model. However, the Developer edition is available only to SQL Server 2016 SP2, SQL Server 2017, and SQL Server 2019 and plays no role in the BYOL model (because SQL Server Developer is free).

In addition to selecting a VM image, you can also choose the VM size, which determines the number of virtual cores (vCores) and amount of memory available to the VM. Plus, you can choose the disk type, amount of storage, high-availability features, management, and monitoring options, and a variety of other settings.

When setting up your environment, you can also select payment and licensing options, which vary depending on subscription type or whether you’re bringing your own SQL Server license. Your subscription fees also depend on the selected VM image and type. Because there are so many options from which to choose, estimating the costs of the various configurations can be a fairly cumbersome process, unless you know exactly which configuration you’re after.

Azure SQL Managed Instance

Azure SQL Managed Instance is a platform as a service (PaaS) offering that’s nearly 100% compatible with the latest SQL Server Enterprise edition, making it easier for customers to move to Azure with minimal application or database changes. Unlike Azure SQL VMs, SQL Managed instance is both a fully managed service and an evergreen service, which means that Microsoft automatically updates and patches the environment continuously.

SQL Managed Instance includes built-in high-availability features, promises 99.99% uptime, and provides automatic backups to help protect data. The platform also supports the Azure Resource Manager API, making it possible to automate service provisioning and scaling.

SQL Managed Instance offers two levels of isolation for protecting data. The first is at the compute level, ensuring that a customer’s instances and databases don’t share VMs with other customers. The second level of isolation is at the network level. In this case, SQL Managed Instance runs a customer’s workloads in a virtual network to keep them separate from other workloads. SQL Managed Instance also provides transparent data encryption, supports Azure Active Directory authentication, and has been certified against multiple compliance standards.

Microsoft offers SQL Managed Instance in two service tiers:

• General purpose. Suited to business applications with typical performance and latency requirements.
• Business critical. Suited to business-critical applications that require high performance, low latency, and minimal disruptions during maintenance. This tier comes with fast local SSD storage and supports In-Memory OLTP.

As part of the setup process, you can choose the number of vCores and amount of storage. You can also select a collation, configure backup storage, and set a maintenance window for when updates should be applied. Microsoft offers multiple pricing options, with fees depending on the selected services. This is similar to Azure SQL VMs, except that estimating your monthly costs is somewhat easier with SQL Managed Instance because you’re not choosing from a multitude of VM images and types (although there are still plenty of variables that can impact pricing).

One of the significant benefits of SQL Managed Instance is that it behaves much like an on-premises SQL Server instance, which helps simplify the process of migrating to Azure. Although you might run into compatibility issues, in most cases you should have little trouble addressing them.

That said, SQL Managed Instance is still a database as a service (DaaS) rather than an on-premises application, so you don’t get the same level of control over the environment. In fact, the service is more closely aligned with Azure SQL Database, which is also a DaaS offering. However, SQL Managed Instance provides a higher degree of code parity and offers multiple features not available to SQL Database, such as Change Data Capture (CDC) and the common language runtime (CLR).

Azure SQL Database

Azure SQL Database is another PaaS offering, similar to SQL Managed Instance. SQL Database provides a fully managed platform that offers DaaS capabilities based on the latest stable version of the SQL Server database engine. SQL Database promises up to 99.995% availability—edging out SQL Managed Instance—and can process both relational and non-relational data, including spatial, graphs, JSON, and XML.

SQL Database automatically handles basic database management functions such as provisioning, monitoring, and backing up data. Like SQL Managed Instance, SQL Database is a fully managed evergreen service with patches and updates automatically applied. In this way, customers can avoid the complexities of maintaining a SQL Server environment on-premises, while getting a scalable service that supports modern cloud applications.

SQL Database is available in two purchasing models: vCore-based and DTU-based. The vCore-based model provides higher compute, memory, storage, and I/O limits, as well as better control over hardware generation. The vCore model offers three service tiers:

• General Purpose. Suited to common business workloads.
• Business Critical. Suited to OLTP workloads that require a high transaction rate, low latency rate, and high resilience to failures.
• Hyperscale. Suited to very large OLTP workloads that require fluid and automatic scalability, along with a high resilience to failures.

Customer can also choose from multiple hardware configurations that vary according to the maximum number of vCores they support, as well as the amount of memory and storage. In addition, customers can opt for one of the following compute tiers:

• Provisioned compute tier. Compute resources are pre-allocated and billed on a per-hour basis and based on the number of configured vCores.
• Serverless compute tier. Compute resources are auto-scaled and billed on a per-second basis and based on the number of vCores used. At this tier, SQL Database also automatically pauses inactive databases, during which you’re billed only for storage. You can also configure how long the database must be inactive before it is automatically paused. Furthermore, the serverless tier can automatically scale resources on demand, eliminating the need to micromanage resources. Both the auto-pause and auto-scale features can help save on subscription fees over the provisioned tier.

The DTU-based purchasing model is much simpler by comparison, providing bundled compute and storage packages targeted at common workloads. The model is built on the concept of the database transaction unit (DTU), a predefined measure of available processing, memory, and I/O capabilities. Like the vCore-based model, the DTU-based model offers three service tiers: Basic, Standard, and Premium. The following table provides an overview of the differences between tiers.

Service tier	Maximum DTUs	Maximum storage	IOPS per DTU	I/O latency	Maximum backup retention (days)
Basic	5	2 GB	1-4	5 ms (read) 10 ms (write)	7
Standard	3000	1 TB	1-4	5 ms (read) 10 ms (write)	35
Premium	4000	4 TB	>25	2 ms (read/write)	35

In the past, SQL Database provided only geo-redundant backup storage. However, Microsoft now offers two other options: locally redundant backup storage and zone-redundant backup storage. Both these options are still in preview. As with SQL Managed Instance, subscription fees are based on the selected options, but the mix of options is more varied with SQL Database, so it takes more effort to estimate and compare the costs of different configurations.

Azure SQL Edge

Azure SQL Edge is a much different offering from the other Azure SQL products. In fact, it’s not a cloud service at all, but rather a special edition of SQL Server that runs in edge environments, providing high-performance data storage and processing for IoT solutions. SQL Edge supports only a subset of features in SQL Server 2019 on Linux but also adds features not available to SQL Server on Linux or Windows. With SQL Edge, you can stream, process, and analyze relational and non-relational data, including graph, JSON, and time-series data.

SQL Edge offers the same T-SQL programming surface area as other SQL Server editions, making it easier for SQL Server developers and DBAs to work in edge environments. They can use SQL Edge to extend their applications to IoT edge gateways and devices in order to deliver real-time insights. SQL Edge also enables application portability between IoT devices, data centers, and the cloud and includes in-database graph and machine learning capabilities for low-latency analytics.

SQL Edge is available in two editions: SQL Edge and SQL Edge Developer. The two editions share identical feature sets but differ in terms of usage rights and supported memory and cores. SQL Edge runs in a container that’s based on the Ubuntu 18.04 OS. Microsoft recommends that you host the container on a Docker server configured with Ubuntu 18.04 LTS. However, you can run SQL Edge on Windows if the container is hosted inside in a Linux VM.

SQL Edge supports two deployment modes:

• Connected. SQL Edge is deployed as an Azure IoT Edge module. IoT Edge is a fully managed service built on Azure IoT Hub. You can download the SQL Edge module from Azure Marketplace. If IoT Edge is running on a Kubernetes cluster, you can deploy SQL Edge on that cluster.
• Disconnected. SQL Edge is deployed from a container image that you can pull from the Docker Hub. In this scenario, you deploy SQL Edge as a standalone Docker container or as part of a Kubernetes cluster.

SQL Edge was developed with edge IoT workloads in mind and has a relatively small footprint. As such, it does not include many of the features available to SQL Server 2019 on Linux, such as replication, CLR assemblies, full-text indexes, in-memory OLTP, or Active Directory integration. However, SQL Edge still includes several important security features, including transparent data encryption, Always Encrypted capabilities, role-based and attribute-based access controls, and data classification for compliance with security regulations.

Of the four Azure SQL offerings, SQL Edge provides the simplest pricing structure. Customers pay a flat monthly or yearly fee for each device on which SQL Edge is deployed. They can deploy Azure SQL Edge on both ARM64 and x64 devices.

The Azure SQL portfolio

With Azure SQL, Microsoft has set out to provide a consistent and unified experience across products and services based on the SQL Server database engine. Customers can leverage familiar tools and resources when working in the different environments, using their existing skills and knowledge across the entire family of Azure SQL products. Microsoft makes it relatively easy to set up these environments, especially the three cloud services, providing customers with secure and intelligent solutions for running their data-driven workloads.

One of the biggest challenges you might face with Azure SQL is calculating a total cost of ownership (TCO) when evaluating whether to adopt one or more of these services. If you know exactly which service you want, what workloads you plan to migrate, how you’ll configure the environment, and which optional features to include, you should be able to get a good sense of the TCO.

However, if you’re evaluating multiple services and configurations and are not sure which workloads you’ll migrate, you’ll have to consider multiple configuration and pricing options that each service offers and come up with a TCO for every applicable combination, a process complicated by the fact that Microsoft’s pricing and service details can at times be confusing, inconsistent, or incomplete. In the end, you might find it easier to contact a Microsoft sales representative or one of the company’s partners to help expedite this process.

Despite these challenges, organizations of all sizes could potentially benefit from Azure SQL, especially if they’re already committed to the SQL Server ecosystem. Microsoft offers multiple options for migrating data and deploying hybrid configurations that span on-premises and cloud environments. Cloud services, in general, can benefit organizations that want to avoid the overhead that comes with deploying and maintaining infrastructure in-house. Like any cloud offering, however, customers should understand the long-term TCO before committing their workloads to one of these services.

 

The post The Azure SQL portfolio appeared first on Simple Talk.

from Simple Talk https://ift.tt/38C17iY
via