Q: I'm running SQL Server on an Azure IaaS virtual machine, and I need better disk performance. Should I use Storage Spaces within the virtual machine across multiple disks?

A: No. (Well . . . maybe not.) Although Storage Spaces is fully supported in Azure and should be used to combine multiple data disks for normal workloads to achieve larger volumes with higher IOPS, you shouldn't necessarily use Storage Spaces with SQL Server. Instead, the best option for the highest level of performance with SQL Server is to use the native SQL Server distribution feature, which will write the database over multiple disks that are exposed to it. However, this approach has some trade-offs in terms of simplicity and flexibility.

Related: The Azure Experience

SQL Server works by having a database that contains a file group (although it can contain multiple file groups); the file group contains one or more files, with each file possibly stored on different physical disks. Objects are written to the file group, which then uses a proportional fill algorithm to ensure all files in the file group contain roughly the same amount of data and therefore distribute the load. You must manually add the files for each disk to the file group. If in the future you wanted to add another disk, you'd need to add another file on that new disk to the file group. However, because there's no automatic reoptimization of data to spread equally, a large amount of the new data would be written to the new disk, which might affect performance.

This situation contrasts with Storage Spaces, which lets you just add disks to the Storage Pool. Disks are then automatically used as needed based on the data, and background optimization occurs when necessary.

Although SQL Server native distribution provides the best performance, Storage Spaces offers a simpler experience at the expense of some performance. The decision is up to you, depending on what's most important.

*********************************

Check out our sister site, SQL Server Pro for more information on SQL Server and Microsoft Azure.