SQL Server Tips

Azure SQL: How To Choose The Right Service Database

Updated
9 min read
Written by
Mark Varnas

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.

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-premises 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 (PaaS) 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 cannot use on-premises 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

1. 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.

2. 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.

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 Managed 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.

1. DTU model

The DTU-based purchase model was the first presented for the 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.

2. 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 get cost savings if you have existing licenses.

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:

1. 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.

2. Business-critical

Indicated for 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.

3. 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.

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:

1. Provisioned

Allows you to choose the exact amount of resources that are provisioned for your workloads. Typically, billing for this compute tier is per hour.

2. 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, the 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 about the difference 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.

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

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

1 thought on “Azure SQL: How To Choose The Right Service Database”

  1. Hi ,

    i have application has 3 separtmodules for diffent 2 clients Atlthough th ap has separate databases we are using a signal application/solutionto manage that .. we looking one DB serverand 3posible databas for application.. please let me know the best solution

    Reply

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials