Azure SQL database is a fast growing service with several features being added each month hence its challenging to capture the latest comparison set however this blog is an effort to categorize various aspects and draw comparison between Azure SQL Database (PaaS) to SQL Server running on an Azure VM (IaaS). For more comprehensive and latest information please refer to Azure SQL DB documentation.
|Category||Azure SQL Database||SQL Server on Azure VM|
|Features||Not all features are supported hence not recommended for Lift & Shift (with small changes) use-cases. If it is a new app born or re-architected in cloud, PaaS would be attractive to your needs. Read specific details on features not supported here||Works best for existing applications that require fast migration to the cloud with minimal changes. Eliminates the need to buy on- premises SQL Server hardware for
production or non-production workloads.
|PaaS vs IaaS||Applications that need built-in high availability, DR and upgrade mechanisms. Employ your IT resources on application and not on Infrastructure.||Customized IT environment with full administrative rights (same as On-premise). You need to plan for all HA, DR, and upgrades and should factor during cost comparison|
|Upgrades||Managed service so gets newer features first, no admin cost of
|Subscribed to a static version and you need to plan for any upgrades/ patches.|
|Deciding which one to use?||For any new cloud-designed, re-architecting/ consolidation, start from a PaaS and scale down if there are strong reasons for not using PaaS.||1. Lift & Shift (with small changes)
2. Existing Large databases migrating to cloud or Consolidating several databases to a cloud based database (using SQL Data warehouse as DBaaS is another option)
|Limitations||1. Recommended for DBs up to 1 TB in size that cannot be partitioned (can scale if partitioning allowed).
2. Full Admin rights are now allowed.
3. Purchase DB size as DTUs without worrying storage, CPUs, scaling and IOPS
4. Each call must be made over SSL (TLS security) since it goes outside the datacenter/On-prem perimeter
|1. Recommended for DBs bigger than 1 TB in size that cannot be
2. Load balancing and auto-scaling needs to be designed and
3. Storage discs (standard, premium), sizes of VMs (DS, DV, GS) needs to be custom selected considering IOPS usage needing significant architectural decisions upfront.
|Total cost of ownership||Eliminates hardware costs and reduces administrative costs.
Lowest start-up cost ($75/month for a dev database)
|Eliminates hardware costs. Start-up costs include VM, Storage
accounts, Disks, Blobs. Significantly cost effective than On-premise solution but higher startup cost than PaaS
|Business continuity||Built-in fault tolerance, automated backups, Point-In-Time
Restore, Geo-Restore, and Active Geo-Replication to increase
business continuity. Such features are almost always not partially
implemented on an On-premise architecture.
|Need a custom created high availability, DR solution highly
optimized for specific application. Need to periodically test/certify
failover to stay relevant.
|License||License is included in PaaS, On-premise license cannot be ported||On-premise license can be ported|
|Hybrid cloud||Works great with On-premise apps||Works great with On-premise apps|
|SSIS/SSAS||Needs to run on a separate VM outside DB and can use SQL DB as upstream to run analytics||Can run on a separate VM or within the same VM|
|Performance (DTU vs RAM/IOPS)||DTU is much more precise on how much load your DB can handle and perform than a D4 (or any VM size) since the actual cores allocated to IO processing is an estimate (could range 0- 95%). Example benchmark highlights a workload mix of 2:1 read-write for up to 100 concurrent users for a SQL P1. 100 concurrent users is a performance that can easily stack up or better a D4 SQL server (DS with premium storage and SSD may be better bit that a different price point).||Typically compared against an On-premise performance as units of
comparison (IOPS, RAM, Storage) can be compared.
|Security||Recent launch of Always Encrypted GA. Encryption at Rest available natively, lets you configure your own network policies for IPs/Ports. Data transferred over Https. Support for VNET coming soon (not yet supported)||Allows all levels of customization, all native SQL server security features available. However, could leave a lot to be accomplished by you however can exist within a VNET that may be pre-configured for security.|
|Data Transfer||You can use the Azure SQL Database automated backups to create a copy of your SQL database. You can create the database copy on either the same server or a different server. Additionally, Azure Data Factory has built in API that can be used to move the data (requires the source and target end points)||SQL server provides several built-in features including replication, export/import in addition to using a backup and restore
|Cost||It’s not necessarily a cost conversation but rather driven by the requirement. In general, if it is a new app you are developing, or plan to re-architect, PaaS may be attractive to your need while if it is an application you got from an ISV or wish to make minimal or no changes, IaaS may be the go to solution. While comparing costs, consider the TCO of overall solution including number of database instances that will be actually used (Production, Production-mirror, QA, Devs) and not necessarily individual databases or servers. For HA/DR scenarios (Clustering or Always ON) for IaaS, consider the cost of additional servers (minimum of two and a third one light weight to protect quorum) likewise, there would be an additional cost to enable HA on PaaS solution as well so essentially the requirements/ business need drive the cost comparison for most part. On a good note, SQL Server Developer version is now free for Visual Studio users. If you are not planning to use the Developer edition, PaaS may offer great cost effective alternative (imagine having your own Dev/QA DB running in <$100/month) and a SQL Server on Azure VM that can be turned on/off on demand for quick/time- bound Dev/QA exercise is not far off either.|