When creating the SQL MIs, we must first understand vCPU and memory limit and request differences. We also researched Microsoft sizing guidance.  The minimum SQL MI size available consisted of 1 vCPU and 2 GB memory. Only a single replica was created for each SQL MI, as we deployed general-purpose tier instances.

To create our first SQL MI, 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 parameter values as listed here:

az sql mi-arc create --name sqlmi-01 --resource-group rg-A7525R06C01-dev-ps2 --custom-location "parmersouth" --dev --replicas 1 ––retention-days 0 --tier "GeneralPurpose" --cores-request "2" --cores-limit "4" --memory-request "4Gi" --memory-limit "8Gi"--storage-class-data "aks-hci-disk-custom" --storage-class-logs "aks-hci-disk-custom" --storage-class-datalogs "aks-hci-disk-custom" --volume-size-data "100Gi" --volume-size-logs "50Gi"--volume-size-datalogs "50Gi"

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

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

Table 7.      Storage locations for the SQL MI

Note: Microsoft recommends using Read Write Many (RWX) capable storage classes for SQL MI backups for both General Purpose and Business Critical service tiers. By default, if the backup storage class is not specified, the data storage class is used for backups. See Create an Azure SQL Managed Instance for details. Because RWX was not currently available for Cluster Shared Volumes running on Azure Stack HCI, we accepted the default backup configuration for our tests.

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

Table 8.      Containers running in the SQL MI pods

The data controller generates the SQL MI pods’ IP addresses and ports in a consistent pattern. This allowed us to manage multiple deployment scripts and tools in an efficient way. We used the following Azure CLI command to discover the networking information for all the SQL MIs in the arc-services-ns namespace.

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

  {

    "desiredVersion": null,

    "name": "sqlmi-01",

    "primaryEndpoint": "10.129.80.55,1433",

    "replicas": "1/1",

    "runningVersion": "v1.15.0_2023-01-10",

    "state": "Ready"

  },

…….

SQL MI configuration profile

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

• TPC-C database pre-sized data and log files to remove SQL autogrowth contention
• TempDB datafiles = SQL MI requested core count 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 - 16.0.816.19223 (X64) Nov 23 2022 10:48:45   Copyright (C) 2022 Microsoft Corporation General Purpose (64-bit) on Linux (Ubuntu 20.04.5 LTS) <X64>

Note: We chose to remove the automated backup functionality provided with a SQL MI. More information can be found in the Microsoft release notes under the July 2021 release. Setting the retention-days parameter equal to 0 disabled automatic backups.