SQL Managed Instance Failover behaviors

Question

We have setup a primary and secondary SQL managed instance within a failover group. The replication on these instances appears to be working, and the failover testing from the portal does failover correctly. I have a question about the failover behavior though.

The failover group has two endpoints, read-write and read-only. In it's initial state, the r/w endpoint points to the Primary instance (Central-US) and the r/o endpoint points to the Secondary instance (East US 2). My question is, if the secondary instance fails in a disaster scenario, and stays unavailable for a long period of time, does the r/o endpoint automatically recognize this and use the available instance? If a serious power/network outage removed one instance, would my read-only services be impacted, or automatically failover to the available endpoint?

I found in the documentation that SQL Instances (non-MI) have a configurable option for this kind of behavior but I have been unable to find anything pointing to this option being configurable with a managed instance. We have been so far unable to mimic a scenario where one instance was unavailable for a significant period of time; the vNet Security Group would not allow me to place rules that would halt network connectivity, even if only for testing.

Thanks in advance.

Answer 1

Hi, @Corey Dodd Welcome to Microsoft Q&A, Thanks for posting your question.

A read-only endpoint failover policy can be configured for SQL MI as well if an automatic failover policy is configured for the failover group.

Check e.g. PowerShell command documentation, more specifically parameter AllowReadOnlyFailoverToPrimary.

Just like with SQL DB, the option is not configurable on the Azure portal.

Please let us know if the information helps.

Regards
Geetha

Answer 2

@GeethaThatipatri-MSFT , I was able with a bit of experimentation to get this value changed.

It turns out, I was using Azure CLI, and needed to be using the Azure PowerShell module. Once I installed the proper module, I was able to test the commands here. The specific syntax needed that ended up changning my ReadOnlyFailoverPolicy parameter is as follows:

Set-AzSqlDatabaseInstanceFailoverGroup -name <failover group name> -ResourceGroupName <resource group name> -Location <primary instance location> -AllowReadOnlyFailoverToPrimary Enabled

The problems were specifically that I was using "true" instead of "Enabled" on the AllowReadOnlyFailoverToPrimary flag, and that I assumed the Name flag was wanting the MI name, not the Failover Group name.

This problem has been resolved, and thank you so much for your assistance.