PASS Pro Now Available

Welcome to the next evolution of PASS. Unlock exclusive training, discounts, and networking opportunities designed to accelerate your data career. Learn More >

The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Choose the Right Azure SQL Database Deployment Options for Your Solution

Happy 10th birthday, Azure SQL Database! Yes, that’s right – Microsoft’s Platform as a Service (PaaS) SQL database is ten years old this year! It’s grown from “Azure SQL” to a multi-faceted service with many options. At its core, though, it has had the same goal for a decade – the service manages the hardware, patching, backups, high availability, and even tuning plans and indexes; you focus on development and performance.

As it’s grown from one limited offering to a set of services, choosing the best option for your application and database can be confusing. Single database or managed instance, DTU or vCore, serverless, hyperscale - how do you know which one is right for you? In this article, I’ll explain the many options available and provide guidance on how to choose the best one for your project.

Terms

Let's start by understanding the different terms for the options you're presented with. 

  • Deployment Option – The choice you make about how to structure the SQL server and its databases.
  • Purchase Model – Within the deployment option, the choice you make about how to pay for the service.
  • Service Tier – Within the purchase model, the level of compute power you want.
  • Compute Tier – Within the service tier, the ability to have compute 24/7 or on demand.

Deployment Options

Deployment options determine how to structure the "SQL Server" and its databases. You have three options currently:

Managed Instance

Managed Instances are a collection of system and user databases with a shared set of resources. You deploy a logical SQL server, and the databases on it share the compute power. They are ideal for moving existing on-premises SQL Server instances and databases to a PaaS offering in Azure with minimal code changes. This option offers near-100% compatibility with on-premises SQL Server.

Managed instances are the easiest way to migrate existing on-premises data. You can migrate with an existing backup or the Database Migration Service. You can use features of SQL Server that aren’t available in Single or Elastic Pool such as cross-database queries, SQL Server Agent jobs, Change Data Capture, Resource Governor, or Service Broker. On top of this, you still have the PaaS features such as built-in backups, high availability, and scalability.

Single

A single database is a standalone database, given its own set of resources. In this case, you create a logical SQL server, but the databases can’t natively communicate with other databases. These databases are optimized for development of new, cloud-native applications. They can also be a good choice for simple databases that need to be migrated from an on-premises SQL Server or other relational databases (MySQL, PostgreSQL).

Elastic Pool

An Elastic Pool is a collection of databases with a shared set of compute resources. These are also created under and managed via a logical SQL server. They allow you to take a set of databases with unpredictable or varying demand and use that to optimize your costs.

Elastic Pools are ideal for multi-tenant SaaS applications where you have many databases with the same schema that have varying usage patterns. It’s rare to have multiple customers or divisions that all have the same level of use every day of every week; thus, the pricing can accommodate spikes and dips in usage.

You can also use them for databases that require cross-database communication with the elastic query feature (although this is still in preview).

Purchase Model

After you’ve chosen your deployment option, you’ll need to decide how to pay for it. There are two options: the default, vCore, and the older DTU model. In vCore, compute and storage are paid for and scale separately. You choose the number of cores you want, and the amount of memory you get is tied to that. You then choose your storage option separately. In the DTU model, which is described as “a blended measurement of CPU, memory, and I/O”, compute and storage are bundled and scale together.

Service Tiers

Within each purchasing model there, are different tiers of service available. These determine the level of resources you’ll be able to utilize.

DTU gives you the choice of Basic, Standard, or Premium, with a cap on the max storage size.

  • Basic
    • 5 DTUs
    • Max 2 GB
  • Standard
    • 10 – 3,000 DTUs
    • Max 1 TB
  • Premium
    • 125 – 4,000 DTUs
    • Max 4 TB

vCore pricing is based on the compute and memory level you desire. You then need to choose your storage - General Purpose, Business Critical, or Hyperscale. Within each, the storage size varies.

  • General Purpose is premium blob storage. This is a great choice for non-production or low-performance workloads.
    • Compute ranges from 2 vCore/10.5 GB memory to 80 vCore/396 GB memory.
    • Storage ranges from 3 TB to 8 TB.
  • Business Critical uses local SSD storage. This is suggested for production workloads, or anything requiring high performance.
    • Compute ranges from 2 vCore/10.5 GB memory to 80 vCore/396 GB memory.
    • Storage is available up to 4 TB.
  • Hyperscale is the newest service offering; a distributed model with compute nodes, page servers, a log service, and storage. It’s designed to support much larger databases, faster backups, faster restores, and rapid scale out and scale up. 
    • Compute ranges from 2 vCore/10.5 GB memory to 80 vCore/396 GB memory.  
    • Storage is available up to 100 TB!

Compute Tier

Within the Single Database deployment option, under the vCore pricing model with General Purpose storage, there are two options for your compute resources.

 Provisioned compute provides a fixed amount of compute resource for a fixed price and is billed hourly. This is a good choice for databases that are needed continually with predictable, consistent usage.

The alternative is Serverless compute, which auto-scales compute and billing for compute used per second. In this model, you set a minimum and maximum vCore, and when your database is being used, it will scale between those levels. You also set an autopause delay time period, which indicates how long the database is inactive before it is paused – and during that time period, you’re only paying for storage, not compute! This is a good choice for databases that are used for short periods of time and then are inactive.

Summary

Azure SQL Database is Microsoft’s PaaS SQL Server database. It’s designed to minimize management and optimize performance, and includes multiple deployment options and service tiers so you can find the right blend of management, features, compute, and storage.  To determine the right database for you, decide which Deployment option is best based on the current environment, and then work through the purchase model, and service and compute tiers.  While moving your database within Azure is possible, it can be complicated depending on where it exists, so it’s imperative that you spend the time up front researching which solution is optimal for your database(s).

Jes Schultz
About the author

Jes Schultz is a Software Engineer with Microsoft who has experience with a wide range of SQL Server features, from version 2005 through 2019, and is on the cutting edge with Azure technologies. Since graduating Fox Valley Technical College with an IT-Programmer/Analyst degree, Jes has worked as an SSRS developer, DBA, and consultant. Before joining Microsoft, Jes was a six-time Data Platform MVP, recognized for her community work. Jes tackles every project and problem with tenacity and her enthusiasm is unmatched in the SQL Server community. In her free time, Jes never stops moving, and counts fitness, coffee, cooking, and travel as essentials in life.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids