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 <ResourceGroupname> -ServerName <ServerName> -DatabaseName <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