Optimizing protocol selection when using Amazon FSx for NetApp ONTAP for Microsoft SQL Server

In this blog post, we will review advantages and disadvantages of the two storage access protocols, iSCSI and SMB, offered by Amazon FSx for NetApp ONTAP (FSx for ONTAP). For comparison purposes, we will use Microsoft SQL Server on Windows. We will provide recommendations for selecting the optimal storage access protocol for your specific scenario.

1.   Introduction

FSx for ONTAP is a storage service that allows you to launch and run fully managed NetApp ONTAP file systems in the AWS Cloud. It offers high-performance file storage that’s broadly accessible from Linux, Windows, and macOS compute instances via the industry-standard NFS, SMB, and iSCSI protocols.

Using FSx for ONTAP as a storage layer for SQL Server deployments offers consistent high performance and throughput with low latency, as well as the additional benefits outlined in the article: Benefits of using Amazon FSx for NetApp ONTAP with SQL Server. FSx for ONTAP supports database instant file initialization – an important feature that distinguishes it from other managed file sharing options.

Another very useful feature of FSx for ONTAP is the ability to create read-only snapshots or writable clones of your database storage. Snapshots offer protection against accidental deletion or modification of your databases, while clones offer fast refreshes of the lower environments so that testing and QA can be performed on up-to-date, production copies of databases.

FSx for NetApp ONTAP uses two deployment types, Single-AZ and Multi-AZ, that offer different levels of availability and durability. For both deployment types, FSx for ONTAP provisions a pair of servers either across two Availability Zones for a Multi-AZ deployment or across two separate fault domains within a single Availability Zone for a Single-AZ deployment. This offers high availability and durability for storage by providing seamless failover in case of a server failure or a routine maintenance.

All these features position FSx for ONTAP as a premier platform for SQL Server deployment on AWS either using Always On FCI, Always-On Availability Group, or as a stand-alone node. The latter allows for optimizing deployment cost by separating compute and storage facilities as discussed in our earlier blog post.

For setting up SQL Server FCI on the Linux platform, you can use all NFS, SMB, and iSCSI protocols offered by FSx for ONTAP. However, on the Windows Server, only the SMB and iSCSI protocols are supported for SQL Server FCI. When planning for SQL Server deployments using FSx for ONTAP as a storage layer on Windows, customers must decide which of the two storage access protocols to use – SMB or iSCSI. Reviewing benefits and drawbacks of each of these protocols and providing recommendations for selecting the protocol for your SQL Server deployment is the focus of this blog post.

2.   iSCSI and SMB Overview

iSCSI is a transport layer protocol that describes how Small Computer System Interface (SCSI) transports packets over a TCP/IP network to provide block-level access to storage devices.

Authentication of the iSCSI connection takes place when establishing a connection between the iSCSI initiator on the client machine — SQL Server host in our case — and  the storage target on the FSx for ONTAP file server. iSCSI enables data storage to be presented to client machines as raw data volumes that need to be initialized and formatted on the client before use.

Unlike iSCSI block-level access, SMB is a network file sharing protocol. It relies on user-based authentication to access files on the fileserver. SMB clients access the file system hosted on the fileserver.

There are several versions of the SMB protocol. The latest version of the protocol, SMB 3,  achieves high network performance through the use of multichannel and multiple parallel paths, which is based on received side scaling (RSS).

3.   Configuration and Management

Ease of configuration and management is one of the criteria for selecting the connection protocol between SQL Server and FSx for ONTAP storage.

3.1.  iSCSI Configuration and Management

Mounting iSCSI LUNs to a Windows client is a multi-step process, which requires coordinated changes to be implemented both on the file server client and on the storage server.

On the client platform, which is typically Windows with SQL Server,  configuring the iSCSI Initiator and enabling and configuring MPIO are required. To increase data throughput between the storage client and server, it is recommended to configure multiple iSCSI sessions on the Windows iSCSI initiator of the storage client using MPIO to accommodate the configured throughput on FSx for ONTAP.

After configuring iSCSI initiator on Windows Server, the initiator identification must be transferred to the FSx for ONTAP iSCSI target to establish connection authentication. This requires knowledge of NetApp commands and some knowledge of Linux, as well as a basic understanding of storage protocols and networking.

After establishing a connection with the server, iSCSI LUNs will appear on Windows Server as raw volumes that need to be initialized and formatted prior to their use. While this is extra configuration overhead, it allows selection of file system attributes like block size, to optimize the volume for the intended use. iSCSI volumes can be combined into a RAID group, which opens novel opportunities that we discussed in our other blog post.

The established connection needs to be monitored and managed as iSCSI implementation takes the LUN offline if respective volume runs out of space, which brings down the SQL Server database allocated to this LUN.

3.2.  SMB Configuration and Management

Establishing a connection to FSx for ONTAP using the SMB protocol requires minimal storage administration experience. After configuring the SMB share on FSx for ONTAP, establishing a connection only requires steps on the client Windows machine. No configuration needs to be transferred to FSx for ONTAP. Connecting to an existing file share is an operation that typically a DBA can implement without the involvement of a storage administrator. Another benefit of an SMB connection is that it configures multichannel with 4 channels by default without any other configuration steps.

To support authentication with SMB, customers need Microsoft Active Directory (AD). The FSx for ONTAP SVM and SQL Server host machine need to join this AD, and a domain account needs to be created for the SQL Server service. Finally, this account needs to be authorized to access the FSx for ONTAP SMB share.

4.   SQL Server Performance when using SMB and iSCSI with FSx for ONTAP

Storage performance directly affects the performance of SQL Server as database systems rely heavily on storage to persist and protect data. Given the two protocol options, iSCSI and SMB, for connecting SQL Server to FSx for ONTAP storage, it is important to provide recommendations on protocol selection from the performance viewpoint.

To compare SQL Server performance over iSCSI and SMB protocols, we used the leading benchmarking and load testing software, HammerDB, to run a series of tests. Using HammerDB, we generated an OLTP-style 3.5 TB test database. To host SQL Server Enterprise Edition and run performance tests, we used a memory optimized r5dn.24xlarge Amazon Elastic Compute Cloud (Amazon EC2) instance with 96 vCPUs, 768 GB RAM, 100 Gbps enhanced networking, and locally attached NVMe SSD storage, which holds the SQL Server temporary database (TempDB).

We placed our 3.5 TB database on file systems with 7 TB, 14 TB, 21 TB, and 28 TB of total space allocated. For each selected file system size, we created two FSx for ONTAP file systems, one configured for iSCSI and one for SMB data access. We configured MPIO with 4 sessions on Windows Server iSCSI initiator connected to FSx for ONTAP iSCSI target to utilize the available bandwidth.

For each series of tests, we created a new FSx for ONTAP file system onto which we copied the complete database files. All FSx for ONTAP instances used for our testing were created in a multi-AZ configuration. We configured each file system with 80,000 IOPS and 2 GB/s throughput.

We ran all tests with the “All Warehouses” option set to maximize the load on the storage subsystem. Using the same version 4.6 of HammerDB software and the same database on SQL Server for all tests, we chose to use the  Transactions per Minute (TPM) metric for comparing SQL Server performance in various scenarios. Figure 1 captures the results of our series of tests for iSCSI protocol with Figure 2 providing the same results for configurations with the SMB protocol.

Figure 1. Performance results over the 30-hour series of tests (iSCSI protocol)

Figure 2. Performance results over the 30-hour series of tests (SMB protocol)

As can be seen from Figures 1 and 2, SQL Server performance starts at the same level for a given file system configuration. This is the expected result as all file systems are configured identically for IOPS and throughput, with the only difference being the amount of allocated storage. As the tests progress, SQL Server performance drops to a steady-state level, which is slightly lower than performance achieved in the first test. These drops in performance happening faster and deeper for the file systems of the smaller size. After 30-35 tests, performance results stabilized with statistically insignificant deviations between individual tests.

If we compare initial performance results, we notice that SQL Server running on FSx for ONTAP configured with iSCSI protocol provides over a 20% increase in performance than the performance metrics from FSx for ONTAP configured with SMB. These results are practically uniform for the file systems of different sizes, as shown in Table 1.

Table 1. SQL Server performance iSCSI versus SMB – Initial run

If we compare results for the steady state condition, iSCSI performance improvement dropped to approximately 10%, as shown in Table 2.

Table 2. SQL Server performance iSCSI versus SMB – Steady state

5.   Conclusion

Both iSCSI and SMB protocol options offered by FSx for ONTAP can be used for SQL Server deployments on AWS. When deciding between these two options, consider the following factors:

1.      iSCSI implementations rely on host-based authentication, while SMB implementations rely on user-based authentication to access file servers. Establishing host-based authentication requires a storage engineer to establish a connection between client and server. User-based authentication requires an additional component, Microsoft AD, but allows for stricter separation of duties between storage, AD, and database administrators.

2.      iSCSI configuration is more complex as it requires installation and configuration of additional components including the iSCSI initiator and establishing multiple iSCSI paths using MPIO. The number of required MPIO paths doubles when FSx for ONTAP is configured for high-availability Multi-AZ. SMB does not require any special configuration beyond connecting to a file share. SMB comes pre-installed on every Windows server and establishes multichannel connections by default.

3.      FSx for ONTAP configured with the iSCSI protocol provides approximately 10% increase in performance for SQL Server with OLTP-type workloads than the same system configured with SMB.

Whichever protocol option you select for your SQL Server with FSx for ONTAP deployment on AWS, with proper configuration and capacity planning, you will get both high performance and high availability for your SQL Server deployment.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.