Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Azure Sql Database Vs Aws Rds

Azure Sql Database Vs Aws Rds

The recent trend proves that the adoption of the Cloud has much greater significance and importance in modernizing IT. If you are working on migrating the on-premises SQL Server to Microsoft Azure cloud, you need to have a better understanding of the key differences between Azure SQL databases and SQL Server on Azure VMs and options that work best for you.

One of the key decision points for organization and Azure users is whether to deploy Azure SQL Database or SQL Server on Azure VMs for their relational database needs. Azure SQL Databases and SQL Server on Azure VM are optimized for different requirements. Let us deep dive further to understand the key differences.

In this article, you will see:

  1. Introduction and overview
  2. Azure SQL database design concepts
  3. Difference between Azure SQL Database and SQL Server on Azure VMs
  4. And more…

Overview

Azure SQL Database is great in several scenarios — when there is a challenge to provision and manage many databases without building the datacenter infrastructure; when there is a need to reduce the risk—as the management and patching overhead is completely owned by the vendor, which helps organizations and application owners concentrate on just the design-and-usage of the database.

Azure SQL databases are optimized for scenarios where there is a need for quick turnaround time in building the application (go-to-market time) and lower cost requirements (TCO – Total Cost of Ownership), reduced risks, and improved productivity.

Key differences between Azure SQL Database and SQL Server on Azure VMs

The following table summarizes the key differences between Azure SQL Database and SQL Server on Azure VMs.

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

Summary

In this article, you have learned the key differences between an SQL Server on Azure VMs and Azure SQL database.

Cloud platforms offer greater flexibility to manage TCO (Total Cost of Ownership). In the case of Azure SQL Database completely avoids CAPEX (Capital Expenditure) and OPEX (Operational Expenditure) but in the case of SQL Server on VMs induce a huge reduction in CAPEX.

By now, you understand that Azure SQL Databases do not provide all the features like replication, SQL Server Agent, Linked Servers, etc.,. The organizations that rely heavily on such features then SQL Server on Azure VM is the best fit. SQL Server on Azure VMs is optimized for scenarios where an organization is looking to extend its on-premises deployments to the cloud. Since the SQL Server engine running on an Azure VM is exactly the same as that running on your on-premises environments, it's easier for organizations to lift-and-shift their SQL workloads to Azure. With SQL Server running on Azure VMs, the organization's IT team has full administrative control over the VMs.

That is all for now… stay tuned for more updates.

Ultra Disk layout

Image 1: Ultra disk layout

Premium performance model

Image 2: Premium performance model

Driver and Tool image

Image 3: Driver and Tools

Table of contents

IT Cloud Automation using PowerShell
Deep dive into IT Cloud Automation using PowerShell
Getting started with Azure Automation
Getting started with Azure SQL Database using Azure CLI
Provisioning SQL Server 2019 Azure Container Instance using PowerShell
Four different methods to copy your Azure SQL database
Azure SQL Database vs SQL Server on Azure VMs
How to provision Azure SQL Database using Ansible
Quick start guide to Geo-restore in Azure SQL Database
Different ways to login to Azure automation using PowerShell
How to perform Azure SQL database Import/Export operations using PowerShell
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server
  • Author
  • Recent Posts

Prashanth Jayaram

Azure Sql Database Vs Aws Rds

Source: https://www.sqlshack.com/azure-sql-database-vs-sql-server-on-azure-vms/