“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:
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 ` --use-k8s
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
- 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.