Azure SQL: How to choose the right service database

What is Azure SQL?

In the recent past, Microsoft introduced Azure SQL, bringing all the SQL Server products in one suite.

Microsoft is grouping all the options for SQL running in the Azure platform into one portfolio.  A family of managed products, making it easier to see them and choose the best for your needs.

If you’re already familiar with Azure SQL Database, this slight shift means that Azure SQL also includes SQL Server on VMs (Virtual Machines).  Microsoft is continuously investing and enhancing the benefits associated with it.

As you can see below, there are several different options within Azure SQL.

Azure SQL products in the cloud.
Figure 1 – Azure deployment options.

So, as we move from left to right, the more we will go towards a type of cloud-native solution. In other words, you will have less control, but you can get more resources provided by the Cloud platform.

SQL Server on Virtual Machines

It’s functionally identical to deploying SQL Server to an on-prem VM.

This first option is Infrastructure as a Service (IaaS).  Microsoft manages the Infrastructure, so basically, you have all of the features and functionality available to you, along with all of the overhead of managing it.

Typically, this is the best option for migrations and applications requiring OS-Level access. There are expansive SQL and OS version support.

Azure SQL on Azure Virtual Machines doesn’t have specific service tiers. If you’re considering Azure SQL VMs, you’ll want to review the images, the storage configuration, and finally, the quick checklist to obtain optimal performance.

About a year ago, the resource provider was announced, which brings the functionality of Azure Marketplace images to SQL Server instances self-installed on Azure VMs.

Azure SQL Database

With this platform as a service (PaSS) option, organizations can reduce management overhead and quickly deliver highly resilient services.

You don’t need to architect a database installation for scalability, high availability, or disaster recovery. All these features are provided automatically by the service.

Azure SQL Database uses the latest stable version of the Microsoft SQL Server database engine. You can use advanced query processing features, such as high-performance in-memory technologies and intelligent query processing.

The newest capabilities of SQL Server are released first to SQL Database, and then to SQL Server itself. You get the latest SQL Server capabilities with no overhead for patching or upgrading, tested across millions of databases.

It is important to note that not all SQL Server features are available in Azure SQL Database.  You can not use on-premise features like change data capture (CDC), Common Language Runtime  (CLR), database mail, and others. You can review all the features available here.

SQL Database can be the right choice for a variety of modern cloud applications because it enables you to process both relational data and non-relational structures, such as graphs, JSON, spatial, and XML.

Currently, Azure SQL Database offers two deployment options:

  • Single database – It is similar to a contained database in SQL Server. Each Database has it is own Resources (DTU or V-Core), and this resource will not be shared with other databases in the same single Instance. In the vCore-based purchasing model, Hyperscale, and serverless service tier options are available.
  • Elastic Pool – It is a collection for Multiple Azure Single Databases with a shared set of resources managed via a logical SQL server. Databases can be moved into and out of an elastic pool. It provides a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.

Azure SQL Managed Instance

Azure SQL Managed Instance is a service, which is a hybrid of the previous two options, designed to provide easy compatibility with the latest SQL Server (Enterprise Edition) Database Engine used for on-premises databases.

It combines the broadest SQL Server engine compatibility with all the benefits of a fully managed and evergreen platform as a service.

The benefit behind it is that you can lift-and-shift on-premises SQL Servers and applications to Azure with minimal changes.

These instances enable you to deploy a native virtual network (VNet) that mimics on-premises deployments and increases security.

Using manages Instance, you will have access to many features as Linked server, database mail, resource government, service broker, and much more.

Choosing the right purchase model

Once you pick a deployment option, the next decision you have is the purchasing model.

DTU model

The DTU-based purchase model was the first presented for Azure SQL Database.

Microsoft designed it to be simple.  So basically, you go to one level of DTU, and you get a certain amount of computing and storage, but they kind of scale linearly as opposed to independent.

Vcore model

If you choose the SQL managed Instance, the choice here is simple because you only have the VCore model available.

It is a newer model that allows you to scale compute and storage independently.

Microsoft’s recommended model will enable you to use things like Azure hybrid benefits and getting cost savings if you have existing licenses.

Figure 2 – Azure SQL Purchase models.

Choosing a service tier for performance and availability

In this post, we will focus on the vCore purchasing model (recommended), but you can easily compare the vCores and DTUs models.

Within the VCore purchasing model, there are three service tiers, as you can see below:

  • General-purpose – This is Ideal for most business workloads. Offers budget-oriented, balanced, and scalable compute and storage options. You will have temp DB running on SSD and data and log files stored in Azure premium storage. The backups globally distributed protect you in times of catastrophic failure.
  • Business-critical – Indicated to applications with low-latency response requirements. It also offers the highest resilience to failures by using several isolated replicas.  Only this tier can leverage in-memory OLTP to improve performance. You will have temp DB data, and log files, all running on local SSD.  Behind the scenes, Microsoft will deploy an always-on availability group for you. So at any given time, you’re going to have three secondary replicas.
  • Hyperscale – It’s perfect for business workloads with highly scalable storage and read-scale requirements. Currently only available for single databases, not managed instances. This is the only option with the ability to autoscale storage up to 100 TB.
Figure 3 – Azure SQL Services Tiers.

For a more in-depth explanation between the three tiers (including scenarios), you can also refer to the Microsoft service-tier characteristics documentation.

Provisioned or serverless compute?

If you choose General Purpose within Single databases and the vCore-based model, you have a new decision to make regarding the compute that you pay for:

  • Provisioned: Allows you to choose the exact amount of resources that are provisioned for your workloads. Typically, billing for this compute tier isper hour.
  • Serverless: Has auto-pause and resume capabilities (with a time delay you set), meaning when your database is paused, you only pay for storage. Therefore, you only have to pay for the computing resources (memory & vCores) you use.

Unfortunately, serverless compute tier isn’t a fit for all databases. You may experience some delay in the ramp-up of the compute power after idle periods. Also, after an auto-pause, the first connection to the database will fail.

For a more in-depth explanation between the two compute options (including scenarios), you can refer to the detailed comparison in the documentation and be sure to check out pricing information.

Summary

Azure offers a lot of options and flexibility, so you can get what you need, nothing less.

As you’ve hopefully noticed, there are a few main decisions to be made.

Figure 4 –  Azure SQL models and service tiers.

The graph above summarizes the service tier options with some additional considerations based on user needs.

If you’re not sure which way to go, let us help point you in the right direction!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.