Best Practices for Microsoft SQL on Azure with Cloud Block Store
Azure SQL Virtual Machine Recommended Best Practices with Cloud Block Store
Overview
Proper configuration of SQL server on the Azure platform used in conjunction with Pure Cloud Block Store (CBS) is a critical requirement to ensure that maximum performance is achieved and that customers do not unnecessarily waste expenditure. This KB article will serve as a landing page and attempt to consolidate, or otherwise point to, best practices, lessons learned and pitfalls to avoid when setting up SQL server to run with Pure Cloud Block Store on Azure.
This guide is intended to be a resource both for net new Azure SQL VMs as well as previously deployed Azure SQL VM(s) that are to be connected to a new Cloud Block Store instance.
Azure Networking Recommended Best Practices
Azure Accelerated Networking
Beginning with release 6.2.4 of Cloud Block Store, all newly deployed CBS instances by default have enabled support for Azure Accelerated Networking. The differentiator of Azure Accelerated Networking relative to their traditional networking architecture is that it offloads much of the networking stack from the virtual machine hardware onto the network card, bypassing the virtualized switches. This in turn leads to less latency and VM host CPU utilization so that applications run faster and more consistently.
The following Windows operating systems support Accelerated Networking and we strongly recommend using them with CBS whenever possible for optimal performance on each NIC attached to the VM:
- Windows Server 2022 Standard/Datacenter
- Windows Server 2019 Standard/Datacenter
- Windows Server 2016 Standard/Datacenter
Previously deployed Windows Azure virtual machines may not have Accelerated Networking enabled, since it is a relatively recent feature from Azure. To check whether Accelerated Networking is enabled or not, follow the next set of instructions.
Start with clicking on the Networking button on the Azure VM blade.
For each NIC, the below screenshot will show whether or not Accelerated Networking is Enabled or Disabled.
If Accelerated Networking is showing as Disabled, click on the Network Interface and then click on the Enable accelerated networking option shown below. Note that this can be done while the VM is running.
For more information and instructions on how to deploy Windows-based Azure VMs with Accelerated Networking, please see this article.
Considerations for Network Bandwidth and Number of NICs on Azure Virtual Machines
A common question for Azure Virtual Machines is whether or not additional network interface cards (NICs) will improve performance when added to the VM and subsequently connected to Cloud Block Store. Yet another common question is how much network bandwidth should be provisioned with your Azure Virtual Machine type to ensure optimal performance. In this section we will provide guidance for both of these questions.
Starting with the number of NICs to assign to a VM: There may be situations where multiple NICs attached to an Azure VM is a requirement (e.g. having 1 NIC for management and another NIC for iSCSI traffic), however, adding additional NICs to an Azure VM for the same service (e.g. iSCSI traffic) will not increase the overall bandwidth capability of the VM. The amount of available bandwidth is selected when the VM-type is chosen. In fact, that network bandwidth is drawn from a single pool that each of the attached NIC(s) will utilize and share.
A single NIC for an Azure SQL VM will provide maximum performance between the Cloud Block Store instance, and the SQL virtual machine.
When it comes to how much network bandwidth a customer should provision for a given Azure SQL VM, the answer will depend on the size of the SQL DB, the required database throughput needed and the type of CBS instance being used (V10 vs V20). Cloud Block Store controller VMs are themselves Azure VMs so they will have a provisioned amount of bandwidth available for iSCSI, management and other required network operations. In order to maximize throughput performance between the SQL VM and the CBS instance hosting SQL data, we recommend choosing a SQL VM type that is either identical to, or slightly below the bandwidth values of the two CBS option controller types:
| Azure CBS Array | Azure Controller VM | Provisioned IOPS |
|---|---|---|
| V10 | D32s_v3 | 51,200 |
| V20 | D64s_v3 | 80,000 |
Azure VM Recommended Best Practices
Regions and Azure Zones
An obvious but important consideration for using SQL with Cloud Block Store is to confirm that the Azure VM and the Cloud Block Store instant are deployed in the same Azure zone and are using the same vNet wherever possible. This ensures the shortest network round trip and also prevents unexpected ingress and egress charging.
A further recommendation is to make certain that the Azure VM and the CBS instance are deployed into the same Azure zone. Specifying an Azure zone is optional when deploying a new Azure VM, however, we recommend placing it into the same zone as CBS as not selecting a zone can allow the VM to 'float' between zones upon reboot/start-up and may lead to sub-optimal performance relative to pinning the VM to the same zone as CBS. Note that it is not possible to assign an Azure zone to, or migrate a VM or CBS to a different Azure zone once it has been created.
The below two screenshots highlight where this selection is made during the deployment process for an Azure VM and CBS, respectively.
Azure Virtual Machine Type for SQL
For production SQL workloads, Azure has recommended using virtual machine types that are in the Ebdsv5-series. These virtual machines are purpose-built for memory-intensive applications and they also support and have Accelerate Networking enabled by default upon creation. Of course, other virtual machine types that support SQL can certainly be used, especially for smaller test/dev SQL instances.
Further reading on this VM type and SQL recommendations on VM type can be found below:
Ebdsv5-series Azure Virtual Machine Documentation
In-Guest iSCSI Setup
Connectivity to Cloud Block Store from an Azure VM is established and maintained via the Windows iSCSI Initiator service. This service is used to mount and maintain the external disks presented by Cloud Block Store to the Azure VM instance. This section of the guide will provide basic instructions for how to setup iSCSI specifically for use within Azure.
Instructions for enabling and establishing initial iSCSI connecitivy between Pure Cloud Block Store and an Azure virtual machine can be found here:
Note that the iSCSI setup steps shown in this link above must be completed prior to moving on to the below subsequent sections.
Establishment and Number of Unique iSCSI Sessions
Once iSCSI connectivity has been established between Pure Cloud Block Store and the Azure SQL VM, further optimizations are required in order to achieve maximum performance as well as automated failover in the event that a single path, or CBS controller goes offline.
Establishing multiple iSCSI sessions between the Azure VM and both iSCSI ports on the Cloud Block Store controller VMs will ensure failover as well as providing a larger data path to maximize performance.
We recommend establishing a minimum of 16 iSCSI sessions per controller VM (32 total) at minimum in order to achieve throughput levels required for sustained read and writes between SQL and CBS.
The procedure for setting up iSCSI sessions is covered in the Setup iSCSI on Windows Server article.
iSCSI Best Practices
There are a few registry and Windows operating system-level refinements that we recommend making in order to maximize iSCSI performance.
The following KB article provides instructions for how to implement these best practices.
MPIO Setup and Multi-Path Policy Recommendations
Multipath-IO (MPIO) is a critical feature that establishes multiple redundant paths between the Azure Windows VM and the Cloud Block Store instance. This redundancy ensures that a planned or unplanned initiator outage on either the VM or CBS will automatically failover to an alternate available path. Furthermore, selecting a MPIO policy will also provide intelligent load balancing across available initiators for optimal performance.
MPIO setup and configuration is covered via the following KB articles and need to be followed in order below. Notes specific to CBS are also included.
- Installing Multipath-IO
- Configuring Multipath-IO
- Make certain to follow the instructions included in the iSCSI section of this KB since CBS utilizes the iSCSI protocol.
- Setting Multipath-IO Policy
- We recommend using the LQD (Least Queue Depth) MPIO policy with CBS due to our recommendation to use 16 iSCSI paths per controller for best performance.
NTFS Volume Setup and Formatting
With an in-guest iSCSI volume provisioned from CBS and connected to the Windows SQL VM instance, the next step is to setup and format that volume within the Windows operating system. Disk format type and allocation size are important selections to make for optimal performance of SQL volumes.
To start, open the Windows Disk Management cmdlet via right-clicking on the Windows icon and selecting Disk Management.
If the new CBS disk has already been detected by the Windows OS, the following window will spawn when Disk Management is launched. If it does, accept the default options and click OK to initialize the disk.
If the above window does not open when you launch Disk Manager, you may need to scan for the new CBS volume. To do that, select the Action menu and then click on Rescan Disks.
When the new volume is found, confirm that the capacity shown matches the capacity that you allocated when the volume was created in CBS. Next, right click on the Disk and select Online to bring it online.
Next, right click on the Unallocated space and select New Simple Volume...
Within the New Simple Volume Wizard, it is fine to leave the volume size as the maximum (1) and the click Next (2).
Optionally, you can assign a drive letter to the new volume. Then click Next.
Confirm that the file system is set to NTFS. For the allocation unit size pull-down menu, select the 64K option.
Optionally provide a volume label name and then click Next.
Click Finish on the next screen to complete formatting the volume. It is now ready for use.
Microsoft SQL Server Recommended Best Practices
Version of SQL Server
The flavor of SQL Server licensing in use (Web vs. Standard vs. Enterprise) has a direct impact in the amount of system resources that can be allocated for use with the SQL instance. The below screenshot demonstrates the number of SQL editions available just for Windows Server 2022.
The below table outlines the maximum amount of VM CPUs and RAM that can be allocated to the SQL service based upon SQL version. This is an important consideration as it can be wasteful to oversize a VM type if only a Web or Standard license is available.
| SQL Version | Maximum Compute Capacity for SQL Instance | Maximum Memory Utilization for SQL Server Instance |
|---|---|---|
| Web | 4 sockets or 16 cores | 64 GB |
| Standard | Lesser of 4 sockets or 24 cores | 128 GB |
| Enterprise | Operating System Maximum / Azure VM Type | Operating System Maximum / Azure VM Type |
For large-scale production workloads, it is likely the SQL Enterprise will be required in order to utilize enough virtual machine resources to make performance acceptable.
Pure Storage SQL Server Best Practices
Pure Storage maintains recommended best practices for use with SQL server at the below link:
Customers who are interested in pairing two Cloud Block Store instances with ActiveCluster and using it for transparent SQL DR should review the following reference architecture. Even though the document is focused on our on-premises FlashArray, the majority of concepts and steps to set it up are the same.
TempDB Best Practices in Azure
TempDB is an ephemeral SQL system database where data is temporarily cached before being written to a permanent location. Because of its ephemeral nature, performance is the most important factor to consider. Enterprise features such as snapshots and volume resiliency should not be prioritized for TempDB so native block storage offerings from Azure provide the optimal solution.
The following article describes how to optimize TmpDB for SQL server:
TempDB Performance Optimization
Whenever possible, our recommendation for TempDB is to store it on the attached SQL data volume that is provisioned with the SQL VM upon deployment. This scenario is ideal as no additional cost will be incurred beyond the VM expense itself. However, in some scenarios very large SQL instances may overrun the included SQL data volume (from a capacity and/or performance standpoint) and in that scenario we recommend provisioning and attaching an additional Ultra SSD to the SQL VM to serve as the new TempDB location.
High Availability Disaster Recovery
High Availability Disaster Recovery (HADR) is an umbrella term that covers ensuring that a given SQL instance will be online in seconds or minutes during an unplanned local outage/event as well as backing up the SQL data in a durable fashion so that it can be recovered either locally or remotely in the event of a more widespread unplanned outage. Customers interested in setting up a High Availability Disaster Recovery (HADR) SQL solution are encouraged to review Microsoft's best practice guide:
Useful SQL Troubleshooting Articles and Options
Customers experiencing performance, resiliency or other issues with SQL and CBS should consult the following articles for further steps to diagnose, troubleshoot and resolve such issues.