Tutorial: Add SQL Managed Instance to a failover group

Applies to: Azure SQL Managed Instance

Add managed instances of Azure SQL Managed Instance to an auto-failover group.

In this tutorial, you will learn how to:

  • Create a primary managed instance.
  • Create a secondary managed instance as part of a failover group.
  • Test failover.

There are multiple ways to establish connectivity between managed instances in different Azure regions, including:

This tutorial provides steps for global virtual network peering. If you prefer to use ExpressRoute or VPN gateways, replace the peering steps accordingly, or skip ahead to Step 7 if you already have ExpressRoute or VPN gateways configured.

Important

Prerequisites

To complete this tutorial, make sure you have:

Create a resource group and primary managed instance

In this step, you will create the resource group and the primary managed instance for your failover group using the Azure portal or PowerShell.

Deploy both managed instances to paired regions for data replication performance reasons. Managed instances residing in geo-paired regions have much better data replication performance compared to instances residing in unpaired regions.

Create the resource group and your primary managed instance using the Azure portal.

  1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, and then type Azure SQL in the search box. (Optional) Select the star next to Azure SQL to favorite it and add it as an item in the left-hand navigation.

  2. Select + Add to open the Select SQL deployment option page. You can view additional information about the different databases by selecting Show details on the Databases tile.

  3. Select Create on the SQL Managed Instances tile.

    Select SQL Managed Instance

  4. On the Create Azure SQL Managed Instance page, on the Basics tab:

    1. Under Project Details, select your Subscription from the drop-down and then choose to Create New resource group. Type in a name for your resource group, such as myResourceGroup.
    2. Under SQL Managed Instance Details, provide the name of your managed instance, and the region where you would like to deploy your managed instance. Leave Compute + storage at default values.
    3. Under Administrator Account, provide an admin login, such as azureuser, and a complex admin password.

    Create primary managed instance

  5. Leave the rest of the settings at default values, and select Review + create to review your SQL Managed Instance settings.

  6. Select Create to create your primary managed instance.

Create secondary virtual network

If you're using the Azure portal to create your secondary managed instance, you will need to create the virtual network before creating the instance to make sure that the subnets of the primary and secondary managed instance do not have overlapping IP address ranges. If you're using PowerShell to configure your managed instance, skip ahead to step 3.

To verify the subnet range of your primary virtual network, follow these steps:

  1. In the Azure portal, navigate to your resource group and select the virtual network for your primary instance.

  2. Select Subnets under Settings and note the Address range of the subnet created automatically during creation of your primary instance. The subnet IP address range of the virtual network for the secondary managed instance must not overlap with the IP address range of the subnet hosting primary instance.

    Primary subnet

To create a virtual network, follow these steps:

  1. In the Azure portal, select Create a resource and search for virtual network.

  2. Select the Virtual Network option and then select Create on the next page.

  3. Fill out the required fields to configure the virtual network for your secondary managed instance, and then select Create.

    The following table shows the required fields and corresponding values for the secondary virtual network:

    Field Value
    Name The name for the virtual network to be used by the secondary managed instance, such as vnet-sql-mi-secondary.
    Address space The address space for your virtual network, such as 10.128.0.0/16.
    Subscription The subscription where your primary managed instance and resource group reside.
    Region The location where you will deploy your secondary managed instance.
    Subnet The name for your subnet. default is offered as a default name.
    Address range The IP address range for your subnet, such as 10.128.0.0/24. This must not overlap with the IP address range used by the virtual network subnet of your primary managed instance.

    Secondary virtual network values

Create a secondary managed instance

In this step you will create a secondary managed instance, which will also configure the networking between the two managed instances.

Your second managed instance must be:

  • Empty, i.e. with no user databases on it.
  • Hosted in a virtual network subnet that has no IP address range overlap with the virtual network subnet hosting the primary managed instance.
  1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, and then type Azure SQL in the search box. (Optional) Select the star next to Azure SQL to add it as a favorite item in the left-hand navigation.

  2. Select + Add to open the Select SQL deployment option page. You can view additional information about the different databases by selecting Show details on the Databases tile.

  3. Select Create on the SQL managed instances tile.

    Select SQL Managed Instance

  4. On the Basics tab of the Create Azure SQL Managed Instance page, fill out the required fields to configure your secondary managed instance.

    The following table shows the values necessary for the secondary managed instance:

    Field Value
    Subscription The Azure subscription to create the instance in. When using Azure portal, it must be the same subscription as for primary instance.
    Resource group The resource group to create secondary managed instance in.
    SQL Managed Instance name The name of your new secondary managed instance, such as sql-mi-secondary.
    Region The Azure region for your secondary managed instance.
    SQL Managed Instance admin login The login you want to use for your new secondary managed instance, such as azureuser.
    Password A complex password that will be used by the admin login for the new secondary managed instance.
  5. Under the Networking tab, for the Virtual Network, select from the drop-down list the virtual network you previously created for the secondary managed instance.

    Secondary MI networking

  6. Under the Additional settings tab, for Geo-Replication, choose Yes to Use as failover secondary. Select the primary managed instance from the drop-down.

    Be sure that the collation and time zone match that of the primary managed instance. The primary managed instance created in this tutorial used the default of SQL_Latin1_General_CP1_CI_AS collation and the (UTC) Coordinated Universal Time time zone.

    Secondary managed instance networking

  7. Select Review + create to review the settings for your secondary managed instance.

  8. Select Create to create your secondary managed instance.

Create a global virtual network peering

Note

The steps listed below will create peering links between the virtual networks in both directions.

  1. In the Azure portal, go to the Virtual network resource for your primary managed instance.
  2. Select Peerings under Settings and then select + Add.

Screenshot of peerings page for VNetA

  1. Enter or select values for the following settings:

    Settings Description
    This virtual network
    Peering link name The name for the peering must be unique within the virtual network.
    Traffic to remote virtual network Select Allow (default) to enable communication between the two virtual networks through the default VirtualNetwork flow. Enabling communication between virtual networks allows resources that are connected to either virtual network to communicate with each other with the same bandwidth and latency as if they were connected to the same virtual network. All communication between resources in the two virtual networks is over the Azure private network.
    Traffic forwarded from remote virtual network Both Allowed (default) and Block option will work for this tutorial. For more information, see Create a peering
    Virtual network gateway or Route Server Select None. For more information about the other options available, see Create a peering.
    Remote virtual network
    Peering link name The name of the same peering to be used in the virtual network hosting secondary instance.
    Virtual network deployment model Select Resource manager.
    I know my resource ID Leave this checkbox unchecked.
    Subscription Select the Azure subscription of the virtual network hosting the secondary instance that you want to peer with.
    Virtual network Select the virtual network hosting the secondary instance that you want to peer with. If the virtual network is listed, but grayed out, it may be because the address space for the virtual network overlaps with the address space for this virtual network. If virtual network address spaces overlap, they cannot be peered.
    Traffic to remote virtual network Select Allow (default)
    Traffic forwarded from remote virtual network Both Allowed (default) and Block option will work for this tutorial. For more information, see Create a peering.
    Virtual network gateway or Route Server Select None. For more information about the other options available, see Create a peering.
  2. Click Add to configure the peering with the virtual network you selected. After a few seconds, select the Refresh button and the peering status will change from Updating to Connected.

    Virtual network peering status on peerings page

Create a failover group

In this step, you will create the failover group and add both managed instances to it.

Create the failover group using the Azure portal.

  1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, and then type Azure SQL in the search box. (Optional) Select the star next to Azure SQL to add it as a favorite item in the left-hand navigation.

  2. Select the primary managed instance you created in the first section, such as sql-mi-primary.

  3. Under Data management, navigate to Failover groups and then choose Add group to open the Instance Failover Group page.

    Add a failover group

  4. On the Instance Failover Group page, type the name of your failover group, such as failovergrouptutorial. Then choose the secondary managed instance, such as sql-mi-secondary, from the drop-down. Select Create to create your failover group.

    Create failover group

  5. Once failover group deployment is complete, you will be taken back to the Failover group page.

Test failover

In this step, you will fail your failover group over to the secondary server, and then fail back using the Azure portal.

Test failover using the Azure portal.

  1. Navigate to your secondary managed instance within the Azure portal and select Instance Failover Groups under settings.

  2. Note managed instances in the primary and in the secondary role.

  3. Select Failover and then select Yes on the warning about TDS sessions being disconnected.

    Fail over the failover group

  4. Review that managed instance is the primary and which managed instance is the secondary. If failover succeeded, the two instances should have switched roles.

    Managed instances have switched roles after failover

  5. Go to the new secondary managed instance and select Failover once again to fail the primary instance back to the primary role.

Clean up resources

Clean up resources by first deleting the managed instances, then the virtual cluster, then any remaining resources, and finally the resource group. Failover group will be automatically deleted when you delete any of the two instances.

  1. Navigate to your resource group in the Azure portal.
  2. Select the managed instance(s) and then select Delete. Type yes in the text box to confirm you want to delete the resource and then select Delete. This process may take some time to complete in the background, and until it's done, you will not be able to delete the virtual cluster or any other dependent resources. Monitor the deletion in the Activity tab to confirm your managed instance has been deleted.
  3. Once the managed instance is deleted, delete the virtual cluster by selecting it in your resource group, and then choosing Delete. Type yes in the text box to confirm you want to delete the resource and then select Delete.
  4. Delete any remaining resources. Type yes in the text box to confirm you want to delete the resource and then select Delete.
  5. Delete the resource group by selecting Delete resource group, typing in the name of the resource group, myResourceGroup, and then selecting Delete.

Full script

There are no scripts available for the Azure portal.

Next steps

In this tutorial, you configured a failover group between two managed instances. You learned how to:

  • Create a primary managed instance.
  • Create a secondary managed instance as part of a failover group.
  • Test failover.

Advance to the next quickstart on how to connect to SQL Managed Instance, and how to restore a database to SQL Managed Instance: