Learning from Expertise #4: Why cannot add instance Failover Group to Azure SQL Managed Instance?!
Published Oct 15 2021 07:57 AM 3,935 Views
Microsoft

Overview:

We see customers were not able to create an instance (secondary) failover group to Azure SQL Managed Instance (primary), the secondary managed instance shows dimmed (disabled). hence, they cannot choose it from drop list on Instance Failover group wizard portal.

 

Ahmed_S_Mahmoud_1-1634306464710.png

 

In case the customer is using powershell command AzSqlDatabaseInstanceFailoverGroup

 

Write-host "Creating the failover group..."
$failoverGroup = New-AzSqlDatabaseInstanceFailoverGroup -Name $failoverGroupName `
     -Location $location -ResourceGroupName $resourceGroupName -PrimaryManagedInstanceName $primaryInstance `
     -PartnerRegion $drLocation -PartnerManagedInstanceName $secondaryInstance `
     -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1
$failoverGroup

 

It still failing with error like: 

Error Code: 500 InternalServerError 
Error Message: An unexpected error occurred while processing the request. Tracking ID: 'XXXXXX-XXX-XXXX-XXXX-XXXXXXXX'

Partner managed instance's DNS Zone does not match the DNS Zone of the source managed instance.

 

Solution:

In this scenario, most properly the operation is disabled on the portal or stuck on the backend because the secondary managed instance's DNS Zone does not match the DNS Zone of the source managed instance (primary).

 

When the DNS Zone on the secondary server is different from the DNS Zone on the primary server. This breaks InstanceFailoverGroup functionality.

 

DNS zone is a property of a SQL Managed Instance and underlying virtual cluster, and its ID is included in the host name address. The zone ID is generated as a random string when the first SQL Managed Instance is created in each VNet and the same ID is assigned to all other instances in the same subnet. Once assigned, the DNS zone cannot be modified. SQL Managed Instances included in the same failover group must share the DNS zone. You accomplish this by passing the primary instance's zone ID as the value of DnsZonePartner parameter when creating the secondary instance.

 

Note:- You need to make sure to choose Yes to *Use as failover secondary* while creating the secondary managed instance.

 

Ahmed_S_Mahmoud_4-1634308167153.png

 

Another requirements should be considered that the secondary managed instance must:

  • Be from a different region.
  • Be an empty managed instance has the same max-size as the primary.
  • Have a different subnet and IP range than the primary managed instance.

Additional information can be found in references:

Auto-failover groups - Azure SQL Database & SQL Managed Instance | Microsoft Docs

Tutorial: Add SQL Managed Instance to a failover group - Azure SQL Managed Instance | Microsoft Docs

 

I hope you find this article helpful. If you have any feedback please do not hesitate to provide it in the comment section below.

 

Ahmed S. Mazrouh

Co-Authors
Version history
Last update:
‎Oct 15 2021 07:58 AM
Updated by: