This is my eighth post in a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This time, it’s a little trick on how to make life easier for SQL Server on an Azure Virtual Machine. This tip applies to standard on-premises setups as well, although it’s not always as easy as running a PowerShell script to magically conjure a bunch of disks, on your own servers.
Prerequisites
In order to leverage this tip, you will need an Azure VM (or an on-premises server, where you can add disks) and some administrative permissions.
Things to do in Azure
First of all, you need to add the Data Disks to you Virtual Machine. This can be done in two ways; (1) You can do this through the Portal. Select the VM you want to work with, click All Settings->Disks->Attach New.
This will bring you to the final dialog, where you punch in the specifics for your new disk.
Or you can do it using Powershell, using Add-AzureDataDisk.
Windows Server
Once the disks have been attached to the VM, it’s time to do some magic in Windows. Mind you, a long series of screenshots. First, you’ll have to create a Storage Pool, next configure a Virtual Disk and finally a Volume (Disk).
It all begins with in the Server Manager, where this illustration should take you through the steps to create a Storage Pool:
Once the Storage Pool is created, you head on to create the Virtual Disk:
Finally you need to create a new Volume:
Benefits
The obvious benefit is throughput. The more disks you add to your stripe, the more theoretical throughput you’ll get. As an example, is ran a very quick test, using CrystalDiskMark
Beware: Do not use the installer, as it contains adware. Get the portable edition (.zip).
As displayed in above screen shots, the single Azure Standard Storage VHD gives you (as promised) about 500 IOPS. Striping eight (8) of those, will roughly give you eight (8) times the IOPS, but not same magnitude of [MB/s] apparently. Still, the setup is better off, after, rather than before!
Do mind, that there are three levels of storage performance; P10, P20 and P30. For more information, read this.
Pingback: Testing Your #SSAS Cubes w/ Visual Studio – T-SQL.dk
Note that there are some VERY special considerations if you stripe disks (w Windows Storage Spaces pools) in Azure if used in combination with Windows Clustering, such as used with Avail Groups. Very specific steps required (from KB article) to accomplish this combination.
Pingback: T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toys – T-SQL.dk