2

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.

1

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.

0

They both based on page blob - performance should be pretty the same - mostly depends on the network speed.

  • Managed disk has extra layer as file system.
  • Managed disk has fixed size - so you allocate entire disk while using only part of it.
  • Data files in azure can just auto grow when needed.

I keep log file on local disk - for performance reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.