Comparisson between SQL services on Azure

25 Feb 2021 0 minutes to read Contributors

Introduction

Azure SQL is a family of fully managed, secure, and intelligent SQL database services that support a wide range of application patterns, from re-hosting and modernizing existing SQL Server workloads to modern cloud application development.

 

 

Because the entire Azure SQL family is built upon the same SQL Server database engine, you can migrate applications with ease and continue to use the tools, languages, and resources you’re familiar with. You’ll discover that your skills and experience transfer easily to the cloud, as the innovative features in Azure SQL help you operate more efficiently and save money along the way.

 

The question is, how do we can help your customers choose the right option for their needs? This is an area where partners can add value for customers. We can help them make a choice that will solve the challenges they may be facing today, with flexibility to address their future business needs as well.

 So we brought up below comparison together so our customer can understand and chose right type of SQL service on Azure.

 

Azure SQL Database vs SQL Server on Azure VMs

 

 

Azure SQL Database

Azure IaaS SQL Server

 

 

Database Features

The majority of the database-level features, SQL standards, T-SQL query processing are supported. For example, database collation, database auditing, T-SQL Expression, etc.

It supports all the SQL Server on-premises capabilities

 

 

Database size

1.      The database size is always based on the underlying service tier models. For example, the Premium P15 service tier model supports up to 4 TB databases

2.      Azure SQL Database support databases of up to 100 TB with the Hyperscale service tier model

3.      Databases per logical server are 5000

4.      DTU (Database Transaction Units) or eDTU (Elastic Database Transaction Units) quota is 54,000 per server

  • Note: The only vCore model supports Hyperscale service tier

Max database size is constrained by the size of the VM. SQL Server instances support up to 256 TB of storage. The instance can support as many databases as needed

For example, a premium storage disk can support up to 32 TB. You also have an option to use Ultra disk. The Ultra Disk is available in different sizes that can be customized for the range of input values

You can refer to the Image 1 for more details

 

 

Database File layout

Multiple log files are not supported

Multiple log files are supported

 

 

Compute resources

The computing resource is based on the DTU or VCore Model. There is no direct control over computing resources. You need to understand the performance baseline benchmarks to decide the computing

https://dtucalculator.azurewebsites.net/

In this case, you have full control over the VM compute resources for all the SQL Server deployments

The VM series are broadly classified to fulfill all the application needs:

  • Compute-optimized
  • A-Series
  • B- Series
  • D-Series – General purpose. In most cases, we tend to use this series.
  • Memory-optimized
  • M-Series—Memory and storage optimized virtual machines
  • GPU optimized
  • N –Series – Virtual Machines enabled with High-Graphics capabilities
  • High performance compute
  • E-Series -Optimized for in-memory and hyper-threaded applications
  • L-Series
  • Storage optimized virtual machines

 

 

Availability

It is 99.995% available and availability is guaranteed

1.      By default, Azure infrastructure provides fault-tolerance and high Availability for the Azure SQL databases

2.      By default, SQL Database and SQL Managed Instance store data in geo-redundant (RA-GRS) storage blobs that are replicated to a paired region

3.      You can test the in-built automatic failover feature

Invoke-AzSqlDatabaseFailover -ResourceGroupName -ServerName -DatabaseName

4.      Also, you have active geo-replication and point-in-time restore of the databases

The availability is up to 99.99%

1.      By default, Azure infrastructure provides fault-tolerance and high Availability for the VMs

2.      You can use SQL level high-availability and Disaster-recovery features

3.      Achieving high-availability always incur the cost and additional overhead to manage the additional VM servers

 

 

Migration

It will be migrated to the latest available stable database engine version

Run Database migration Assistant or Azure Migrate tools to define the upgrade or migration paths

You can also try Transactional Replication in some cases

You can refer to this article for T-SQL differences

It will be a lift-and-shift kind of migration, if it is the same version

You can use SQL native backup and restore method, log shipping, AlwaysOn for the migration

 

 

Database Backup

Automatic. It will support short-term (7 or 35 days) and Long-term up to 10 years based on the service tiers

It is possible to restore the deleted database point-in-time, or to the earlier point-in time on the same server

  • Note 1: accidentally or intentionally, If you delete the server or logical host, all the associated databases and backups are also deleted. It is not possible to recover the database and the deleted server
  • Note 2: Backups do not incur an additional cost for storage until it goes beyond 100% of the provisioned database storage
  • Note 3: Manage the backup retention period to reduce storage cost

It is not an automatic process. The database backups are managed using SQL native or any third-party tools

 

 

Resource Management

We have a scale in and scale out option to manage the compute (DTU) to individual databases

You can still use the resource governance features with a heavy administration overhead

 

 

Database Patching

Automatic

Manual

 

 

License

Built-in license model. The database software is automatically patched and upgraded by Microsoft

1.      No upfront cost

2.      Pay-As-You-Go — pay only for what you use

Azure Hybrid Benefit (AHB)—It supports the use of the existing server license with Software Assurance BYOL (Bring-Your-OWN-License) model where you need to pay for VM (Compute) and storage only

You also have the option to use Microsoft controlled licenses for SQL Server images versions such as SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019 and editions such as Developer Edition, Express Edition, Web Edition , Standard Edition, and Enterprise Edition

Pay-as-you-go model

Disaster recovery (DR) model where it is used only for DR in Azure

 

 

Pricing

Azure SQL Database pricing calculator

Azure Pricing Calculator

 

 

Monitoring and Reporting

Integrated with BI. It is easy to integrate with SQL Server analytics solution and Log Analytics using OMS

Need integration with custom scripts or third party tool

 

 

Usage

1.      High Time-to-market

2.      Support modern lightweight application

3.      In most cases, the agile application is built on this framework

4.      Applications that need built-in high-availability, disaster recovery, and auto-patching and upgrade mechanisms

5.      The application that requires automatic scale option

1.      Application requires minimal or no code changes usually prefer this type of infrastructure. This is usually because of application dependency and complex integration

2.      OLTP databases where workload and transactions are managed and isolated independently

3.      Security — the requirement is to get exclusive access or administrator privileges to the server

4.      Scale up or down is available at the VM level, but some of them can be done online. In some cases, the VM needs to be brought down. For example, changing the disk type from Premium SSD to HDD

 

 

SQL Agent, Linked server & DB Mail

No SQL agent or DB mail or Linked server

SQL Agent & DB Mail are supported as similar to on-premise. Supports Linked server

 

 

Transparent Data Encryption (TDE)

By default, TDE is enabled

TDE is not enabled by default. You need to walk-through the manual process to enable TDE manually

 

 

Database Restore

You can only restore using the Azure portal, or Azure PowerShell cmdlets or Azure CLI cmdlets

Database restores with automated backups using SSMS is not allowed. Point-in-time database restores are possible and are performed using the above-mentioned set of methods

Restore can be performed using SSMS and point -in-time restore possible depending on the backup frequency and database recovery model

 

 
 

 

Database Copy

Bacpac files, import/export or data copy methods to copy the databases

Backpac, import/export, backup and restore method

 

 

Business Intelligence Services

Azure Data Factory (For SSIS packages)

Power BI for SSRS ( SQL Server Reporting Services)

 

 

Azure Analysis Services (for OLAP models)

 SSAS ( SQL Server Analysis Services)

 

 

Recovery model

Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available

All 3-recovery models Full, Simple, and Bulk-logged recovery models are supported

 

 

Transactional Replication

Yes, Transactional and snapshot replication subscriber only

Replication is supported

 

 

Driver and tool support

It supports the following drivers:

.Net Framework , ODBC, PHP, JDBC, OLEDB, NODE.js

Tools: SSMS, sqlcmd, Azure Data Studio, MSSQL CLI

You can refer to image 3 for more details

SQL Server connectivity can be made using the following drivers:

ODBC Drivers or SQL Native Client driver or OLEDB provider for SQL Server

Tools: SSMS, sqlcmd, Azure Data Studio, MSSQL CLI

 

 

Azure SQL Database vs Azure SQL Managed Instance

 

Feature

Azure SQL Database

Azure SQL Managed Instance

Always Encrypted

Yes - see Cert store and Key vault

Yes - see Cert store and Key vault

Always On Availability Groups

99.99-99.995% availability is guaranteed for every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database

99.99.% availability is guaranteed for every database and can't be managed by user. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. Use Auto-failover groups to configure a secondary SQL Managed Instance in another region. SQL Server instances and SQL Database can't be used as secondaries for SQL Managed Instance.

Attach a database

No

No

Auditing

Yes

Yes, with some differences

Azure Active Directory (Azure AD) authentication

Yes. Azure AD users only.

Yes. Including server-level Azure AD logins.

BACKUP command

No, only system-initiated automatic backups - see Automated backups

Yes, user initiated copy-only backups to Azure Blob storage (automatic system backups can't be initiated by user) - see Backup differences

Built-in functions

Most - see individual functions

Yes - see Stored procedures, functions, triggers differences

BULK INSERT statement

Yes, but just from Azure Blob storage as a source.

Yes, but just from Azure Blob Storage as a source - see differences.

Certificates and asymmetric keys

Yes, without access to file system for BACKUP and CREATE operations.

Yes, without access to file system for BACKUP and CREATE operations - see certificate differences.

Change data capture - CDC

No

Yes

Collation - server/instance

No, default server collation SQL_Latin1_General_CP1_CI_AS is always used.

Yes, can be set when the instance is created and can't be updated later.

Columnstore indexes

Yes - Premium tier, Standard tier - S3 and above, General Purpose tier, Business Critical, and HyperScale tiers

Yes

Common language runtime - CLR

No

Yes, but without access to file system in CREATE ASSEMBLY statement - see CLR differences

Credentials

Yes, but only database scoped credentials.

Yes, but only Azure Key Vault and SHARED ACCESS SIGNATURE are supported - see details

Cross-database/three-part name queries

No - see Elastic queries

Yes, plus Elastic queries

Cross-database transactions

No

Yes, within the instance. See Linked server differences for cross-instance queries.

Database mail - DbMail

No

Yes

Database mirroring

No

No

Database snapshots

No

No

DBCC statements

Most - see individual statements

Yes - see DBCC differences

DDL statements

Most - see individual statements

Yes - see T-SQL differences

DDL triggers

Database only

Yes

Distributed partition views

No

Yes

Distributed transactions - MS DTC

No - see Elastic transactions

No - see Linked server differences. Try to consolidate databases from several distributed SQL Server instances into one SQL Managed Instance during migration.

DML triggers

Most - see individual statements

Yes

DMVs

Most - see individual DMVs

Yes - see T-SQL differences

Elastic query (in public preview)

Yes, with required RDBMS type.

Yes, with required RDBMS type.

Event notifications

No - see Alerts

No

Expressions

Yes

Yes

Extended events (XEvent)

Some - see Extended events in SQL Database

Yes - see Extended events differences

Extended stored procedures

No

No

Files and file groups

Primary file group only

Yes. File paths are automatically assigned and the file location can't be specified in ALTER DATABASE ADD FILE statement.

Filestream

No

No

Full-text search (FTS)

Yes, but third-party word breakers are not supported

Yes, but third-party word breakers are not supported

Functions

Most - see individual functions

Yes - see Stored procedures, functions, triggers differences

In-memory optimization

Yes in Premium and Business Critical service tiers.
Limited support for non-persistent In-Memory OLTP objects such as memory-optimized table variables in Hyperscale service tier.

Yes in Business Critical service tier

Language elements

Most - see individual elements

Yes - see T-SQL differences

Linked servers

No - see Elastic query

Yes. Only to SQL Server and SQL Database without distributed transactions.

Linked servers that read from files (CSV, Excel)

No. Use BULK INSERT or OPENROWSET as an alternative for CSV format.

No. Use BULK INSERT or OPENROWSET as an alternative for CSV format. Track these requests on SQL Managed Instance feedback item

Log shipping

High availability is included with every database. Disaster recovery is discussed in Overview of business continuity.

Natively built in as a part of Azure Data Migration Service migration process. Not available as High availability solution, because other High availability methods are included with every database and it is not recommended to use Log-shipping as HA alternative. Disaster recovery is discussed in Overview of business continuity. Not available as a replication mechanism between databases - use secondary replicas on Business Critical tierauto-failover groups, or transactional replication as the alternatives.

Logins and users

Yes, but CREATE and ALTER login statements do not offer all the options (no Windows and server-level Azure Active Directory logins). EXECUTE AS LOGIN is not supported - use EXECUTE AS USER instead.

Yes, with some differences. Windows logins are not supported and they should be replaced with Azure Active Directory logins.

Minimal logging in bulk import

No, only Full Recovery model is supported.

No, only Full Recovery model is supported.

Modifying system data

No

Yes

OLE Automation

No

No

OPENDATASOURCE

No

Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences

OPENQUERY

No

Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences

OPENROWSET

Yes, only to import from Azure Blob storage.

Yes, only to SQL Database, SQL Managed Instance and SQL Server, and to import from Azure Blob storage. See T-SQL differences

Operators

Most - see individual operators

Yes - see T-SQL differences

Polybase

No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function or use an external table that references a serverless SQL pool in Synapse Analytics.

No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function, a linked server that references a serverless SQL pool in Synapse Analytics, or an external table (in public preview) that references a serverless SQL pool in Synapse Analytics or SQL Server.

Query Notifications

No

Yes

Machine Learning Services(Formerly R Services)

Yes, in public preview

No

Recovery models

Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available.

Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available.

Resource governor

No

Yes

RESTORE statements

No

Yes, with mandatory FROM URL options for the backups files placed on Azure Blob Storage. See Restore differences

Restore database from backup

From automated backups only - see SQL Database recovery

From automated backups - see SQL Database recovery and from full backups placed on Azure Blob Storage - see Backup differences

Restore database to SQL Server

No. Use BACPAC or BCP instead of native restore.

No, because SQL Server database engine used in SQL Managed Instance has higher version than any RTM version of SQL Server used on-premises. Use BACPAC, BCP, or Transactional replication instead.

Semantic search

No

No

Service Broker

No

Yes, but only within the instance. If you are using remote Service Broker routes, try to consolidate databases from several distributed SQL Server instances into one SQL Managed Instance during migration and use only local routes. See Service Broker differences

Server configuration settings

No

Yes - see T-SQL differences

Set statements

Most - see individual statements

Yes - see T-SQL differences

SQL Server Agent

No - see Elastic jobs (preview)

Yes - see SQL Server Agent differences

SQL Server Auditing

No - see SQL Database auditing

Yes - see Auditing differences

System stored functions

Most - see individual functions

Yes - see Stored procedures, functions, triggers differences

System stored procedures

Some - see individual stored procedures

Yes - see Stored procedures, functions, triggers differences

System tables

Some - see individual tables

Yes - see T-SQL differences

System catalog views

Some - see individual views

Yes - see T-SQL differences

TempDB

Yes. 32-GB size per core for every database.

Yes. 24-GB size per vCore for entire GP tier and limited by instance size on BC tier

Temporary tables

Local and database-scoped global temporary tables

Local and instance-scoped global temporary tables

Time zone choice

No

Yes, and it must be configured when the SQL Managed Instance is created.

Trace flags

No

Yes, but only limited set of global trace flags. See DBCC differences

Transactional Replication

Yes, Transactional and snapshot replication subscriber only

Yes, in public preview. See the constraints here.

Transparent data encryption (TDE)

Yes - General Purpose and Business Critical service tiers only

Yes

Windows authentication

No

No

Windows Server Failover Clustering

No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database.

No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database.

 

In this article