Are there benefits specific to SQL Server on Azure VM when using the SQL Server Data Files in Windows Azure feature? Aka using Azure blob storage container for database files versus an Azure VM data disk which itself is backed by Azure storage page blob?
The MS docs SQL Server data files in Microsoft Azure encourage the use of this feature with SQL on Azure VM but don't make clear a compelling advantage compared to using an Azure managed data disk.
The listed benefits apply to both forms of Azure storage AFAIK.
Are there any benefits specific to SQL on Azure VM?
Why use SQL Server data files in Microsoft Azure?
- Easy and fast migration benefits
- Cost and limitless storage benefits
- High availability and disaster recovery benefits
- Security benefits
- Snapshot backup
Per MS docs SQL Server data files in Microsoft Azure > Concepts and Requirements
This new feature uses Page blobs, which are more efficient when ranges
of bytes in a file are modified frequently.
Per MS docs, Introduction to Azure managed disks
Azure managed disks are stored as page blobs, which are a random IO
storage object in Azure. We call a managed disk ‘managed’ because it
is an abstraction over page blobs, blob containers, and Azure storage
accounts.
Best Answer
The biggest advantage of putting the database files on page blobs is for very large databases is you can use File-Snapshot Backups for Database Files in Azure. But it's a more advanced and somewhat more tricky feature than using Azure Disks, which are very simple and user-friendly.
So the basic guidance is to use Azure Disks, unless you have a scenario where you really need to avoid creating a copy of the data for backups, or restoring as a size-of-data operation. Note that for HA/DR using regular backup to URL or managed backup to Azure Blob storage is also much more useful than file snapshot backups, as you get Lifecycle Management, multi-region support, soft delete, and all the other features of blob storage of your backups.
And Azure Disks support things like Azure Backup and Azure Site Recovery, so they are compatible with enterprise-wide BC/DR solutions. If you store your databases directly on blobs, or in Azure Premium Files the data is not automatically associated with your VM from a infrastructure, management, and monitoring POV.