Skip to main content
Pure Technical Services

Best Practices for Microsoft SQL Server | Pure CBS on Azure

Currently viewing public documentation. Please login to access the full scope of documentation.

Azure Virtual Machine Recommended Best Practices for SQL Server 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 VMs that will be running SQL Server as well as previously deployed Azure VM(s) with SQL Server 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.  

Windows Server versions 2016 and later support Accelerated Networking, both Standard and Datacenter Editions. Pure strongly recommends using Windows Server that supports Accelerated Networking with CBS whenever possible for optimal performance on each NIC attached to the VM.

For more information on Accelerated Networking, see the Microsoft Learn topic "Accelerated Networking overview".

Previously deployed Azure virtual machines with Windows Server may not have Accelerated Networking enabled, since it is a relatively recent feature from Azure.

To check whether Accelerated Networking is enabled or not, first click on Networking under Settings for a selected Azure VM as shown in Figure 1.

AC-1.png

Figure 1. Networking settings in Azure

For each NIC attached to the VM, the Azure Portal will show if Accelerated Networking is Enabled or Disabled. The example in Figure 2 shows that Accelerated Networking is enabled.

AC-2.png

Figure 2. Example where Accelerated Networking is enabled.

If Accelerated Networking is showing as Disabled, click on the Network Interface and then click on the Enable accelerated networking option shown in Figure 3. 

This can be done while the VM is running.

AC-3.png

Figure 3. Enabling Accelerated Networking in the Azure Portal

For more information and instructions on how to deploy Windows-based Azure VMs with Accelerated Networking and PowerShell, please see the Microsoft Learn article "Use Azure PowerShell to create a VM with Accelerated Networking".

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 VM running SQL Server will provide maximum performance between the Cloud Block Store instance, and the VM.

When it comes to how much network bandwidth a customer should provision for a given Azure VM running SQL Server, the answer will depend on the size of the database, 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 Server VM and the CBS instance hosting SQL Server data, we recommend choosing a 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 Availability Zones 

An obvious but important consideration for using SQL Server with Cloud Block Store is to confirm that the Azure VM and the Cloud Block Store instant are deployed in the same  region and availability 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 availability zone in an Azure region.  Specifying an availaibility zone is optional when deploying a new Azure VM, however, we recommend placing it into the same zone as CBS. Not selecting an availability 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 availabilty zone as CBS.  Note that it is not possible to assign an availability zone to, or migrate a VM or CBS to a different availability zone once it has been created.

The below two screenshots highlight where this selection is made during the deployment process for an Azure VM (Figure 4) and CBS (Figure 5)., respectively.

AZ-1.png

Figure 4.  Selecting the region and availability zone for a VM

AZ-2.png 

Figure 5. Selecting the region and availability zone for CBS

Azure Virtual Machine Type for SQL Server

For production SQL Server workloads, Azure has recommended using VM 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 Server can certainly be used, especially for smaller test/dev SQL Server instances.

Further reading on this VM type and SQL Server recommendations on VM type can be found below:

Ebdsv5-series Azure Virtual Machine Documentation

SQL Server Performance Guidelines 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 Server 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.

  1. Installing Multipath-IO
  2. Configuring Multipath-IO 
    1. Make certain to follow the instructions included in the iSCSI section of this KB since CBS utilizes the iSCSI protocol.
  3. Setting Multipath-IO Policy
    1. 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.
 

SQL Server Recommended Best Practices

This section will discuss the best practices for SQL Server for CBS deployments.

Pure Storage SQL Server Best Practices

Pure Storage maintains recommended best practices for use with SQL Server in the topic Best Practices for Microsoft SQL Server on FlashArray. Although the title says FlashArray, it contains general best practices for all SQL Server deployments including CBS.

Customers who are interested in pairing two Cloud Block Store instances with ActiveCluster and using it for transparent SQL Server DR should review the document SQL Server with ActiveCluster. Even though the architecture is focused on our on-premises FlashArray, the majority of concepts and steps to set it up are the same.

Formatting Volumes for Use with SQL Server

With an in-guest iSCSI volume provisioned from CBS and connected to the Windows VM instance running SQL Server, 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 Server volumes. To see Pure's best practices for formatting disks for SQL Server, consult the Formatting Volumes section of the document Best Practices for Microsoft SQL Server on FlashArray.

Edition of SQL Server 

The edition of SQL Server (Standard or Enterprise) has a direct impact in the amount of system resources that can be allocated for use with the SQL Server instance. The below screenshot demonstrates the number of variants available for SQL Server 2019 images in Azure using Windows Server 2022 as of the writing of this document. To read more about the different editions of SQL Server, see the topic "Editions and supported features of SQL Server 2022" in Microsoft Learn. There will be a version for your target version of SQL Server as long as it is in current support.

SQL-versions.png

The below table outlines the maximum amount of VM CPUs and RAM that can be allocated to the SQL Server service based upon SQL Server edition.  This is an important consideration as it can be wasteful to oversize a VM type if only a Web or Standard license is available. To see updated specifications for SQL Server, see the topic "Compute capacity limits by edition of SQL Server" in Microsoft Learn.

SQL Server Edition Maximum Compute Capacity for a SQL Server Instance Maximum Memory Utilization for a SQL Server Instance
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 Server Enterprise Edition will be required in order to utilize enough VM resources to make performance acceptable. 

 
tempdb Best Practices in Azure

tempdb is an ephemeral SQL Server 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. For more information on tempdb, see the tempdb section of the best practices document linked earlier.

Whenever possible, our recommendation for tempdb is to store it on the attached SQL Server 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 Server instances may overrun the included SQL Server 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 Server VM to serve as the new tempdb location.

High Availability and Disaster Recovery

High Availability and Disaster Recovery (HADR) is concept that ensures a given SQL Server instance will be available when necessary and in the event a problem occurs, recover. Backing up and restoring databases is a fundamental cornerstone of any availability strategy. Follow Microsoft's best practices for highly available SQL Server deployments in the section "HADR configuration" in the Microsoft Learn topic "Checklist: Best practices for SQL Server on Azure VMs".

Useful SQL Server Troubleshooting Articles and Options

Customers experiencing performance, resiliency or other issues with SQL Server and CBS should consult the following articles for further steps to diagnose, troubleshoot and resolve such issues.