Troubleshooting: Unable to Add Instance Failover Group to Azure SQL Managed Instance

By /
Unable to Add Instance Failover Group to Azure SQL Managed Instance
Post Views: 4

Overview

Unable to Add Instance Failover Group to Azure SQL Managed Instance: Many customers encounter issues when trying to create an Instance Failover Group for an Azure SQL Managed Instance. Specifically, they find that the secondary managed instance appears dimmed (disabled/greyed out) in the Instance Failover Group Wizard Portal, preventing selection.

Unable to Add Instance Failover Group to Azure SQL Managed Instance

Additionally, executing the PowerShell command:

AzSqlDatabaseInstanceFailoverGroup

also, results in a 500 Internal Server Error.

Also Read: How to login to different tenant in Azure portal login?

Reason for the Issue

The most common cause of this issue is that the secondary managed instance’s DNS Zone does not match the DNS Zone of the primary managed instance.

When the DNS Zone on the secondary instance differs from that of the primary, the Instance Failover Groupfunctionality is disrupted, making the option unavailable in the portal and failing in PowerShell.

Understanding the DNS Zone in Azure SQL Managed Instance

  • The DNS zone is a critical property of a SQL Managed Instance and its underlying virtual cluster.
  • The DNS zone ID is embedded in the host name address of the instance.
  • The zone ID is generated as a random string when the first SQL Managed Instance is created within a VNet.
  • All subsequent instances within the same subnet inherit the same DNS zone ID.
  • Important: Once assigned, the DNS zone cannot be changed.
  • All SQL Managed Instances in the same Failover Group must share the same DNS zone ID.

Solution

To resolve this issue, ensure that the secondary managed instance is created with the correct DNS Zone ID.

Steps to Fix the Issue

  1. Create the Secondary Managed Instance with the Correct DNS Zone
    • When provisioning the secondary managed instance, pass the primary instance’s DNS Zone ID using the DnsZonePartner parameter.
    • This ensures that both the primary and secondary instances share the same DNS Zone.
  2. Select ‘Use as Failover Secondary’ During Instance Creation
    • When creating the secondary instance, ensure you select Yes for the “Use as failover secondary” option.
    • unabletocreatefailovergroup png
  3. Verify Additional Requirements for the Secondary Managed Instance
    • The secondary instance must be in a different Azure region.
    • It must be an empty managed instance and have the same max-size as the primary instance.
    • The secondary instance must be in a different subnet and have a different IP range from the primary.

Also Read: How to Configure OAuth Authentication in Azure AD for Accessing Office 365 Mailbox via Microsoft Graph API

Additional Best Practices and Considerations

In addition to the above steps, ensure that your network configurations and security policies are optimized to support failover functionality. Regularly verify that firewall settings, virtual network peering, and network security groups are not inadvertently blocking communication between instances. Testing failover scenarios in a controlled environment can further validate that the configuration works as expected. Continuously monitor DNS settings and other configurations through Azure’s monitoring tools to quickly identify and resolve discrepancies. Such proactive measures can prevent unexpected downtime and strengthen your overall system resilience.

Conclusion

If you are unable to add an Instance Failover Group to an Azure SQL Managed Instance, the most likely cause is a DNS Zone mismatch between the primary and secondary instances. By ensuring the correct DNS Zone ID is assigned during secondary instance creation and fulfilling the additional requirements, you can successfully establish an Instance Failover Group without encountering issues. For further troubleshooting, consider checking Azure Activity Logs, PowerShell error messages, and Azure Portal Notifications to identify potential backend operation failures

For further troubleshooting, consider checking Azure Activity Logs, PowerShell error messages, and Azure Portal Notifications to identify potential backend operation failures.

External 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

Ravi Chopra
Latest posts by Ravi Chopra (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top