We defined six multi-instance test groups that we ran independently, and then documented the results. The test groups are listed in the following table.
Table 10. Testing matrices
SQL MI instance name | Resources requested | Test group |
SQLMI-01 | 4 vCPU, 8 Gi | Test-1, Test-2, Test-4, Test-6 |
SQLMI-02 | 4 vCPU, 8 Gi | Test-2, Test-4, Test-6 |
SQLMI-03 | 4 vCPU, 8 Gi | Test-2, Test-4, Test-6 |
SQLMI-04 | 4 vCPU, 8 Gi | Test-2, Test-4, Test-6 |
SQLMI-05 | 6 vCPU, 6 Gi | Test-3, Test-4, Test-6 |
SQLMI-06 | 6 vCPU, 6 Gi | Test-3, Test-4, Test-6 |
SQLMI-07 | 6 vCPU, 6 Gi | Test-3, Test-4, Test-6 |
SQLMI-08 | 6 vCPU, 6 Gi | Test-3, Test-4, Test-6 |
SQLMI-09 | 8 vCPU, 16 Gi | Test-5, Test-6 |
SQLMI-10 | 8 vCPU, 16 Gi | Test-5, Test-6 |
56 CPU requested | 88 Gi memory requested |
We chose these resources to represent a nice mix of general-purpose SQL Server workloads. As detailed in this testing section, we discovered that there are substantial opportunities to gain even greater SQL MI density efficiencies in the future.
We deployed all SQL MIs in a single batch before running any tests. In each test case, all SQL MIs were running, but we did not apply a load from HammerDB unless that instance was in the test group.
After we created the instances, we observed that the IP addresses shown below were reused in a consistent pattern from the data controller. This allowed us to manage multiple deployment scripts and tools in an efficient way. Only a single replica was created for each SQL MI, as we deployed general-purpose tier instances:
azdata arc sql mi list
Name PrimaryEndpoint Replicas State
-------- -------------------- ---------- --------
sqlmi-01 192.168.101.138,1433 1/1 Ready
sqlmi-02 192.168.101.142,1433 1/1 Ready
sqlmi-03 192.168.101.143,1433 1/1 Ready
sqlmi-04 192.168.101.144,1433 1/1 Ready
sqlmi-05 192.168.101.145,1433 1/1 Ready
sqlmi-06 192.168.101.146,1433 1/1 Ready
sqlmi-07 192.168.101.147,1433 1/1 Ready
sqlmi-08 192.168.101.148,1433 1/1 Ready
sqlmi-09 192.168.101.149,1433 1/1 Ready
sqlmi-10 192.168.101.150,1433 1/1 Ready
After we followed the test harness deployment steps, we ran a HammerDB workload against each SQL MI test group that is listed in Table 10.