August 10

Azure SQL vs SQL Server 2020

0  comments

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"73dde":{"name":"Main Accent","parent":-1},"8215b":{"name":"Main Accent Transparent","parent":"73dde","lock":{"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"73dde":{"val":"rgb(126, 201, 233)"},"8215b":{"val":"rgba(99, 191, 227, 0.07)","hsl_parent_dependency":{"h":197,"l":0.64,"s":0.7}}},"gradients":[]},"original":{"colors":{"73dde":{"val":"rgb(253, 99, 78)","hsl":{"h":7,"s":0.97,"l":0.64,"a":1}},"8215b":{"val":"rgba(253, 99, 78, 0.07)","hsl_parent_dependency":{"h":7,"s":0.97,"l":0.64,"a":0.07}}},"gradients":[]}}]}__CONFIG_colors_palette__

Table of Contents

Azure SQL Database is a PaaS (Platform as a Service) offer, built on standardized hardware and software that is owned, hosted, and maintained by Microsoft. SQL Server on Azure Virtual Machines (VMs) is an IaaS (Infrastructure as a Service) offer and allows you to run SQL Server inside a virtual machine in the cloud.


Azure SQL Database and SQL Managed Instance share a common code base with the latest stable version of SQL Server. Most of the standard SQL language, query processing, and database management features are identical. Azure manages your databases and guarantees their high-availability. Some features that might affect high-availability or can't be used in PaaS world have limited functionalities in SQL Database and SQL Managed Instance. These features are described in the table below.


Features comparison: Azure SQL Database and Microsoft SQL Server


Microsoft Azure SQL Database

Microsoft SQL Server

Technical documentation

Cloud-based only

Yes

No

Data scheme

Yes

Yes

Typing

Yes

Yes

XML support

Yes

Yes

Secondary indexes

Yes

Yes

SQL

Yes

Yes

APIs and other access methods

ADO.NET
JDBC
ODBC

ADO.NET
JDBC
ODBC
OLE DB
Tabular Data Stream (TDS)

Supported programming languages

.Net
C#
Java
JavaScript (Node.js)
PHP
Python
Ruby

C+
C++
Delphi
Go
Java
JavaScript (Node.js)
PHP
Python
R
Ruby
Visual Basic

Server-side scripts

Transact SQL

Transact SQL, .NET languages, R, Python and (with SQL Server 2019) Java

Triggers

DDL triggers - Database only

   DML triggers - Most

Yes

Partitioning methods


Tables can be distributed across several files (horizontal partitioning); sharding through federation

Replication methods

Yes, with always 3 replicas available

Yes, but depending on the SQL-Server Edition

MapReduce

No

No

Consistency concepts

Immediate Consistency

Immediate Consistency

Foreign keys

Yes

Yes

Transaction concepts

ACID

ACID

Concurrency

Yes

Yes

Durability

Yes

Yes

In-memory capabilities


Yes

User concepts

Fine grained access rights according to SQL-standard

Fine grained access rights according to SQL-standard

DTU vs vCore

Let’s have a look first on each of it.


DTUs – Database Throughput Unit

The performance levels of DTUs are measured using the following metric:

 DTUs with different service tiers allow comparison of resources that power different Database performance levels
  • Measured in (DTUs) or Database Throughput Units
  • DTU = Unit of measurement for compute processing power.

It comprises of the following:

  • CPU, memory, physical reads, and transaction log writes (IOPS)

As you see from the screenshot, your max database size is dependent on the amount of DTUs that you allocate. Microsoft provides linear scalability for these databases and can be scaled up or down based on estimated loads.

Microsoft has created the Azure SQL Database Benchmark or ASDB to estimate how much performance improvement is offered with every increase in DTUs.

The main idea of the DTU concept is to offer a preconfigured resource configuration to clients so that it simplifies the scaling of the performance over a single metric. Such as, if we need more performance, we can slide the bar and increase the number of DTU in Azure SQL Database.


Presenting – vCores

With vCores, Microsoft takes a more contemporary approach. It is similar to sizing an Azure SQL VM for running database workloads. Even though it is a managed database, Microsoft gives you more flexibility in configuring the CPU-cores and database size independent of each other. As far as IOPS is concerned, they are fixed and dependent on the vCore service tier. See below:

General Purpose

Hyperscale

Business Critical

7000 IOPS Max

200,00 IOPS Max

200,000 IOPS Max

5-10 milliseconds disk latency

5-10 milliseconds disk latency @ 7000 IOPS for Logs

5-10 milliseconds disk latency

1-2 milliseconds disk latency @ 200,000 IOPS for Data


1-2 milliseconds disk latency @ 200,000 IOPS for Logs

1-2 milliseconds disk latency @ 200,000 IOPS for Logs

This brings in more transparency to the pricing models and more control in individual resource allocation.


vCore Model Highlights

DB size and memory can be increased individually. IOPS is service tier dependent, see table above.

7GB or 5.5GB of memory is allocated to each vCores (Service Tier Dependent)

  • Pay separately for compute capacity, number of IOPS, DB backup, log storage, and database size
  • If you already own an On-Premises SQL License with Software Assurance (SA), you can save up to 55% with a BYOL license.


DTU to vCore Conversion

In simplistic terms, here is a quick comparison with vCores vs DTU:

  • 1 vCore of General Purpose = 100 DTUs Standard
  • 1 vCore of Business Critical = 125 DTUs Premium
  • 80 vCores = 8000 DTUs *

A maximum of 4000 DTUs are supported at the time of writing this article.


Conclusion

The Basic, Standard S0, S1 and S2 service tiers provide less than one vCore (CPU). For CPU-intensive workloads, a service tier of S3 or greater is recommended.

Regarding data storage, the Basic, Standard S0, and S1 service tiers are placed on Standard Page Blobs. Standard Page Blobs use hard disk drive (HDD)-based storage media and are best suited for development, testing, and other infrequently accessed workloads that are less sensitive to performance variability.

You should only consider moving to the new vCore model if your DTU usage somehow exceeds 300 or more. However, you can also choose vCores if you want to allocate more memory and less CPU or vice versa.

About the Author

Ateeb is a Microsoft Certified Solutions Associate. He aids clients in deployment, and management of Microsoft technologies - for both, on-premises and on the cloud environment. With significant experience in deploying stand-alone and enterprise solutions, Ateeb enjoys helping organizations with their digital transformation journey.

Ateeb Fayyaz

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>