If you are using SQL Server in an Azure VM (IaaS) you have a number of options of where to store the database files (.mdf, .ldf, and .ndf). Most customers use managed disks, available in a number of offerings: Standard HDD, Standard SSD, Premium SSD, and Ultra SSD. Managed disks are highly recommended to use over unmanaged disks (see Azure Managed vs Unmanaged disks : The choice). You create and then attach one or more managed disks to the VM. Each VM comes with built-in storage, but this is a temporary storage drive, labeled as the D: drive, and is not persisted to Azure blob storage. You do not want to store your user database files or user transaction log files on the D: drive (see Performance best practices for Azure VM). The same goes for the C: drive, which holds the operating system, and should not be used for database files.
If using managed disks, the recommendation is to attach several premium SSDs to a VM. From Sizes for Windows virtual machines in Azure you can see for the size DS5_v2 you can add 64 of 4TB data disks (P50) yielding the potential total volume size of up to 256TB per VM, and if you use the preview sizes (P80) your application can have up to around 2PB of storage per VM (64 of 32TB disks). With premium storage disks, your applications can achieve 80,000 I/O operations per second (IOPS) per VM, and a disk throughput of up to 2,000 megabytes per second (MB/s) per VM. Note the more disks you add the more storage you get and the more IOPS you get, but only up to a certain point due to VM limits, meaning at some point more disks get you more storage but not more IOPS.
Ultra SSD is in preview and an option for data-intensive workloads such as SAP HANA, top tier databases, and transaction-heavy workloads and has a disk capacity up to 4PB (64 of 64TB disks) and 160,000 IOPS per disk with a disk throughput of up to 2,000 megabytes per second (MB/s) per VM. Ultra SSD has higher performance than Premium SSD so you can use fewer of them to achieve the performance you need, simplifying things (i.e. possibly not having to create a storage space) and saving costs without sacrificing IOPS (see Mission critical performance with Ultra SSD for SQL Server on Azure VM). Note that there is no SQL Server VM image that uses Ultra SSD from the Azure Marketplace yet.
In the past, after provisioning a SQL Server VM (a SQL VM created from an Azure Marketplace image), you had to manually attach and configure the right number of data disks to provide the desired number of IOPS or throughput (MB/s). Then you needed to stripe your SQL files across the disks or create a Storage Pool to divide the IOPS or throughput across them. Finally, you’d have to configure SQL Server according to the performance best practices for Azure VM.
A couple of years ago, Microsoft made this part of the provisioning experience (but only when choosing a SQL Server VM from the Marketplace, not a regular VM). When creating the VM you can easily configure the desired IOPS, throughput, and storage size within the limits of the selected VM size, as well as the target workload to optimize for (either online transaction processing or data warehousing). As you change the IOPS, throughput, and storage size, it will automatically select the right number of disks to attach to the VM. During the VM provisioning, if more than one disk is required for your specified settings, it will automatically create one Windows storage space (virtual drive) across all disks (see Windows Server 2012 Storage Virtualization Explained). For more details see Storage configuration for SQL Server VMs.
If you are using Ultra SSD, since there is no SQL Server VM image in the Azure Marketplace that uses it, you will need to implement storage spaces manually if you wish to use more than one data disk – see Implementing Storage Spaces Inside Azure Virtual Machines.
Keep in mind that Azure premium managed disks are just premium page blobs with some API make-up so they look like disks (disks and blobs use different APIs). Azure storage page blobs do not officially announce performance numbers per size while Azure premium managed disks announce this info, so that is why tables like this will show performance numbers for disks and not page blobs. Page blobs are one of three types of blob storage, the other two being block blobs and append blobs.
If you need extreme performance for SQL Server and are OK with not having high-availability for storage, the Lsv2-series VMs are optimized to use the local disk on the node attached directly to the VM rather than using durable data disks. This allows for greater IOPS / throughput for your workloads. It supports up to 19.2TB of storage. Your applications can achieve up to 3.4M I/O operations per second (IOPS) per VM, and a disk throughput of up to 22,000 megabytes per second (MB/s) per VM. However, you miss out on the high-availability benefits of Azure blob storage, so instead of using it for SQL Server, it is better used for NoSQL stores such as Apache Cassandra and MongoDB which replicate data across multiple VMs to achieve persistence in the event of the failure of a single VM.
Another option for storing the database files is Azure blob storage. I see this used for some on-prem SQL Server installations when the latency caused by accessing blob storage from on-prem is not a big deal (see SQL Server data files in Microsoft Azure and SQL Server 2014 and Windows Azure Blob Storage Service: Better Together). It is also sometimes used when using SQL Server in a VM with a large database (3-30 TB range) and a fast backup/restore is needed, as a file-snapshot backup (BACKUP DATABASE using WITH FILE_SNAPSHOT) could be done very quickly (note a file-snapshot cannot be done when database files are on managed disks, so you must do streaming backups). Other benefits that come with storing the database files in Azure blob storage is Instant Log Initialization for SQL Server in Azure, fast consistency checking, and reporting off of a restored copy of the snapshot. Be aware there are some limitations: you are limited to a database size of 35TB and geo-replication for your storage account is not supported (if a storage account is geo-replicated and a geo-failover happened, database corruption could occur), there is no local SSD cache to potentially improve storage performance and the same network bandwidth is shared between the VM network traffic and the database storage traffic, which can affect network intensive applications. For more info see SQL Server VLDB in Azure: DBA Tasks Made Simple.
In looking at the PaaS version of SQL Server, Azure SQL Database, here is the underlying persistent storage that is used for its databases for the various options:
- Singleton: DTU Basic tiers (max 2GB database size) and Standard tiers (max 1TB database size) use blob storage, Premium tier (max 4TB database size) uses a locally attached SSD; vCore General Purpose tier (max 4TB database size) uses premium blob storage, Business Critical tier (max 4TB database size) uses a locally attached SSD
- Managed Instance: General Purpose tier (max 8TB database size) uses premium blob storage (see Storage performance best practices and considerations for Azure SQL DB Managed Instance (General Purpose)), Business Critical tier (max 4TB database size) uses a locally attached SSD
- Hyperscale: Uses standard blob storage (max 100TB database size) but achieves very high performance due to multiple SSD-based caching tiers
More info:
Azure SQL Database high availability
High-performance Premium Storage and managed disks for VMs
About disks storage for Azure Windows VMs
Tutorial: Use Azure Blob storage service with SQL Server 2016
Storage Configuration Guidelines for SQL Server on Azure VM
How to Choose the Correct Azure VM Disk Tier
