Although working on cloud-based solutions has become routine, newbie DBAs have to figure out how to seamlessly work on-premises as well as in the cloud. For a SQL Server® administrator, cloud platforms, such as Amazon® and Azure™, which function as a service on an infrastructure, pose a completely different set of challenges. Many times administrators are not aware of the nuances that accompany these deployments.
In this post, I’ll discuss a few of the offerings provided by SQL Server inside a VM on Azure from Microsoft®. I’ll be sharing some of the things I learned while working on VM.
Create OS striping
The storage space feature is available from Windows® 2012 and onward. For Azure VMs running on Windows Server® 2008 R2 and previous releases, the only striping technology available is striped volumes for dynamic disks.
- To create an OS striping, follow these steps: Open disk management on the SQL Azure virtual machine.
- Right-click the unallocated space on the disk.
- Choose simple volume.
- Complete the steps in the wizard.
- In the format partition, change the allocation unit size to 64KB.
- Select perform a quick format. Follow the wizard through to completion.
Storage space is a new function available in Windows 8.0+ and Windows 2012+. This feature helps you deploy large volumes of storage solutions with cost effective, highly available, scalable, and flexible solutions.
A standard disk in Azure has IOPs limits. A storage account has a maximum IOPs limit of 20,000. Avoid storing multiple heavily used data disks in the same storage account. Multiple data disks can be used to stripe storage space, or OS striping to increase the IOPs. You can create a single storage pool with 16 data disks. This helps provision 8000 IOPs for the volume. Exceeding the IOPs limit of the given volume would create random high latency. Therefore, avoiding or increasing the IOPs should be your focus.
If you have not configured the disk properly, you are likely to see the following error log messages:
2015-12-06 12:06:44.510 spid4s SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [XXXXXXXXXX] in database [YYYYYYY] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x0000000dab0000
2015-12-06 17:50:57.790 spid4s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [XXXXXXXXX] in database [YYYYYY] (2). The OS file handle is 0x0000000000000498. The offset of the latest long I/O is: 0x00000011c85000
To improve the performance and throughput of your backup, use the following parameter combinations:
- Block_size (test with different values): Use 64K.
- MaxTransferSize (test with different values): Use 1MB.
Striping multiple files will increase the throughput.
Some of these recommendations can also be used for on-premises deployments. That said, disk issue problems have always been something to consider when working with Azure. I hope this post will help you when deploying SQL Server® on a VM.
Leave a Reply