Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan Technical Reviewers

Azure Infrastructure Services fundamentals

This section provides an overview of the Azure Infrastructure Services including some key considerations that can have a direct impact on the performance of your SQL Server workloads.

Azure Infrastructure Services lets you access scalable, on-demand infrastructure using Virtual Machines and Virtual Networks. You can use Azure to store data, to create virtual machines for development and test, and to build web applications or run other applications. Before you start reading this article, you should understand the fundamental concepts of Azure, such as cloud services, virtual machines, virtual networks, storage accounts, and so on. For more information, see Azure Documentation.

Azure VM configuration options

Virtual machine size

Azure virtual machines are available in different sizes and resource options in terms of number of CPU cores, memory capacity, maximum disk space, bandwidth, and so on. For the latest information about the supported virtual machine sizes and resource options, see Virtual Machine and Cloud Service Sizes for Azure. We recommend that you review all these virtual machine size options and choose the ones best suited for your SQL Server workloads.

Network bandwidth

The network traffic includes all traffic between client applications and SQL Server in Azure VM and, any other communication that involves a SQL Server process, or other processes in a virtual machine, such as ETL packages or backup and restore operations. We recommend that you choose a larger virtual machine size with more network bandwidth.

Important note: Network communications generated by accessing data disks and the operating system disk attached to the virtual machine are not considered part of the bandwidth limits.

Disk types and configurations

Azure Virtual Machines provide three types of disks:

• 
  Operating system disk (persistent): Every virtual machine has one attached operating system disk (C: drive) that has a limit of 127 GB. You can upload a virtual hard disk (VHD) that can be used as an operating system disk, or you can create a virtual machine from a platform image, in which case an operating system disk is created for you. An operating system disk contains a bootable operating system. It is mostly dedicated to operating system usage and its performance is optimized for operating system access patterns such as boot up.
  
• 
  Data disk (persistent): A data disk is a VHD that you can attach to a virtual machine to store application data. Currently, the largest single data disk is 1 terabyte (TB) in size. You can specify the disk size and add more data disks (up to 16, depending upon virtual machine size). This is useful when the expected database size exceeds the 1 TB limit or the throughput is higher than what one data disk can provide.
  
• 
  Temporary local disk (non-persistent): Each virtual machine that you create has a temporary local disk, the D: drive and which is labeled as TEMPORARY STORAGE. This disk is used by applications and processes that run in the virtual machine for transient storage of data. It is also used to store page files for the operating system. Temporary disk volumes are hosted in the local disks on the physical machine that runs your virtual machine (VM). Temporary disk volumes are not persistent. In other words, any data on them may be lost if your virtual machine is restarted. Temporary disk volumes are shared across all other VMs running on the same host.
  

Important note: Proper configuration of your Azure disks (operating system and data) is one of the most important areas to focus on when optimizing the performance of your SQL Server workloads. Both operating system disks and data disks are stored as VHD files in the individual page blobs hosted in the Azure Storage Blob Service. Each blob has its own capacity limits based on the intrinsic storage architecture. For more information, see the following blog posts:

• 
  Azure Storage Scalability and Performance TargetsAzure
  
• 
  Azure Storage: A Highly Available Cloud Storage Service with Strong Consistency
  
• 
  Data Series: Exploring Azure Drives, Disks, and Images
  
• 
  Erasure Coding in Azure Storage
  

Each data disk can be a maximum of 1 TB in size. Depending upon your virtual machine size, you can attach up to 16 data disks to your virtual machine. I/Os per second (IOPS) and bandwidth are the most important factors to consider when determining how many data disks you need.

Storage capacity planning is similar to the traditional ‘spindle’ count in an on-premises storage design. In this article, we discuss various approaches you can use to spread I/O operations across one or more data disks, and discuss how to use SQL Server performance features, such as compression, to maximize the performance of your SQL Server workloads.

Disk cache settings in Azure virtual machines

In Azure virtual machines, data of persisted drives is cached locally to the host machine, thus bringing the data closer to the virtual machine. Azure disks (operating system and data) use a two-tier cache:

• 
  Frequently accessed data is stored in the memory (RAM) of the host machine.
  
• 
  Less recently accessed data is stored on the local hard disks of the host machine. There is cache space reserved for each virtual machine operating system and data disks based on the virtual machine size.
  

Cache settings help reduce the number of transactions against Azure Storage and can reduce disk I/O latency in some scenarios.

Azure disks support three different cache settings: Read Only, Read Write, and None (disabled). There is no configuration available to control the size of the cache.

• 
  Read Only: Reads and writes are cached for future reads. All writes are persisted directly to Azure Storage to prevent data loss or data corruption while still enabling read cache.
  
• 
  Read Write: Reads and writes are cached for future reads. Non-write-through writes are persisted to the local cache first, then lazily flushed to the Azure Blob service. For SQL Server, writes are always persisted to Azure Storage because it uses write-through. This cache setting offers the low disk latency for light workloads. It is recommended for the operating system and data disks with sporadic disk access. As the physical local disks are shared by all tenants on the host, you may observe increased latency when the total workload exceeds the performance of the physical host local disks.
  
• 
  None (disabled): Requests bypass the cache completely. All disk transfers are completed against Azure Storage. This cache setting prevents the physical host local disks from becoming a bottleneck. It offers the highest I/O rate for I/O intensive workloads. Note that I/O operations to Azure Storage do incur transaction costs but I/O operations to the local cache do not.
  

The following table demonstrates the supported disk cache modes:

Disk type	Read Only	Read Write	None (disabled)
Operating system disk	Supported	Default mode	Not supported
Data disk	Supported	Supported	Default mode

Note that temporary disks are not included in this table because they are not Azure disks, but are implemented using local attached storage.

Important notes:

• 
  Cache can be enabled for up to 4 data disks per virtual machine (VM).
  
• 
  Changes to caching require a VM restart to take effect.
  

For specific recommendations and best practices for cache settings and performance behaviors, see the Azure virtual machine disks and cache settings section in this article.