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 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 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 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: 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. 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. 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 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
No comments:
Post a Comment