All HammerDB CLI execution was orchestrated from a single jump host running outside of the DBaaS environment.

Our test plan consisted of six discrete groups of tests. We began with a single SQL MI and added more instances with each successive test. We also varied the sizing configurations of the SQL MIs. Each CPU and memory configuration were selected to create a representative general-purpose SQL workload that exists in production and dev/test environments.

SQL MI configuration profile

This SQL Server MIs had the following configuration profile and specifications:

• TPC-C database pre-sized data and log files to remove SQL autogrowth contention
• SIMPLE Recovery Model
• TempDB datafiles = SQL MI requested core count. Up to 8. This is managed by default.
• SQL MAXDOP = 0 (Kept default value)
• SQL memory configuration left alone – This is managed by the Linux node (currently) as default memorylimitmb for Linux to avoid OOM scenarios.

Note: Browsing the SQL Server logs – as an example – 8 Gi allocated container memory SQL MI detects 6,554 MB of memory for SQL

• Version = Microsoft Azure SQL Managed Instance - Azure Arc - 15.0.2148.140 (X64) July 21, 2021 23:25:56 General Purpose (64-bit) on Linux (Ubuntu 20.04.2 LTS) <X64>

Note: We chose Simple Recovery Model to remove the automated backup functionality provided with a SQL MI. Find out more in the Microsoft release notes under the July 2021 release.

SQL MI creation

When creating a SQL MI, we had to understand the difference between vCPU and memory limit and request differences. We also researched Microsoft sizing guidance. We paid special attention to the section about Kubernetes cluster sizing. Then, we researched the configuration options for new SQL MIs. We found the listing of arguments that could be used to script our deployments with the following command:

az sql mi-arc create --help

To create our first SQL MI named sqlmi-01, we ran the following Azure CLI command. For all subsequent SQL MIs, we only modified the CPU and memory specifications and left all storage and volume values as listed here:

az sql mi-arc create --name sqlmi-01 --k8s-namespace arc --dev --replicas 1 --cores-request "4" --cores-limit "4" --memory-request "8Gi" --memory-limit "8Gi" --storage-class-backups "aks-hci-disk-custom" --storage-class-data "aks-hci-disk-custom" --storage-class-logs "aks-hci-disk-custom"--storage-class-datalogs "aks-hci-disk-custom" --volume-size-backups "50Gi" --volume-size-data "50Gi" --volume-size-logs "20Gi"--volume-size-datalogs "50Gi" --use-k8s

Note: We included the --dev switch to avoid the charges for production instances. For more information about SQL MI-Azure Arc General Purpose vCore pricing,  see the Microsoft documentation.

The –storage-class-x parameters in the Azure CLI command that are aligned with the SQL Server files are listed in the following table.

Table 8.      Storage locations for the SQL MI

Every SQL MI pod ran the containers listed in the following table.

Table 9.      Containers that deliver the SQL MI

We listed the containers in each SQL MI pod by running the following kubectl command:

kubectl get pods sqlmi-01-0 -o jsonpath='{.spec.containers[*].name}' -n arc

After we created a managed instance, we issued an Azure CLI command to obtain its primary endpoint IP address and port number. Because the data controller was deployed in indirect mode, we used SQL Server Management Studio and SQLCMD to create and connect to SQL databases:

az sql mi-arc list -k arc --use-k8s

sqlcmd -S <external IP,port number> -U miadmin