Arc Enabled Data Services – 10

Managing the Azure Arc-enabled SQL MI

Views 674

“SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL Server database engine compatibilities with all the benefits of a fully managed and evergreen platform as a service.”

Azure Arc-enabled SQL Managed Instance allows you to take advantage of the benefits of Azure SQL Managed Instance while hosting them on your hardware in your On-premises environment or another public cloud.

With this in mind, there are components we have control over and need to configure based on our requirements:

  • Data Controller Upgrade Management
  • Arc-enabled SQL MI Upgrade Management
  • Arc-enabled SQL MI Server Options

Data Controller Upgrade Management

Managing your Data Controller through Azure Data Studio provides an easy approach for controlling when you wish to apply upgrades supplied by Microsoft. This is something you need to stay on top of as the upgrade path is an incremental approach and only allows you to upgrade to the next version. If you leave it too long, you could be faced with multiple upgrades to be undertaken. This could lead to extended outages while you get up to date.

In Azure Data Studio, right-click on your Data Controller and select Manage. You will see the Upgrade Management tab, which displays if there are any upgraded versions available for you.

It is important to undertake upgrades on the Data Controller, prior to undertaking any upgrades on the Arc-enabled SQL Managed Instances you have configured.

A Data Controller configured at the same version or 1 version in front of the Arc-enabled SQL Managed Instance is supported, but never behind.

An upgrade process can be kicked off by using the Upgrade button in Azure Data Studio or by issuing the az arcdata dc upgrade cmd.

Once you have kicked off an upgrade you can track the status of the upgrade by executing the following command:

Kubectl get datacontrollers -A

The upgrade process should go through the following statuses:

  • Ready
  • Updating
  • UpdatingDependentResources
  • Ready

Now depending on your network bandwidth, the resources applied to your kubernetes cluster, and the details included in the upgrade this could take some time.

Arc-enabled SQL MI Upgrade Management

You will want to configure the maintenance window based on your environment requirements and workloads. This will dictate when upgrades are able to be applied. The setting of the maintenance window is only available via CLI commands in either Azure Data Studio (terminal window) or the Azure Portal Cloud Shell.

To view the current configuration, run the following command:

Kubectl describe datacontroller -n [Data Controller Namespace]

A lot of information about the Data Controller will be displayed. Under Settings, you are looking for Maintenance.  As we have not set it up yet, no details will be being displayed.

Using the following command we are able to set the required maintenance window:

Az arcdata dc update --maintenance-start

This functionality can be configured to be applied automatically, but I would not recommend this unless you have seriously tested in your environment and understand the outage durations and how that may impact your environment SLAs. The commands to enable auto updates is:

az sql mi-arc upgrade --name $Env:sql-managed-instanceName `
    --desired-version auto  `
    --k8s-namespace $Env:MyNamespace `

Arc-enabled SQL MI Server Options

You have a number of options available to you to configure in your newly provisioned environment. All of these are undertaken using the az sql mi-arc update cli commands. Some of the options are available to be adjusted from within Azure Data Studio.

Expand Azure Arc Controllers in the Connections Tab, and expand a data controller to see the list of available Arc-enabled SQL Managed Instances. Right-click on the Arc-enabled SQL Managed Instance and select manage.  Here you will have the ability to change/configure the following:

  • Compute resources
    • CPU
    • Memory
    • Synchronous Secondary Replicas
  • Backup Retention policy

But for everything else like:

  • Enabling SQL Agent
  • Configuring Active Directory
  • Adjusting the number of HA replicas
  • Adjusting the number of readable secondaries
  • Enabling traceflags

You will need to understand and use the az sql mi-arc cli command.

In our next post, we will have a look at Managing the Azure Arc-enabled SQL Managed Instance High Availability.

Leave a Reply

Your email address will not be published. Required fields are marked *


I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.