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.
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'
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.
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