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-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.
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.
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.
The graph above summarizes the service tier options with some additional considerations based on user needs.
Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.
One Response
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