n0derunner

    Generate load on Microsoft SQLserver Windows from HammerDB on Linux

    Published: (Updated: ) in Database Performance, , , , , by gary.

    HammerDB on Linux driving load to Windows SQL Server

    Often it’s nice to be able to drive Windows applications and databases from Linux, especially if you are more comfortable in a Unix environment. This post will show you how to drive a Microsoft SQL Server database running on a Windows server from a remote Linux machine. In this example I am using Ubuntu 22.04, SQLserver 2019, Windows 11 and HammerDB 4.4

    Important Note.

    To connect to SQLserver, you will need an ODBC driver on Linux. At time of writing (October 2022) – It is best to use ODBC Version 17. This is because Version 18 will fail to connect due to certificate issues. It is possible to use odbc.ini to overrride the certificate settings (specifically forcing ODBC to trust the server certificate) however HammerDB calls ODBC drivers directly and as such does not use odbc.ini.

    For versions of HammerDB beyond 4.5 – you will be able to make the same overrides (force certificate trust) directly in HammerDB – but HammerDB 4.5 does not support that.

    Installing ODBC Drivers

    To connect HammerDB to Microsoft SQLserver you will need to install the base unixodbc driver – and the specific Windows ODBC driver. These steps work on Ubuntu 22.04

    Step 1 Bring ubuntu up to date

    Update and upgrade Ubuntu. Reboot if the kernel is upgraded during this step
    sudo apt update; sudo apt upgrade; sudo reboot

    Step 2 Install unixodbc

    As long as apt is up-to-date, installing unixodbc will install the dependencies correctly
    sudo apt install unixodbc

    Step 3 Install SQLServer Version 17 ODBC and tools

    Copy/paste these instructions into a file named install-ms-odbc.sh and run as root e.g. # bash ./install-ms-odbc.sh

    if ! [[ "16.04 18.04 20.04 22.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev
    Step 4 Prepare SQL server for remote connections

    There are few things to do on the MS SQLserver side to allow remote logins. Google these tasks if you are unfamiliar with SQLserver.

    Step 5 – Check ODBC can connect.

    Before proceding to install HammerDB, first check to see that you can connect to the DB with vanilla ODBC tools. Assume for now that the SQLserver is at IP address 192.168.1.69 and we created a user called tpcc with the password tpcc

    $ /opt/mssql-tools/bin/sqlcmd -S 192.168.1.69 -U tpcc -P tpcc
1> select @@version
2> go
...
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
...
    Step 6 – Download and install HammerDB

    In either case the script is a bash file that needs to be made executable. Note HammerDB 4.5 opened a TCL window during install. If running via ssh ensure that your connection allows remote X11 sessions. (xeyes is your friend as always). e.g.

    chmod +x HammerDB-4.4-Linux-x64-installer.run
./HammerDB-4.4-Linux-x64-installer.run
    Step 7 Create connection/workload script

    Create a script like this which describes the database you want to connect to and the workload to run. Call it something like mssqls.tcl

    dbset db mssqls
dbset bm TPC-C
diset connection mssqls_linux_server sqlserver
diset connection mssqls_uid nutanix
diset connection mssqls_pass nutanix
diset connection mssqls_linux_odbc "ODBC Driver 17 for SQL Server"

diset tpcc mssqls_dbase tpcc
diset tpcc mssqls_allwarehouse true
diset tpcc mssqls_driver timed

loadscript
vuset vu 5
vuset delay 0
vuset repeat 0
vuset showoutput 0
vuset logtotemp 1
vucreate
    Step 8 Start HammerDB and load the script
    :~/HammerDB-4.4$ ./hammerdbcli
HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration

    Load the configuration scrtipt

    hammerdb>source mssql.tcl

    You should get an output like below

    Database set to MSSQLServer
Benchmark set to TPC-C for MSSQLServer
Changed connection:mssqls_linux_server from localhost to sqlserver for MSSQLServer
Changed connection:mssqls_uid from sa to nutanix for MSSQLServer
Changed connection:mssqls_pass from admin to nutanix for MSSQLServer
Value ODBC Driver 17 for SQL Server for connection:mssqls_linux_odbc is the same as existing value ODBC Driver 17 for SQL Server, no change made
Value tpcc for tpcc:mssqls_dbase is the same as existing value tpcc, no change made
Changed tpcc:mssqls_allwarehouse from false to true for MSSQLServer
Clearing Script, reload script to activate new setting
Script cleared
Changed tpcc:mssqls_driver from test to timed for MSSQLServer
Script loaded, Type "print script" to view
User Delay(ms) must be 1 or greater
setting to value: Repeat Delay(ms) must be 1 or greater
setting to value: Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Vuser 6 created - WAIT IDLE
Logging activated
to /tmp/hammerdb.log
6 Virtual Users Created with Monitor VU
    Step 9 Run HammerDB

    Now you can run some workload by issuing vurun

    hammerdb>vurun

    Output should look something like this

    Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:RUNNING
Vuser 2:VU 2 : Assigning WID=1 based on VU count 5, Warehouses = 10 (1 out of 2)
Vuser 2:VU 2 : Assigning WID=6 based on VU count 5, Warehouses = 10 (2 out of 2)
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:VU 3 : Assigning WID=2 based on VU count 5, Warehouses = 10 (1 out of 2)
Vuser 3:VU 3 : Assigning WID=7 based on VU count 5, Warehouses = 10 (2 out of 2)
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 4:RUNNING
Vuser 4:VU 4 : Assigning WID=3 based on VU count 5, Warehouses = 10 (1 out of 2)
Vuser 4:VU 4 : Assigning WID=8 based on VU count 5, Warehouses = 10 (2 out of 2)
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 5:RUNNING
Vuser 5:VU 5 : Assigning WID=4 based on VU count 5, Warehouses = 10 (1 out of 2)
Vuser 5:VU 5 : Assigning WID=9 based on VU count 5, Warehouses = 10 (2 out of 2)
Vuser 5:Processing 10000000 transactions with output suppressed...
Vuser 6:RUNNING
Vuser 6:VU 6 : Assigning WID=5 based on VU count 5, Warehouses = 10 (1 out of 2)
Vuser 6:VU 6 : Assigning WID=10 based on VU count 5, Warehouses = 10 (2 out of 2)
Vuser 6:Processing 10000000 transactions with output suppressed...

    At this point you should see SQLServer under load and running transactions. On the terminal you will eventually see this output

    Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 5 in minutes
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:5 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 53077 NOPM from 121712 SQL Server TPM
Vuser 1:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 6:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
    Step 10. Enjoy

    Now you have HammerDB running on Linux driving load to MS SQLserver.

    Comments

    Leave a Comment