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.
This SQL Server MIs had the following configuration profile and specifications:
Note: Browsing the SQL Server logs – as an example – 8 Gi allocated container memory SQL MI detects 6,554 MB of memory for SQL
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.
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
Switch |
Description |
--storage-class-data |
SQL data .mdf files |
--storage-class-datalogs |
SQL log .ldf files |
--storage-class-backups |
SQL Server backup files. Full, Diff, T-log |
--storage-class-logs |
SQL agent, error log, trace files, health logs, and so on |
Every SQL MI pod ran the containers listed in the following table.
Table 9. Containers that deliver the SQL MI
Container |
Description |
arc-sqlmi |
The SQL Server executable |
collectd |
A metrics collector |
fluentbit |
Log processor and forwarder |
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