Deploy Azure Arc-enabled SQL Managed Instance in directly connected mode on a Microk8s Kubernetes cluster in an Azure VM using ARM Templates

The following README will guide you on how to deploy a “Ready to Go” environment so you can start using Azure Arc-enabled data services and SQL Managed Instance deployed on a single-node Microk8s Kubernetes cluster.

By the end of this guide, you will have a Microk8s Kubernetes cluster deployed with an Azure Arc Data Controller & SQL Managed Instance (with a sample database), and a Microsoft Windows Server 2022 (Datacenter) Azure Client VM, installed & pre-configured with all the required tools needed to work with Azure Arc-enabled data services:

Deployed Architecture

Note: Currently, Azure Arc-enabled data services with PostgreSQL Hyperscale is in public preview.

Prerequisites

  • Clone the Azure Arc Jumpstart repository

    git clone https://github.com/microsoft/azure_arc.git
    
  • Install or update Azure CLI to version 2.25.0 and above. Use the below command to check your current installed version.

    az --version
    
  • Generate SSH Key (or use existing ssh key).

  • Create Azure service principal (SP)

    To be able to complete the scenario and its related automation, Azure service principal assigned with the “Contributor” role is required. To create it, login to your Azure account run the below command (this can also be done in Azure Cloud Shell).

    az login
    az ad sp create-for-rbac -n "<Unique SP Name>" --role contributor
    

    For example:

    az ad sp create-for-rbac -n "http://AzureArcData" --role contributor
    

    Output should look like this:

    {
      "appId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXX",
      "displayName": "AzureArcData",
      "name": "http://AzureArcData",
      "password": "XXXXXXXXXXXXXXXXXXXXXXXXXXXX",
      "tenant": "XXXXXXXXXXXXXXXXXXXXXXXXXXXX"
    }
    

    Note: It is optional, but highly recommended, to scope the SP to a specific Azure subscription.

Architecture (In a nutshell)

From the Microk8s GitHub repo:

“Microk8s is a single-package, fully conformant, lightweight Kubernetes that works on 42 flavors of Linux. Perfect for Developer workstations, IoT, Edge & CI/CD. MicroK8s tracks upstream and releases beta, RC and final bits the same day as upstream K8s."

In this guide, we automate the installation of Microk8s on an Ubuntu 18.04 VM running on Azure using a few simple commands to install from the Snap Store, before proceeding to onboard it as an Azure Arc-enabled Kubernetes Cluster.

Once our K8s Cluster is onboarded, we proceed to create a Custom Location, and deploy an Azure Arc Data Controller in Directly Connected mode.

Automation Flow

For you to get familiar with the automation and deployment flow, below is an explanation.

  • User is editing the ARM template parameters file (1-time edit). These parameters values are being used throughout the deployment.

  • Main azuredeploy ARM template will initiate five linked ARM templates:

    • VNET - Deploys a Virtual Network with a single subnet - used by our VMs.
    • ubuntuMicrok8s - Deploys an Ubuntu Linux VM which will have Microk8s installed from the Snap Store.
    • clientVm - Deploys the Client Windows VM. This is where all user interactions with the environment are made from.
    • mgmtStagingStorage - Used for staging files in automation scripts and kubeconfig.
    • logAnalytics - Deploys Azure Log Analytics workspace to support Azure Arc-enabled data services logs upload.
  • User remotes into Client Windows VM, which automatically kicks off the DataServicesLogonScript PowerShell script that deploys and configure Azure Arc-enabled data services on the Microk8s Kubernetes cluster - including the data controller.

Deployment

As mentioned, this deployment will leverage ARM templates. You will deploy a single template that will initiate the entire automation for this scenario.

  • The deployment is using the ARM template parameters file. Before initiating the deployment, edit the azuredeploy.parameters.json file located in your local cloned repository folder. An example parameters file is located here (ensure to set deploySQLMI to true):

    • sshRSAPublicKey - Your SSH public key - sample syntax: ssh-rsa AAAAB3N...NDOCE7U3DLBISw==\n.
    • spnClientId - Your Azure service principal id.
    • spnClientSecret - Your Azure service principal secret.
    • spnTenantId - Your Azure tenant id.
    • windowsAdminUsername - Client Windows VM Administrator name.
    • windowsAdminPassword - Client Windows VM Password. Password must have 3 of the following: 1 lower case character, 1 upper case character, 1 number, and 1 special character. The value must be between 12 and 123 characters long.
    • myIpAddress - Your local IP address/CIDR range. This is used to allow remote RDP and SSH connections to the Client Windows VM and Microk8s VM.
    • logAnalyticsWorkspaceName - Unique name for log analytics workspace deployment.
    • deploySQLMI - Boolean that sets whether or not to deploy SQL Managed Instance, for this data controller and Azure SQL Managed Instance scenario, we will set it to true.
    • deployPostgreSQL - Boolean that sets whether or not to deploy PostgreSQL Hyperscale, for this data controller and Azure SQL Managed Instance scenario, we leave it set to false.
    • templateBaseUrl - GitHub URL to the deployment template - filled in by default to point to Microsoft/Azure Arc repository, but you can point this to your forked repo as well.
  • To deploy the ARM template, navigate to the local cloned deployment folder and run the below command:

    az group create --name <Name of the Azure resource group> --location <Azure Region>
    az deployment group create \
    --resource-group <Name of the Azure resource group> \
    --name <The name of this deployment> \
    --template-uri https://raw.githubusercontent.com/microsoft/azure_arc/main/azure_arc_data_jumpstart/microk8s/azure/arm_template/azuredeploy.json \
    --parameters <The *azuredeploy.parameters.json* parameters file location>
    

    Note: Make sure that you are using the same Azure resource group name as the one you’ve just used in the azuredeploy.parameters.json file

    For example:

    az group create --name Arc-Data-Microk8s --location "East US"
    az deployment group create \
    --resource-group Arc-Data-Microk8s \
    --name arcdatademo \
    --template-uri https://raw.githubusercontent.com/microsoft/azure_arc/main/azure_arc_data_jumpstart/microk8s/azure/arm_template/azuredeploy.json \
    --parameters azuredeploy.parameters.json
    --parameters templateBaseUrl="https://raw.githubusercontent.com/your--github--handle/azure_arc/microk8s-data/azure_arc_data_jumpstart/microk8s/azure/arm_template/"
    

    Note: The deployment time for this scenario can take ~15-20min

    Deployment time

  • Once Azure resources have been provisioned, you will be able to see it in the Azure portal. At this point, the resource group should have 13 various Azure resources deployed.

    ARM template deployment completed

    New Azure resource group with all resources

Windows Login & Post Deployment

  • Now that first phase of the automation is completed, it is time to RDP to the Client VM using it’s public IP.

    Client VM public IP

  • At first login, as mentioned in the “Automation Flow” section above, the DataServicesLogonScript PowerShell logon script will start it’s run.

  • Let the script run it’s course and do not close the PowerShell session, this will be done for you once completed.

    PowerShell logon script run

    Once the script will finish it’s run, the logon script PowerShell session will be closed, the Windows wallpaper will change and both the Azure Arc Data Controller and the SQL Managed Instance will be deployed on the cluster and be ready to use:

    Wallpaper Change

  • Since this scenario is deploying the Azure Arc Data Controller and SQL Managed Instance, you will also notice additional newly deployed Azure resources in the resources group (at this point you should have 17 various Azure resources deployed. The important ones to notice are:

    • Azure Arc-enabled Kubernetes cluster - Azure Arc-enabled data services deployed in directly connected mode is using this resource to deploy the data services cluster extension, as well as using Azure Arc Custom locations.

    • Custom location - provides a way for tenant administrators to use their Azure Arc-enabled Kubernetes clusters as a target location for deploying Azure services.

    • Azure Arc Data Controller - The data controller that is now deployed on the Kubernetes cluster.

    • Azure Arc-enabled SQL Managed Instance - The SQL Managed Instance that is now deployed on the Kubernetes cluster.

    Addtional Azure resources in the resource group

  • Another tool automatically deployed is Azure Data Studio along with the Azure Data CLI, the Azure Arc and the PostgreSQL extensions. Using the Desktop shortcut created for you, open Azure Data Studio and click the Extensions settings to see both extensions.

    Azure Data Studio shortcut

  • Additionally, the SQL Managed Instance connection will be configured within Data Studio, as well as the sample AdventureWorks database will be restored automatically for you.

    Configured SQL Managed Instance connection

Cluster extensions

In this scenario, three Azure Arc-enabled Kubernetes cluster extensions were deployed:

Operations

Azure Arc-enabled SQL Managed Instance Stress Simulation

Included in this scenario, is a dedicated SQL stress simulation tool named SqlQueryStress automatically installed for you on the Client VM. SqlQueryStress will allow you to generate load on the Azure Arc-enabled SQL Managed Instance that can be done used to showcase how the SQL database and services are performing as well to highlight operational practices described in the next section.

  • To start with, open the SqlQueryStress desktop shortcut and connect to the SQL Managed Instance primary endpoint IP address. This can be found in the SQLMI Endpoints text file desktop shortcut that was also created for you alongside the username and password you used to deploy the environment.

    Open SqlQueryStress

    SQLMI Endpoints text file

Note: Secondary SQL Managed Instance endpoint will be available only when using the HA deployment model (“Business Critical”).

  • To connect, use “SQL Server Authentication” and select the deployed sample AdventureWorks database (you can use the “Test” button to check the connection).

    SqlQueryStress connected

  • To generate some load, we will be running a simple stored procedure. Copy the below procedure and change the number of iterations you want it to run as well as the number of threads to generate even more load on the database. In addition, change the delay between queries to 1ms for allowing the stored procedure to run for a while.

    exec [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 8
    
  • As you can see from the example below, the configuration settings are 100,000 iterations, five threads per iteration, and a 1ms delay between queries. These configurations should allow you to have the stress test running for a while.

    SqlQueryStress settings

    SqlQueryStress running

Azure Arc-enabled SQL Managed Instance monitoring using Grafana

When deploying Azure Arc-enabled data services, a Grafana instance is also automatically deployed on the same Kubernetes cluster and include built-in dashboards for both Kubernetes infrastructure as well SQL Managed Instance monitoring (PostgreSQL dashboards are included as well but we will not be covering these in this section).

  • Now that you have the SqlQueryStress stored procedure running and generating load, we can look how this is shown in the the built-in Grafana dashboard. As part of the automation, a new URL desktop shortcut simply named “Grafana” was created.

    Grafana desktop shortcut

  • [Optional] The IP address for this instance represents the Kubernetes LoadBalancer external IP that was provision as part of Azure Arc-enabled data services. Use the kubectl get svc -n arc command to view the metricsui external service IP address.

    metricsui Kubernetes service

  • To log in, use the same username and password that is in the SQLMI Endpoints text file desktop shortcut.

    Grafana username and password

  • Navigate to the built-in “SQL Managed Instance Metrics” dashboard.

    Grafana dashboards

    Grafana “SQL Managed Instance Metrics” dashboard

  • Change the dashboard time range to “Last 5 minutes” and re-run the stress test using SqlQueryStress (in case it was already finished).

    Last 5 minutes time range

  • You can now see how the SQL graphs are starting to show increased activity and load on the database instance.

    Increased load activity

    Increased load activity

Cleanup

  • If you want to delete the entire environment, simply delete the deployed resource group from the Azure portal.

    Delete Azure resource group