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

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

We deployed a SQL MI with Azure Arc-enabled data services in direct mode.

We pre-built the TPROC-C database on the first Arc-enabled SQL MI. We pre-sized the SQL Server data and log files.

We retrieved the Arc-enabled SQL MI endpoint IP for the instance we were restoring the database into.

We restored the database backup to the arc-sqlmi container in the appropriate Arc-enabled SQL MI pod.

We deployed the T-SQL Stored Procedure test harness. A custom T-SQL procedure was created and deployed to the Master database on the instance. Results were inserted 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)

    ) ;

We ran a timed HammerDB workload with an incrementing user count. We used the autorundrive.tcl file and updated it with the IP address of the Arc-enabled SQL MI endpoint. We ran the batch file using the integrated terminal from the HammerDB root directory in C:\Program Files\HammerDB-4.4. 

The multi-instance test batch file included two execution statements per instance:

1. Ran a small piece of dynamic SQL in a .sql file that resided in the HammerDB root execution directory.

2. Started the Arc-enabled SQL MI specific HammerDB drive CLI - each containing the specific IP for each instance endpoint.

The process ran for the duration configured in the HammerDB autorundrive.tcl file.

We deployed a master reporting SQL instance. We used a Linked Server connection to each of the Arc-enabled SQL MIs to retrieve the records by batch ID.

We grouped the TPM results by batch ID on the reporting host.

Finally, we pulled the aggregated data to Excel and used pivot tables to display our results in charts.