This section provides best practices and recommendations for SQL MI and Hammer DB test harness.

Perform the following deployment steps for the multi-instance SQL MI test harness:

Note: All the tasks are authored and managed with VS Code.

                  1. Deploy a SQL MI with Azure Arc-enabled data services in indirect mode.

                  2. build the TPCC database on the first SQL MI and presize the SQL Server data and log files.

                  3. Retrieve the SQL MI endpoint IP for the instance that we were restoring the database into.

                  4. Restore the database backup is to the arc-sqlmi container in the appropriate SQL MI pod.

                  The T-SQL Stored Procedure test harness is deployed.

                  5. Create a custom T-SQL procedure and deploy to the Master database on the instance and insert the results into a reporting table:

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)

    ) ;

Perform the following deployment steps for the HammerDB test harness:

A timed HammerDB workload is run with an incrementing user count. The autorundrive.tcl file is used and updated with the IP address of the SQL MI endpoint. Then the batch file is run using the integrated terminal from the HammerDB root directory in C:\Program Files\HammerDB-4.2. 

The multiinstance test batch file included two execution statements per instance:

1. Run a small piece of dynamic SQL was in a .sql file that resides in the HammerDB root execution directory.
2. Start the SQL MI specific HammerDB drive CLI - each containing the specific IP for each instance endpoint.
3. Run the process for the duration that is configured in the HammerDB autorundrive.tcl file.
4. A master reporting SQL instance is deployed. A linked Server connection is used for each of the SQL MIs to retrieve the records by batch ID.
5. The TPM results must be grouped by batch ID on the reporting host.
6. Finally, pull the aggregated data to an Excel and use it as pivot tables to display the results in charts.