Introduction

Some time ago, while looking through what SQL server Basic Availability Groups (BAG) can do, I found an awesome article by Pieter Vanhove: https://blogs.technet.microsoft.com/msftpietervanhove/2017/03/14/top-5-questions-about-basic-availability-groups/. There was nothing about performance, though, so I thought: “Hey, why not write an article about BAG performance?” Later, I realized that you need to compare this performance to something else, right? So, I decided to add SQL Server Failover Cluster Instance (FCI) performance measurements. Maybe, I’ll add some SQL Server Availability Groups (AG) measurements at the end; but, let’s see first whether SQL Server FCI can run twice as fast as SQL Server BAG.

In this study, I measured BAG performance alone. Now, as we know the scope of the article, let’s move on!

The toolkit used

First, let’s talk about the setups used for this study.

Here are more details on the setup configuration:

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 (You need fast underlying storage for your database, right? )

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

OS: Windows Server 2016 Datacenter

Database Management System: Microsoft SQL Server 2016

How I test SQL Server Basic Availability Groups (BAG)

Deploy 2-node SQL Server BAG with an empty database

Install Windows Failover Cluster. I assigned quorum vote to the separate Share Witness.

Afterward, install SQL Server 2016 on Node 3 from the preinstalled image. Just run it as administrator to start the installation process.

wp-image-603

During installation, select the New SQL Server stand-alone installation or add features to an existing installation option.

wp-image-604

Next, once prompted to specify the edition, select Developer from the Specify a free edition dropdown list. It is free, and I think that it is enough for today’s study.

wp-image-605

Skim the license agreement and press Next.

wp-image-606

Afterward, select all the necessary components for installing the SQL Server and press Next again.

wp-image-607

wp-image-608

Enter the instance name and ID and click Next.

wp-image-609

At the next step, specify the credentials for SQL Server Database Engine and SQL Server Agent. Just a friendly advice: use accounts that are on the same domain to avoid any possible troubles with BAG creation.

wp-image-610

Specify the authentication mode and credentials for the SQL Server system administrator account.

wp-image-611

Afterward, specify the path to the database. In my case, D disk stands for the Intel SSD DC S3500 drive.

wp-image-612

Next, go to the TempDB tab and set the TempDB data files and TempDB log files parameters.

wp-image-613

Eventually, verify all settings and press Install.

wp-image-614

Wait until SQL Server installs and press Close.

wp-image-615

Now, start SQL Server (SQLBAG) Configuration Manager to enable AlwaysOn High Availability Groups. Find the detailed guide on how to do this in the screenshot below.

wp-image-616

This is it for Node 3. Install SQL server on Node 4 now, and the cluster is good to go.

Once you are done, install Microsoft SQL Server Management Studio and connect both cluster nodes to SQL Server (SQLBAG).

wp-image-617

Creating a database

Let’s create the database now.

wp-image-618

Name the database and back up it afterward.

wp-image-619

wp-image-620

wp-image-621

Create a new availability group

Once you are done with the backup process, create a new availability group.

wp-image-622

First, specify the availability group name and check the Database Level Health Detection box.

wp-image-623

Select the database for VM creation and press Next.

wp-image-624

At the Specify Replicas stage, add the database replica. I decided to keep it on Node 4.

wp-image-625

Here’s what I got at the end of the day.

wp-image-626

Next, go to the Listener tab and specify the name, port (1433), and IP address of the availability group listener.

wp-image-627

At the next step, select Automatic seeding as preferable synchronization preference, and click Next.

wp-image-628

Press Next after successful validation.

wp-image-629

Look through the new availability group settings and press Finish to create a new availability group.

wp-image-630

Once the wizard completes successfully, click Close.

wp-image-631

You can find a new availability group in Microsoft SQL Server Management Studio.

wp-image-632

Filling in the database

Now, let’s flood TestBase with data using HammerDB.

First, start HammerDB and select Benchmark from the Options menu. Set up the benchmarking options just like in the screenshot below.

wp-image-633

In the Schema Build tab, set up the parameters of HammerDB connection to the database and specify the number of Virtual Users. This parameter addresses the number of threads used for filling in the database with data; so, throughout this article, I refer the Virtual Users number as the number of threads.

wp-image-634

Once you have specified all settings, double-click Build to start filling in the database with data.

wp-image-635

Once the process finishes, you’ll get a nice plot and the peak database writing performance.

wp-image-636

wp-image-637

Reducing the amount of RAM on each node

It should be noted that caching may alter the results. Regarding this fact, I decided to reduce the amount of available RAM for each node to 512 MB.

wp-image-638

Testing SQL Server BAG performance

Finally, I got over with the installation process. Let’s now jump to BAG performance measurements!

As we know writing performance, let’s find out SQL Server BAG reading performance. Go to Microsoft SQL Server Management Studio and execute the reading request of 1M rows from the dbo.customer table. In my case, it took around 19 secs to cope with this task.

wp-image-639

Here’s what maximum reading performance was like.

wp-image-640

I also carried out the measurement with SQLQueryStress. This utility provides more room for experiments. For instance, you can change the number of threads in a pretty wide range (Number of Threads = 1,2,4,8,10,12). Actually, that’s what I want to do right now.

After launching SQLQueryStress, press Database. Enter the main load settings and run the connection test.

wp-image-641

Next, at the homepage, form the request, specifying the number of threads, number of iterations, and delay between queries. Once you are done with setting up the utility, you are good to go.

wp-image-642

Here are the results I got.

SQL BAG
test run time, sec SSD, MB/s
threads=1 2,18 346
threads=2 2,09 376
threads=4 2,05 375
threads=8 2,24 348
threads=10 3,07 501
threads=12 4,48 499

Now, let’s do just the same measurement with HammerDB (OLTP pattern). Let’s see how performance changes under varying number of threads (Virtual User = 1,2,4,8,10,12).

Launch HammerDB one more time. In the Driver Script menu, double-click Options and set up the testing parameters in the emerged window. Afterward, double-click Load to prepare the script for connecting the database.

wp-image-643

Now, go to the Virtual User menu and double-click Options to specify the number of virtual users and iterations in the self-titled fields. Press OK. Next, double-click Create to create the necessary amount of Virtual Users. Once you are done with setting up the utility, run the test.

wp-image-644

The table below presents the results that I got with HammerDB under a varying number of threads value.

SQL BAG test run time, sec
Virtual User=1 9
Virtual User=2 8
Virtual User=4 9
Virtual User=8 8
Virtual User =10 10
Virtual User=12 12

The thing which I noticed during the experiment is that the number of transactions per-minute increases when you shift to a higher Virtual Users number. Let me show what I am talking about.

Figures below address performance changes measured with HummerDB while increasing the number of virtual users.

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

I got some numbers for the performance of a 2-node SQL Server Basic Availability Groups cluster. In the next article, I’ll investigate SQL Server Failover Cluster instances performance on the same setup. Stay tuned!