Deployment steps for the multi-instance 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 indirect mode.
- We pre-built the TPCC database on the first SQL MI. We pre-sized the SQL Server data and log files.
- We retrieved the 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 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)
,TPSvaluePerSecond AS (TPSvaluePerMinute / 60) PERSISTED
- 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 SQL MI endpoint. We ran the batch file using the integrated terminal from the HammerDB root directory in C:\Program Files\HammerDB-41.
- The multi-instance test batch file included two execution statements per instance:
- Ran a small piece of dynamic SQL in a .sql file that resided in the HammerDB root execution directory.
- Started the 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 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.