User-Initiated Manual Failover On SQL Managed Instance

This post explains how to perform User-initiated manual failover on SQL Managed Instance on a primary node with General Purpose (GP) and Business Critical (BC) service tiers, and how to manually failover a secondary read-only replica node on the Business Critical service tier only for a database application.

Topics we’ll cover :

• What is Failover
• When To use failover
• How to initiate a manual failover in the managed instance
• How is high availability is implemented on a managed instance
• Functional Limitations

What Is Failover

A failover group is a named group of databases managed by a single server or within a managed instance that can fail over as a unit to another region in case all or some primary databases become unavailable due to an outage in the primary region.

When To Use Manual Failover

High availability is a fundamental part of the SQL Managed Instance platform that works transparently for your database applications. As such, failovers from primary to secondary nodes in case of node degradation or fault detection, or during our regular monthly software updates are an expected occurrence for all applications using SQL MI in Azure. This is why it is important to ensure your applications (legacy apps migrated to the cloud or cloud-born apps) are “cloud-ready” and resilient to transient errors typical for the cloud environments.

Your cloud-ready applications need to follow some principles:

• The application must be able to detect errors and faults when they occur.
• The application must be able to determine if the fault is transient.
• The application needs to retry an operation in case of any fault.
• The application must use an appropriate strategy for the retries.

How To Initiate A Manual Failover On SQL Managed Instance

Remote Based Access Control Permission Required

• Subscription Owner role
• Managed Instance Contributor role
• Custom role with the following permission: Microsoft.Sql/managed instances/failover/action

1.) Using PowerShell

1. If you have several Azure subscriptions, then select the Azure subscription in which your managed instance

$subscription = 'enter your subscription ID here'
Install-Module -Name Az
Import-Module Az.Accounts
Import-Module Az.Sql

Connect-AzAccount
Select-AzSubscription -SubscriptionId $subscription

2. Use Powershell command Invoke-AzSqlInstanceFailover with the following example to initiate failover of the primary node, applicable to both BC and GP service tiers:

$ResourceGroup = 'enter resource group of your MI'
$ManagedInstanceName = 'enter MI name'
Invoke-AzSqlInstanceFailover -ResourceGroupName $ResourceGroup -Name $ManagedInstanceName

3. Use the below Powershell command to failover read secondary node, applicable to BC service tier only.

$ResourceGroup = 'enter resource group of your MI'
$ManagedInstanceName = 'enter MI name'
Invoke-AzSqlInstanceFailover -ResourceGroupName $ResourceGroup -Name $ManagedInstanceName -ReadableSecondary

2.) Using CLI

Use az SQL mi failover CLI command with the following example to initiate failover of the primary node, applicable to both Business Critical and General purpose service tiers.

az sql mi failover -g myresourcegroup -n myinstancename

Use the following CLI command to failover read secondary node, applicable to Business Critical service tier only.

az sql mi failover -g myresourcegroup -n myinstancename --replica-type ReadableSecondary

3.) Using Rest API

For advanced users who would perhaps like to automate failovers of their SQL Managed Instances for purposes of implementing continuous testing pipeline, or automated performance mitigators, this can be accomplished through initiating failover through an API call, see Managed Instances – Failover REST API for details.

To initiate failover using a REST API call, first generate the Authentication Token. One way to do that is to use a Postman client. Initiating the API call from any other client should generally work as well. This token is used as an Authorization property in the header of API request and it is mandatory.

The below are the API calls properties:

Check Out: What is Azure Data Studio?

How Is High Availability Implemented On A Managed Instance

Azure SQL Managed Instance (MI) is offered in two service tiers, one is Business Critical (BC) and the other one is General Purpose (GP). Both service tiers offer High Availability (HA), with different technical implementations, as follows:

HA for SQL Managed Instance BC (Business Critical) administration level was assembled dependent on AlwaysOn Availability Groups (AG), bringing about such MI comprising of the absolute of 4 nodes – one primary and three optional R/O replicas. In the event of a failover, one of the optional copies becomes primary. This sort of failover normally requires a couple of seconds.

High Availability for SQL Managed Instance General Purpose service tiers was based on multiple redundancies of the storage layer and it is based on a single primary node. In case of a failover, a new node is taken from the pool of standby nodes, and the storage is re-attached from the old to the new primary node. This sort of failover normally requires a couple of seconds.

Using the user-initiated manual failover functionality, manually initiating a failover on the MI BC service tier will result in failover of the primary node to 1 of the three secondary nodes. As secondary read-only nodes on the MI BC service tier are often used for reading scale-out from one node (out of three read-only secondary nodes).

The user-initiated manual failover capability admits a failover of read-only replicas. this suggests that users can manually failover the read scale-out from this to a minimum of one among the two other available read-only secondary nodes.

Manually initiating a failover on Managed Instance General Purpose service tier will result in deallocation of the primary node, and the allocation of a new node from the pool of available nodes, and reattachment of the storage from the old to the new node.

Functional limitations

• Failover will not be permitted until the primary full backup for another new database is completed by automated backup systems.
• If any database restoration is in progress then in this case the failover is not allowed.
• There could be one failover initiated on the same Managed Instance every 15 minutes.
• For Business-Critical instances, there must exist a quorum of replicas for the failover request to be accepted.
• For Business-Critical instances, it is not possible to specify which readable secondary replica to initiate the failover on.

Related/References

• Exam DP-300: Microsoft Azure Database Administrator Associate
• Microsoft Certified Azure Database Administrator Associate | DP 300 | Step By Step Activity Guides (Hands-On Labs)
• Azure SQL Database | All you need to know about Azure SQL Services

Next Task For You

In our Azure Database Administrator training program, we will cover all the exam objectives, 24 Hands-On Labs, and practice tests. If you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.