While Azure SQL Database offers a number of advantages inherent to its Database-as-a-Service nature, there are scenarios where capacity or functionality requirements force you to resort to an alternative approach and deploy SQL Server in an Azure virtual machine. When using this deployment option, you can take advantage of several Azure Infrastructure-as-a-Service (IaaS) features that enhance performance of the SQL Server database engine. We will explore a couple of these enhancements in this article. In particular, we will focus on increased I/O throughput facilitated by the Solid State Drive (SSD) storage available on most Azure virtual machines (VMs).
Azure offers a wide variety of VM sizes and types. For their comprehensive, up-to-date listing, refer to Microsoft Azure documentation. A cursory overview of this listing will allow you to determine that a majority of them (in essence, all of them except for A VM sizes) include an SSD-based temporary disk. To fully understand the meaning of the term temporary, it is necessary to briefly review the way VM storage is provisioned. Every Azure VM has one disk hosting the operating system (C: drive in Windows). The corresponding virtual disk (VHD) file does not actually reside on the same Hyper-V host where the VM is running, but instead it is persisted to a separate storage layer and accessed over a high-speed network connection. This design ensures that the Hyper-V host failure does not impact the integrity and persistency of the VM operating system disk. The same approach is used for provisioning any additional disks (referred to as data disks) you decide to attach to an Azure VM. Note that this benefit does not apply exclusively to scenarios that involve a Hyper-V host failure. For example, if you stop and deallocate the VM, its association with the Hyper-V host is no longer maintained. This means that if you decide to bring that VM back online, it will very likely reside on a different host. Storing operating system and data disks in a dedicated storage tier ensures that they remain available regardless of the VM placement.
However, in addition to the operating system disk, every Azure VM also has an extra virtual disk that resides directly on the Hyper-V host local storage. The content of this disk, mounted as D: drive, is bound to be lost if the VM is moved to a different Hyper-V host (as the result of the host failure or the VM deallocation). This makes such a disk unsuitable for storing any data that is business critical or which must persist across VM reboots. On the other hand, D: drive is the preferred choice for storing temporary content, such as Windows paging file (this happens to be the default configuration on VMs you deploy from the Azure image gallery). If the Hyper-V host includes SSD local storage, then you are likely to see performance benefits resulting from improved speed of paging operations. In addition, there is also a monetary advantage, since paging does not require access to the Azure storage, which in turn would result in storage transactional charges.
In the context of SQL Server deployments, SSD-based temporary disk appears to be the perfect match for tempdb. Since tempdb gets populated following a SQL Server restart, it is not necessary to persist its content. However, implementing this scenario warrants some additional considerations, which involve tempdb directory structure and corresponding file system permissions. More specifically, by default, the SQL Server service account does not have sufficient permissions to create tempdb directly in the root of the D: drive (and attempting to place it there during the SQL Server setup will result in an installation failure). As a result, you have two options:
- moving tempdb to the root of D: drive following the installation of SQL Server and adding the SQL Server service account to the local Administrators group
- placing tempdb in a directory on D: drive and creating an operating system startup-triggered scheduled task that creates the directory and starts the SQL Server service, and any other services that depend on it (obviously this requires modifying the start mode of these services to Manual).
When deciding whether to use SSD-based D: drive for tempdb, you should keep in mind that it is also possible to implement VMs that support Premium Storage, which offers a similar performance benefit while at the same time ensuring data persistency. Obviously, in this case, there are extra charges involved (refer to Azure Storage Pricing for details and keep in mind that with Premium Storage, you pay for the amount of storage you provision, rather than for the amount that you actually use), so make sure to weigh potential performance benefits against a higher cost. The general guideline is that local, SSD-based tempdb might be a good fit for workloads that rely heavily on its usage (such as, creating a large number of temporary objects or performing complex joins). For details, refer to Performance best practices for SQL Server in Azure Virtual Machines.
Starting with SQL Server 2014, you can apply the same approach (using either of the two options outlined above) when configuring Buffer Pool Extensions, which uses the SSD storage to extend the database working set, relying on SSD as a secondary cache (for more details regarding this feature, refer to the MSDN library).
