Comparing SQL Server in Azure IAAS VM with Azure SQL Database
There has been quite a lot of discussion about how Azure SQL Database PAAS Service compares with running SQL Server in a VM on Azure using the IAAS method. (Infrastructure as a Service). Here are the high level items to look into, before deciding on which one suits your need.
SQL Server in Azure VM
• You would need a specific version of SQL Server or Windows. The version of SQL Server should be compatible with the Operating system, just like you would on-premises.
• If you need instance-level SQL features like System Tables, SSIS for ETL purposes, Reporting Services, Distributed queries (e.g. Agent Job, Linked Servers, DTC)
• You are responsible for configuring/managing SQL Server and Windows. OS level patching and security updates are provided by Microsoft.
• Great for migrating existing On-Premise Line of business Applications. SQL Hybrid topologies are also pretty flexible and can co-exit.
• You access a VM using Remote Desktop software where your SQL Server is installed
• You manage SQL Server and Windows Backups. You would ideally need a DBA or a support technician who will do the patching, high availability, or backups.
• You select the SQL Server and Windows version and edition.
• There are different VM sizes available: From Shared A0 (1 core, 1GB mem, 100GB) to a G5 system (32 cores, 512GB mem, 32TB) system.
• Microsoft will only ensure VM availability with a SLA of 99.95% in an Availability Set. Microsoft will not provide any SQL level SLA.
Azure SQL Database
• You don’t need a specific version of SQL Server or Windows to choose. You only choose a database type, i.e. Basic, Standard or Premium.
• You cannot access the VM or RDP into it. If you don’t need instance-level SQL features, this is the feature to go with.
• Microsoft will take care of VM maintainance, backup’s and availability. You don’t have to configure and manage SQL Server or Windows.
Great for new apps
You access a database
Database is fully managed
Runs latest SQL Server build with the latest features.
• You can choose different Database sizes: From Basic (2GB, 5tps) to Premium (500GB, 735tps)
• Database availability SLA of 99.99% without any Availability set requirement.
T-SQL surface area is also less in Azure SQL Database. Please see notes here.