A best practice for SQL Server is to store the data/log/backup files on a disk other than the OS Disk. This transfers over to SQL Server installed on an Azure IaaS VM. Due to storage requirements it may be necessary to have more than one physical disk compose a logical disk for these log files. Another best practice to increase IOPS (Input Output Operations) on SQL Server running on an Azure VM is to store each of these physical disks in their own Azure blob storage account.
However, the Azure Management portal will not give us the ability during VHD provisioning to select a preferred storage account. By default the VHD will be created at the storage account where our OSDisk is created. Therefore you must take the following steps to attach the disks one-by-one to the VM. Once that is done you create one spanned disk for data, and one for logs/backup, using the Computer Management Admin console. Then within SQL Server Management Studio you replace the default C:\xxx locations for SQL Server data, logs, and backup files with the spanned disk volumes.
To create and attach the data disks to the VM you must do the following:
- From the Azure Portal for the VM select “Attach an empty disk” and create a new VHD. By default it will be created at the default OSDisk Azure storage location X for the VM
- Create a new Azure destination storage account Y. You will create one account for each VHD as best practice
- With ClumsyLeaf Cloud Explorer expand both the destination storage account X (left hand pane) and the source storage container with the blob containing the new VHD (right hand pane) from step 1.
- Drag/copy the VHD blob from original storage location X (right hand pane) to the new destination storage location Y (left hand pane)
An alternative way to do the VHD copy is to use the Azure command line from your local machine defined in this Aditi blog entry http://blog.aditi.com/2012/11/how-to-copy-files-between-windows-azure.html. You will first need to download the NodeJS tool first from here to your local machine. A third copy option is to use the PowerShell command Add-AzureDisk with the –ImportFrom parameter.
- In the Azure Portal select Detach Disk for the specific VM
- Once the disk is detached go to the Azure Portal VM screen and click on Disks, the select Attach Created Disk
- In the Browse Cloud Storage dialog for VHD URL field locate the storage blob to which you copied the VHD to in step 4, and select Open and complete the dialog.
- Repeat the steps again for as many disks as you need to attach to that the VM running SQL Server.