Bench marking your environment is an important step when introducing new hardware, which is accomplished by running a test workload against the hardware. There are multiple ways to accomplish this to get SQL Server performance data One of these methods is using HammerDB, which is a free tool that provides TPC standard bench marking metrics for multiple database systems, including Microsoft SQL Server. These metrics are an industry standard and are defined by the Transaction Processing Performance Council (TPC). The results from bench marking will help you to ensure that the new infrastructure will be able to support the expected workload.
Azure introduces ways to quickly implement new hardware. However, if the Azure environment isn’t setup correctly, you can introduce issues that could potentially degrade performance. Thankfully, HammerDB is Azure aware which allows you to easily benchmark your cloud environment.
Let’s see how to configure HammerDB for Azure.
HammerDB
HammerDB is open source so you can download it from here. It’s available with a Windows distribution or Linux. For our purposes, I will showcase the Windows version. Go ahead and install it with the default options. Running the application requires launching a batch file as it does not include an actual EXE file. If you accepted the defaults, youshould now have a C:Program FilesHammerDB-3.2 directory. Within the directory, there should be a HammerDB.bat file. This will launch the UI for the application. You can create a shortcut to this batch file on your desktop for ease of future use.
Go ahead and launch the hammerdb batch file. Once the UI has been initialized, on the left hand menu tree, double click on the SQL Server option. In the resulting Benchmark Options dialog window, select SQL Server and you can leave the default TPC-C option.
Click Ok. You’ll get a confirmation window, just click OK again. Now you’ll notice that under SQL Server there is a TPC-C tree that you can expand. Expand the tree.
For the purpose of this blog post I’m only going to focus on how to configure HammerDB to connect and utilize Azure SQL DB. I will let you play around with the other configuration settings or will blog about those at a later point in time. Expand the Schema Build branch. You will see an Option and Build selection. Double click on Options
In the resulting window, you can see that there are some parameters that need to be supplied.
First, enter the name of the SQL Server. Since we want to go to an Azure SQL DB, this is the name of the server that will host the TPCC database. In this case, I have a demo server, sqldbdemo-east.database.windows.net available, which resides in the East region.
Next, select the Azure check box.
Thirdly, you might have to change the version of ODBC driver. In my case, I’ve got the latest version of SSMS installed so ODBC Driver 17 for SQL Server is what I needed to use. You can find this by looking in the ODBC Data Source Administrator if you need to find it. If you need a different ODBC driver, you can find them here.
Finally, supply your credentials. In this case I am use SQL Server authentication, so I supplied the appropriate user name and password.
Click Ok. Now HammerDB is configured to work in conjunction with Microsoft Azure. We are just about ready to build the schema for the database that will be used to perform the benchmarking.
There’s one catch, however. The catch is that you have to create the shell of the database first. The schema build process will not create the database if it does not currently exist. You can easily go to the server and execute a create database statement.
CREATE DATABASE [tpcc]; GO
Now that the database shell is present, we can build the schema into the shell. You can accomplish this either by double clicking “Build” under Options or clicking on the Build button in the toolbar.
Start the schema build. It’ll prompt you for an OK
The build process will also load the data required for the actual benchmarking process. This process could take a few minutes depending on which service tier your Azure SQL DB is sitting on.
Once it has completed, we can confirm that the schema and data are present.
Now we can commence bench marking our Azure environment!
Summary
Bench marking is a process that doesn’t occur as frequently as it probably should. However, tools like HammerDB continue to evolve to match pace with cloud technology to help ensure we have means to do this. Even if you are moving to the cloud, make sure to do your due diligence and benchmark things. You might be surprised with the results..
© 2019, John Morehouse. All rights reserved.