Introduction

Some time ago, I published here comparison of SQL Server Failover Cluster Instances (FCI) and SQL Server Basic Availability (BAG) performance while having them run on top of StarWind Virtual SAN (https://www.hyper-v.io/can-sql-server-failover-cluster-instance-run-twice-fast-sql-server-basic-availability-groups-2-node-cluster-part-2-studying-fci-performance/). Today, I measure SQL Server BAG performance on Storage Spaces. The next part sheds light on SQL Server FCI performance on S2D. Can I squeeze two times more TPM out of SQL Server FCI on S2D than SQL Server BAG can provide on Storage Spaces?

The toolkit used

Let’s take a look at the setup used for this study.

scheme

Unlike the setup used in my previous article, this time, I have 4 drives in each host. The thing is, S2D needs at least 4 drives on each node to be deployed (https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/storage-spaces-direct-hardware-requirements#minimum-number-of-drives-excludes-boot-drive). To compare SQL BAG and FCI performance later, today, I also use 4 disks in underlying storage too.

Here are more details about the environment configuration:

  • SRV153, SRV154: Both hosts are identical from the hardware point of view.
  • Dell R730, CPU 2x Intel Xeon E5-2697 v3 @ 2.60 GHz, RAM 128GB
  • Storage: 4x 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

Deploying SQL Server BAG in a 2-node environment

Testing SQL Server Basic Availability Groups

Install Windows Failover Cluster first. Before I move further, I’d like to mention that I assigned a quorum vote to a separate Share Witness.

Next, start the Storage Spaces installation wizard on SRV153.

Storage Spaces installation wizard on SRV153

Select a node where you want to create the storage pool and enter the storage pool name.

Select a node where you want to create the storage pool and enter the storage pool name

Select the disks which you want to include into the storage pool and press Next.

Select the disks for the storage pool and press Next.

Verify the storage pool settings and click Create.

Verify the storage pool settings and click Create

Once you are done with the storage pool creation, tick the Create a virtual disk when this wizard closes checkbox and press Close.

Create a virtual disk when this wizard closes

Select the storage pool afterward.

Select the storage pool

Name the virtual disk.

Name the virtual disk

Select the Simple layout in the Select the storage layout tab.

storage layout

Specify the provisioning type afterward. Today, I used a Fixed disk.

provisioning

Then, specify the virtual disk size.

specify the virtual disk size

Confirm the virtual disk settings and click Create.

Confirm the virtual disk settings and click Create

After you are done with virtual disk creation, tick the Create a volume when this wizard closes checkbox and close the wizard.

 Create a volume when this wizard closes

Select the recently created virtual disk in the New Volume wizard.

New Volume wizard

Next, specify the volume size.

specify the volume size

Assign the volume to a drive letter and press Next.

Assign the volume to a drive letter and press Next

Choose the disk formatting and set the label.

disk formatting

Confirm the parameters of new volume creation by pressing the Create button.

Confirm the parameters

Once the wizard finishes volume creation, press Close. Go to Disk Management and check whether the volume has been added.

Once the wizard finishes volume creation, press Close

Create just the same disk on the SRV154 node.

Create just the same disk

Installing SQL Server BAG

Now, let’s create a 2-node SQL Server BAG with an empty database. First, install SQL Server 2016 on SRV153 from the image.

install SQL Server 2016

In SQL Server Installation Center, start SQL Server installation.

SQL Server installation

Enter the product key next. Today, I use the Developer free edition (it should be enough for study purposes).

 product key

Look through the license terms (or pretend that you have read it ) and tick the checkbox saying that you accept it.

 license terms

Select the necessary features and specify the path for SQL Server components installation.

Select the necessary features

specify the path for SQL Server components installation

Enter the instance name.

Enter the instance name

Enter the SQL Server credentials. Just use the domain credentials in order to avoid any possible issues with Basic Availability Groups creation in the future.

SQL Server credentials

Specify the authentication security mode and system administrator account.

the authentication security mode and system administrator account

Enter the database directory afterward. Today, it is kept on a virtual disk D over Storage Spaces.

 Storage Spaces

Now, specify TempDB settings in the self-titled tab.

specify TempDB settings in the self-titled tab

Make sure that everything is set right and press Install.

Make sure that everything is set right and press Install

Once the installation wizard finishes, click Close.

Once the installation wizard finishes, click Close

In order to start using Basic Availability Groups, open SQL Server Configuration Manager, go to SQL Server (SQLBAG) settings there, and enable AlwaysOn Availability Groups. Reboot the server afterward.

open SQL Server Configuration Manager

Well, that’s pretty much it for SQL Server BAG installation. Repeat the whole process for SRV154 now.

Once you are done with deploying SQL Server BAG on SRV154, install SQL Server Management Studio and connect all nodes to the SQL Server (SQLBAG).

connect all cluster nodes

Creating a test database

Create a new database.

Create a new database

Enter its name.

Enter its name

Back up that database afterward.

Back up that database

Back up

Create an availability group

After backing up, start Availability Group creation.

 Availability Group

In the New Availability Group wizard, specify Availability Group name and tick the Database Level Health Detection checkbox.

specify Availability Group name and tick the Database Level Health Detection checkbox

Now, select the database for Availability Group creation and press Next.

select the database for Availability Group creation

Add the database replica. Today I keep it on SRV154.

Add the database replica

Here’s how everything looks like at the end.

complete

Go to the Listener tab and enter the IP and availability group listener DNS name.

DNS name

Next, in the Select Data Synchronization menu, select Automatic seeding.

Automatic seeding

Wait until all availability group components are validated and press Next.

Wait until all availability group components are validated

At the Summary step, verify the choices made in the New Availability Group wizard.

New Availability Group wizard

Close the wizard once it completes successfully.

Close the wizard once it completes successfully

Once the Availability Group is created, go to Microsoft SQL Server Management Studio.

QL Server Management Studio

Test time!

Populating the database

Today, I use HammerDB (https://www.hammerdb.com/download.html) to fill in the database (I called it TestBase).

When you start HammerDB, select Benchmark in the Options menu. Find the Benchmark settings in the screenshot below.

Options menu

Go to the Schema build menu next and configure HammerDB connections. You also need to set the number of virtual users there.

build menu

Double-click the Build menu and fill in the database with data.

Double-click the Build menu

Once the database is populated with data, you can find all details about writing performance in the separate file.

transaction counter

Physical disk

Testing performance

Once the database is filled in with data, reduce the amount of RAM available for SQL Server in order to prevent caching from altering the database performance.

server options

Now, it’s time to benchmark reading performance. I judged on reading performance based on how long it takes to read 1 000 000 lines from the dbo.customer database. In my case, it took 19 seconds to finish this request.

reading performance

Here are some details about SQL Server BAG reading performance.

BAG reading

Testing with SQLQueryStress

I also tested reading performance with SQLQueryStress, the utility allowing to estimate reading performance under the varying number of threads (number of virtual users = 1, 2, 4, 8, 10, 12).

Start SQLQueryStress and press Database. Set up database connection and click Test Connection.

Test Connection

Enter the SQL query next. Specify the Number of threads and Number of Iterations parameters and press GO to start the test.

Specify the Number of threads and Number of Iterations

Take a look at database performance after testing it with SQLQueryStress under the varying number of threads.

SQL Server BAG
test run time. sec SSD. MB/s
threads=1 3.09 283
threads=2 2.96 292
threads=4 2.97 282
threads=8 3.13 272
threads=10 3.51 441
threads=12 3.98 517

Run HammerDB again. Go to the Options menu. There, enter HammerDB connection parameters and the number of transactions per virtual user. Press OK. To run the recently prepared script, double-click Load.

Go to the Options menu

Now, go to the Virtual User menu and specify the number of users and iterations. Click OK and double-click Create afterward. Finally, click Run twice to start the test.

 Virtual User menu

Find details about database performance under the varying number of virtual users in the table below.

SQL BAG
test run time, min
Virtual User=1 3
Virtual User=2 3
Virtual User=4 5
Virtual User=8 7
Virtual User =10 9
Virtual User=12 10

HammerDB, Virtual User = 1 and Virtual User = 2

HammerDB, Virtual User = 1 and Virtual User = 2

HammerDB, Virtual User = 4 and Virtual User = 8

HammerDB, Virtual User = 4 and Virtual User = 8

HammerDB, Virtual User = 10 and Virtual User = 12

HammerDB, Virtual User = 10 and Virtual User = 12

Conclusion

Today, I got some numbers for performance of SQL Server Basic Availability Groups run on Storage Spaces. My next article will shed light on SQL FCI performance in a 2-node S2D cluster. Stay tuned!