Search
StarWind is a hyperconverged (HCI) vendor with focus on Enterprise ROBO, SMB & Edge

Can SQL Server Failover Cluster Instance run twice as fast as SQL Server Basic Availability Groups in 2-node cluster? Part 2: Studying FCI performance

  • June 15, 2019
  • 13 min read
IT and Virtualization Consultant. Dmitriy is specializing in Microsoft technologies, with a focus on storage, networking, and IT infrastructure architecture.
IT and Virtualization Consultant. Dmitriy is specializing in Microsoft technologies, with a focus on storage, networking, and IT infrastructure architecture.

INTRODUCTION

In my previous article, I measured SQL Server Basic Availability Groups (BAG) performance. This, as it comes from the name, addresses SQL Cluster Failover Cluster Instance (FCI) performance. I expect SQL Server FCI to exhibit two times higher performance than BAG.

Before I start, I’d like to tell you one important thing about this measurement. SQL Server FCI database resides on a StarWind virtual device. Why did I choose StarWind? Because I got their NFR license some time ago and decided to give this software-defined storage solution a shot. Let’s just hope that it won’t limit SQL Server FCI performance.

THE TOOLKIT USED

Now, let’s take a closer look at the setup configuration that I used for measuring SQL Server FCI performance:

Node 3, Node 4: Both are identical from the hardware point of view

Dell R730, CPU 2x Intel Xeon E5-2697 v3 @ 2.60 GHz , RAM 128GB

Storage: 1x Intel SSD DC S3500 480GB

LAN: 1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s

OS: Windows Server 2016 Datacenter

Database Management System: Microsoft SQL Server 2016

VSAN: StarWind Virtual SAN for Hyper-V (Windows-based) StarWind_8.0-R6U3-release

TESTING SQL SERVER FCI PERFORMANCE

Before you create a database

First, you need to deploy Windows Server Failover cluster and install StarWind Virtual SAN for Hyper-V. Next, create StarWind Virtual Device 1GB intended for the Disk Witness (Cluster Disk1) that is connected to both Hyper-V nodes over iSCSI (1x local iSCSI session, 1x Network iSCSI session).

Create the second StarWind Virtual Device on top of Intel SSD DC S3500. This device is intended for SQL Server FCI database; so, obviously, it should have a larger volume (I set it to 445 GB here). Connect this Virtual Device over iSCSI to both nodes (3x local iSCSI session, 2x Network iSCSI session). Create the Cluster Disk2 on that disk.

wp-image-673

wp-image-674

Setting up SQL Server FCI

Now, let’s go through 2-node SQL Server FCI deployment.

Install SQL Server 2016 on Node 3 from the preinstalled image. On the whole, installation and setting up processes are similar to ones for BAG. Anyway, I will describe the whole process to make reproducing my study easier.

Select the New SQL Server failover cluster installation option.

Press Next after Failover Cluster Rules are successfully installed.

wp-image-677

At the next step, when SQL Server 2016 setup wizard asks you to specify the product key, select the Developer free edition from the dropdown list.

wp-image-678

Look through the license terms and tick the checkbox to accept them. Press Next afterward.

wp-image-679

At the Feature Selection step, specify the path for SQL Server installation and select all features you need. Press Next to proceed.

wp-image-680

Enter SQL Server Network Name, Name instance, and Instance ID in the self-titled fields.

At the next step, select the automatically created cluster resource group and click Next.

wp-image-682

Specify the shared disk that you want to include in the SQL Server resource cluster group for keeping databases.

wp-image-683

Specify the network settings for this failover cluster. You can use DHCP or just enter SQL Server failover cluster IP.

wp-image-684

Enter SQL Server Database Engine and SQL Server Agent account details. Here, I use the domain ones.

wp-image-685

Next, decide on the authentication security mode and specify the password for the SQL Server system administrator account.

wp-image-686

Specify the path for the database. Here I use cluster disk G.

wp-image-687

Afterward, go to the TempDB tab and specify TempDB data files and TempDB log files parameters.

wp-image-688

Check the settings and press Install.

wp-image-689

Once SQL Server FCI installation is complete, press Close.

wp-image-690

Now, let’s install SQL Server FCI on Node 4.

One more time, install the SQL Server preinstalled image as administrator. This time, you need to opt for Add node to a SQL Server failover cluster.

wp-image-691

On the whole, there’s nothing new about the installation procedure. Of course, there are several differences, and, to make the long story short, I’d like to discuss only them in the following steps.

At the Cluster Node Configuration step, select the SQL Server instance to which you are going to connect Node 4.

wp-image-692

During cluster network configuration you need to select the already existing configuration. Just tick the checkbox and press Next.

wp-image-693

Well, that’s it! The whole procedure, apart from those two steps that I’ve just mentioned above, is identical to what you did for Node 3. So, press Close once SQL Server is installed.

wp-image-694

Now, let’s run SQL server and check whether Failover Cluster role is there.

wp-image-695

wp-image-696

Everything is fine, let’s create a database now.

Creating a database

Install Microsoft SQL Server Management Studio on Node 3 and connect to SQL Server FCI.

wp-image-697

Create an empty database (TestBase) next.

wp-image-698

Flooding database

Fill the database with data using HammerDB. Once this process is over, you’ll get the output with database writing performance.

wp-image-699

wp-image-700

Obviously, I do not want the cache to alter SQL Server FCI performance test. That’s why I reduced the overall amount of RAM available for each SQL server to 512MB.

Challenging SQL Server FCI performance

Everything is installed and fine-tuned, so I finally can run some performance tests.

First, let’s see how fast reading can be done. In Microsoft SQL Server Management Studio request reading 1M rows from the dbo.customer table. It took SQL Server FCI 19 sec to cope with this task. Well, the same number as for SQL Server BAG!

wp-image-701

Let’s take a look at reading performance of the disk that keeps the database.

wp-image-702

Let’s do some more tests! Let’s see how database reading bandwidth and request execution time depend on the number of threads (Number of Threads = 1,2,4,8,10,12). Just like for SQL Server BAG, I used SQLQueryStress for these measurements.

SQL FCI
test run time, 

sec

HA IMG,

MB/s

SSD (Node 3),

MB/s

SSD (Node 4),

MB/s

threads=1 2,56 346 191 189
threads=2 2,55 376 193 192
threads=4 4,10 375 259 246
threads=8 9,42 348 324 323
threads=10 12,25 501 347 349
threads=12 17,69 499 397 398

Let’s do one more test using HammerDB (OLTP pattern). Now, let’s see how varying the number of threads impacts the reading speed.

SQL FCI test run time, min
Virtual User=1 9
Virtual User=2 8
Virtual User=4 9
Virtual User=8 8
Virtual User=10 10
Virtual User=12 12

Charts below highlight on how performance changes under a varying number of threads.

Зображення, що містить текст, знімок екрана, схема, карта Автоматично згенерований опис

 

Figure 1: Virtual Users = 1 and Virtual Users = 2

 

Зображення, що містить текст, знімок екрана, схема, ряд Автоматично згенерований опис

 

Figure 2: Virtual Users = 4 and Virtual Users = 8

 

Зображення, що містить текст, знімок екрана, Шрифт, ряд Автоматично згенерований опис

 

Figure 3: Virtual Users = 10 and Virtual Users = 12

 

CONCLUSION

In this article, I studied SQL Server Failover Cluster Instance performance. Time to read 1M rows is the same as for BAG. That’s weird. I will think through it, but it seems to me that there may be something wrong with StarWind virtual device performance. I am not 100% sure as I need to carry out extra measurements to find out what’s going on. In my  next article, https://www.starwindsoftware.com/blog/hyper-v/can-sql-server-failover-cluster-instance-run-twice-fast-sql-server-basic-availability-groups-2-node-cluster-part-3-comparison-time/, I compared FCI and BAG performance and did some tests to find out what could go wrong with StarWind HA device.

Found Dmitriy’s article helpful? Looking for a reliable, high-performance, and cost-effective shared storage solution for your production cluster?
Dmytro Malynka
Dmytro Malynka StarWind Virtual SAN Product Manager
We’ve got you covered! StarWind Virtual SAN (VSAN) is specifically designed to provide highly-available shared storage for Hyper-V, vSphere, and KVM clusters. With StarWind VSAN, simplicity is key: utilize the local disks of your hypervisor hosts and create shared HA storage for your VMs. Interested in learning more? Book a short StarWind VSAN demo now and see it in action!