Tutorial: Add SQL Managed Instance to a failover group

APPLIES TO: Azure SQL Managed Instance

Add managed instances of Azure SQL Managed Instance to a failover group. In this article, you will learn how to:

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

Note

  • When going through this tutorial, ensure you are configuring your resources with the prerequisites for setting up failover groups for SQL Managed Instance.
  • Creating a managed instance can take a significant amount of time. As a result, this tutorial could take several hours to complete. For more information on provisioning times, see SQL Managed Instance management operations.
  • Managed instances participating in a failover group require Azure ExpressRoute, global VNet peering, or two connected VPN gateways. This tutorial provides steps for creating and connecting the VPN gateways. Skip these steps if you already have ExpressRoute configured.

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 performance reasons. Managed instances residing in geo-paired regions have much better performance compared to 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 managed instance, you will need to create the virtual network separately because there is a requirement that the subnet of the primary and secondary managed instance do not have overlapping 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. The subnet address range of the virtual network for the secondary managed instance cannot overlap this.

    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 published by Microsoft 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 values necessary 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 provided for you by default.
    Address range The address range for your subnet. This must be different than the subnet address range used by the virtual network of your primary managed instance, such as 10.128.0.0/24.
       

    Secondary virtual network values

Create a secondary managed instance

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

Your second managed instance must:

  • Be empty.
  • Have a different subnet and IP range than the primary managed instance.

Create the secondary 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 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 subscription where your primary managed instance is.
    Resource group The resource group where your primary managed instance is.
    SQL Managed Instance name The name of your new secondary managed instance, such as sql-mi-secondary.
    Region The location 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 the virtual network you created for the secondary managed instance from the drop-down.

    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 primary gateway

For two managed instances to participate in a failover group, there must be either ExpressRoute or a gateway configured between the virtual networks of the two managed instances to allow network communication. If you choose to configure ExpressRoute instead of connecting two VPN gateways, skip ahead to Step 7.

This article provides steps to create the two VPN gateways and connect them, but you can skip ahead to creating the failover group if you have configured ExpressRoute instead.

Note

The SKU of the gateway affects throughput performance. This tutorial deploys a gateway with the most basic SKU (HwGw1). Deploy a higher SKU (example: VpnGw3) to achieve higher throughput. For all available options, see Gateway SKUs

Create the gateway for the virtual network of your primary managed instance using the Azure portal.

  1. In the Azure portal, go to your resource group and select the Virtual network resource for your primary managed instance.

  2. Select Subnets under Settings and then select to add a new Gateway subnet. Leave the default values.

    Add gateway for primary managed instance

  3. Once the subnet gateway is created, select Create a resource from the left navigation pane and then type Virtual network gateway in the search box. Select the Virtual network gateway resource published by Microsoft.

    Create a new virtual network gateway

  4. Fill out the required fields to configure the gateway for your primary managed instance.

    The following table shows the values necessary for the gateway for the primary managed instance:

    Field Value
    Subscription The subscription where your primary managed instance is.
    Name The name for your virtual network gateway, such as primary-mi-gateway.
    Region The region where your primary managed instance is.
    Gateway type Select VPN.
    VPN Type Select Route-based.
    SKU Leave default of VpnGw1.
    Virtual network Select the virtual network that was created in section 2, such as vnet-sql-mi-primary.
    Public IP address Select Create new.
    Public IP address name Enter a name for your IP address, such as primary-gateway-IP.
       
  5. Leave the other values as default, and then select Review + create to review the settings for your virtual network gateway.

    Primary gateway settings

  6. Select Create to create your new virtual network gateway.

Create secondary gateway

In this step, create the gateway for the virtual network of your secondary managed instance using the Azure portal.

Using the Azure portal, repeat the steps in the previous section to create the virtual network subnet and gateway for the secondary managed instance. Fill out the required fields to configure the gateway for your secondary managed instance.

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

Field Value
Subscription The subscription where your secondary managed instance is.
Name The name for your virtual network gateway, such as secondary-mi-gateway.
Region The region where your secondary managed instance is.
Gateway type Select VPN.
VPN Type Select Route-based.
SKU Leave default of VpnGw1.
Virtual network Select the virtual network for the secondary managed instance, such as vnet-sql-mi-secondary.
Public IP address Select Create new.
Public IP address name Enter a name for your IP address, such as secondary-gateway-IP.
   

Secondary gateway settings

Connect the gateways

In this step, create a bidirectional connection between the two gateways of the two virtual networks.

Connect the two gateways using the Azure portal.

  1. Select Create a resource from the Azure portal.

  2. Type connection in the search box and then press enter to search, which takes you to the Connection resource, published by Microsoft.

  3. Select Create to create your connection.

  4. On the Basics page, select the following values and then select OK.

    1. Select VNet-to-VNet for the Connection type.
    2. Select your subscription from the drop-down.
    3. Select the resource group for SQL Managed Instance in the drop-down.
    4. Select the location of your primary managed instance from the drop-down.
  5. On the Settings page, select or enter the following values and then select OK:

    1. Choose the primary network gateway for the First virtual network gateway, such as primaryGateway.
    2. Choose the secondary network gateway for the Second virtual network gateway, such as secondaryGateway.
    3. Select the checkbox next to Establish bidirectional connectivity.
    4. Either leave the default primary connection name, or rename it to a value of your choice.
    5. Provide a Shared key (PSK) for the connection, such as mi1m2psk.
    6. Select OK to save your settings.

    Create gateway connection

  6. On the Review + create page, review the settings for your bidirectional connection and then select OK to create your connection.

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 favorite it and add it as an 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 Settings, navigate to Instance 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. Review which managed instance is the primary, and which managed instance is the secondary.

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

    Fail over the failover group

  4. Review which 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.

  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: