Deployment steps for the multi-instance SQL MI test harness:

Note: We authored and managed all work with VS Code.

To improve both consistency and efficiency of our multiple SQL MI tests, many running at the exact same time, we added new PowerShell automation to our existing T-SQL test harness. We now can start with a single command using the following steps:

1. Restore the TPCC database back to the baseline build state.
2. Dynamically generate individual - .TCL and .YAML files for each of the HammerDB containerized deployments and the associated workload automation.
3. Clean up any previous HammerDB pods that may exist and deploy a separate HammerDB pod for each SQL MI that has been referenced in the configuration file parameter.
4. Deploy a T-SQL procedure to the master database of each SQL MI to monitor TPM. Writing a single row per minute.
5. A T-SQL History table is also captured to store multiple tests by batchID GUID.
6. Run the workload for the duration and user load defined in the configuration file parameters.
7. Pull all T-SQL TPM records, from the history table, by associated batch, once the test time duration has expired.
8. T-SQL used for final TPM aggregated reporting showcased within this document.
9. Finally, we pulled the aggregated data to Excel and used pivot tables to display our results in charts.

Figure 39.    HammerDB execution – 12 wide

Sample configuration file that is the driver for test harness automation.

## --------------------------------------------------

# Kubernetes related configs

## --------------------------------------------------

$hammerdbNamespace = 'hammer-db-ns'

## --------------------------------------------------

# SQL Server related configs

## --------------------------------------------------

# Can be set to either of these two values (linux or windows)

$sqlEnv="linux"  

# IP addresses of each SQL MI. Add prts if NodePort K8s configuratin

$mssqlips="10.129.80.55", "10.129.80.56", "10.129.80.57", "10.129.80.58", "10.129.80.59"

# MI username

$mssqlUser='miadmin'

# MI Password

$mssqlPass='!!123abc'

# TPC database

$mssqlDatabase='tpcc'

# SQL backup file location

# (do not provide back slash (/) at the end of the path)

$backupLocation="/var/opt/mssql/backups"  

## --------------------------------------------------

# Load run config

## --------------------------------------------------

# RampupTime and execTime in minutes per user load

$userLoadSet=5

# User load test groups

$loadRunUser='5 10 15 20 25'

# HammerDb Ramp up time

$rampupTime=1

# Execute time per user group

$execTime=5

Core T-SQL pulled from the HammerDB source code.

    SELECT  

@cntr_value = cntr_value,

    @datetime = getdate() at time zone 'Central Standard Time'

    FROM sys.dm_os_performance_counters WITH (NOLOCK)

    WHERE counter_name = 'Batch Requests/sec'

    SELECT

@UserCount = COUNT(es.session_id)-1

    FROM sys.dm_exec_sessions AS es WITH (NOLOCK)

    CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib

    WHERE es.database_id = DB_ID(@DatabaseName)

    AND  es.session_id > 50

    AND  es.session_id <> @@SPID

    AND  program_name = 'tclsh8.6'

    OPTION (RECOMPILE);

Sample logging table that resides in the master database for each SQL MI

CREATE TABLE dbo.TPSvalues 

    (

    RowID_TPSvalues INT IDENTITY (1,1)

    ,ServerName VARCHAR(20)

    ,ValueReportTime smalldatetime

    ,TPSvalue BIGINT

    ,TPSvaluePerMinute BIGINT

    ,TPSvaluePerSecond AS (TPSvaluePerMinute / 60) PERSISTED

    ,UserCount INT

    ,CPUCount INT

    ,[CommittedMemory(MB)] BIGINT

    ,[TargetMemoryGoal(MB)] BIGINT

    ,BatchID UNIQUEIDENTIFIER

    ,CpuRank NVARCHAR(MAX)

    ) ;