Skip to content

Latest commit

 

History

History
429 lines (289 loc) · 29.6 KB

failover-group-add-instance-tutorial.md

File metadata and controls

429 lines (289 loc) · 29.6 KB
title titleSuffix description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom
Tutorial: Add SQL Managed Instance to a failover group
Azure SQL Managed Instance
In this tutorial, learn to create a failover group between a primary and secondary Azure SQL Managed Instance.
Stralle
strrodic
mathoma
04/20/2023
sql-managed-instance
high-availability
tutorial
sqldbrb=1
devx-track-azurepowershell

Tutorial: Add SQL Managed Instance to a failover group

[!INCLUDEappliesto-sqlmi]

[!div class="op_single_selector"]

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

In this tutorial, you will learn how to:

[!div class="checklist"]

  • 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

Portal

To complete this tutorial, make sure you have:

Azure PowerShell

To complete the tutorial, make sure you have the following items:

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.

Portal

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.

Azure PowerShell

Create your resource group and the primary managed instance with Azure PowerShell by using the commands in this section, or check the Full script for an example.

Run these commands in the following order to create your resource group, and primary SQL Managed Instance by using Azure PowerShell.

Command Notes
1. Connect-AzAccount Connect to Azure.
2. Set-AzContext Set the subscription context.
3. New-AzResourceGroup Create an Azure resource group.
4. New-AzVirtualNetwork Create a virtual network.
5. Add-AzVirtualNetworkSubnetConfig Add a subnet configuration to a virtual network.
6. Set-AzVirtualNetwork Updates a virtual network.
7. Get-AzVirtualNetwork Get a virtual network in a resource group.
8. Get-AzVirtualNetworkSubnetConfig Get a subnet in a virtual network.
9. New-AzNetworkSecurityGroup Create a network security group.
10. New-AzRouteTable Create a route table.
11. Set-AzVirtualNetworkSubnetConfig Update a subnet configuration for a virtual network.
12. Set-AzVirtualNetwork Update a virtual network.
13. Get-AzNetworkSecurityGroup Get a network security group.
14. Add-AzNetworkSecurityRuleConfig Add a network security rule configuration to a network security group.
15. Set-AzNetworkSecurityGroup Update a network security group.
16. Get-AzRouteTable Gets route tables.
17. Add-AzRouteConfig Add a route to a route table.
18. Set-AzRouteTable Update a route table.
19. New-AzSqlInstance Create a 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 there's no indirect overlap of the IP address ranges between the VNets hosting the primary and secondary instance, or any other VNets they are peered with via local virtual network peering or other means. If you're using Azure PowerShell to configure your managed instance, skip ahead to Create a secondary managed instance.

Portal

To verify the address 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 Address space under Settings and note the Address range of the subnet created automatically during creation of your primary instance. The Address range for the virtual network of the primary instance must not overlap with the address range of the virtual network for the secondary managed instance you plan to create, and any other virtual network peered with either the primary or secondary virtual network.

    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 virtual network, such as 10.128.0.0/24. This must not overlap with the IP address range used by any other virtual networks they are peered with.

    Secondary virtual network values

Azure PowerShell

This step is only necessary if you're using the Azure portal to deploy SQL Managed Instance. Skip ahead to Create a secondary managed instance if you're using Azure PowerShell.

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 that has no IP address range overlap with the virtual network address range hosting the primary managed instance, or any other virtual network peered with it.

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

Azure PowerShell

Create the secondary managed instance with Azure PowerShell by using the commands in this section, or check the Full script for an example.

Run these commands in the following order to create your secondary SQL Managed Instance by using Azure PowerShell.

Command Notes
1. New-AzVirtualNetwork Creates a virtual network.
2. Add-AzVirtualNetworkSubnetConfig Adds a subnet configuration to a virtual network.
3. Set-AzVirtualNetwork Updates a virtual network.
4. Get-AzVirtualNetwork Gets a virtual network in a resource group.
5. Get-AzVirtualNetworkSubnetConfig Gets a subnet in a virtual network.
6. New-AzNetworkSecurityGroup Creates a network security group.
7. New-AzRouteTable Creates a route table.
8. Set-AzVirtualNetworkSubnetConfig Updates a subnet configuration for a virtual network.
9. Set-AzVirtualNetwork Updates a virtual network.
10. Get-AzNetworkSecurityGroup Gets a network security group.
11. Add-AzNetworkSecurityRuleConfig Adds a network security rule configuration to a network security group.
12. Set-AzNetworkSecurityGroup Updates a network security group.
13. Get-AzRouteTable Gets route tables.
14. Add-AzRouteConfig Adds a route to a route table.
15. Set-AzRouteTable Updates a route table.
16. New-AzSqlInstance Creates a managed instance. Be sure to provide the -DnsZonePartner to link the secondary instance to your primary instance.

Create a global virtual network peering

Note

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

Portal

  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 in Azure portal

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

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

Azure PowerShell

Create a global virtual network peering with Azure PowerShell by using the commands in this section, or check the full script for an example.

Run these commands in the following order to peer the virtual networks for your two instances:

Command Notes
1. Get-AzVirtualNetwork Gets a virtual network in a resource group.
2. Add-AzVirtualNetworkPeering Adds a peering to a virtual network.
3. Get-AzVirtualNetworkPeering Gets a peering for a virtual network.

Create a failover group

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

Portal

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.

Azure PowerShell

Create the failover group using the following Azure PowerShell command, or check the Full script for an example.

Command Notes
New-AzSqlDatabaseInstanceFailoverGroup Creates a new Azure SQL Managed Instance failover group.

Test failover

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

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.

Azure PowerShell

Test failover by using the following Azure PowerShell commands, or check the full script for an example.

Command Notes
Get-AzSqlDatabaseInstanceFailoverGroup Gets or lists SQL Managed Instance failover groups.
Switch-AzSqlDatabaseInstanceFailoverGroup Executes a failover of a SQL Managed Instance failover group.

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.

Portal

  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.

Azure PowerShell

You will need to remove the resource group twice. Removing the resource group the first time will remove the managed instances and virtual clusters but will then fail with the error message Remove-AzResourceGroup : Long running operation failed with status 'Conflict'. Run the Remove-AzResourceGroup command a second time to remove any residual resources and the resource group.

Use the following Azure PowerShell command to remove the resource group:

Command Notes
Remove-AzResourceGroup Removes a resource group.

Full script

Azure PowerShell

[!code-powershell-interactivemain]

This script uses the following commands. Each command in the table links to command-specific documentation.

Command Notes
Connect-AzAccount Connect to Azure.
Set-AzContext Set the subscription context.
New-AzResourceGroup Creates an Azure resource group.
New-AzVirtualNetwork Creates a virtual network.
Add-AzVirtualNetworkSubnetConfig Adds a subnet configuration to a virtual network.
Get-AzVirtualNetwork Gets a virtual network in a resource group.
Get-AzVirtualNetworkSubnetConfig Gets a subnet in a virtual network.
New-AzNetworkSecurityGroup Creates a network security group.
New-AzRouteTable Creates a route table.
Set-AzVirtualNetworkSubnetConfig Updates a subnet configuration for a virtual network.
Set-AzVirtualNetwork Updates a virtual network.
Get-AzNetworkSecurityGroup Gets a network security group.
Add-AzNetworkSecurityRuleConfig Adds a network security rule configuration to a network security group.
Set-AzNetworkSecurityGroup Updates a network security group.
Get-AzRouteTable Gets route tables.
Add-AzRouteConfig Adds a route to a route table.
Set-AzRouteTable Updates a route table.
New-AzSqlInstance Creates a managed instance.
Get-AzSqlInstance Returns information about Azure SQL Managed Instance.
New-AzPublicIpAddress Creates a public IP address.
Add-AzVirtualNetworkPeering Adds a peering to a virtual network.
Get-AzVirtualNetworkPeering Gets a peering for a virtual network.
New-AzSqlDatabaseInstanceFailoverGroup Creates a new SQL Managed Instance failover group.
Get-AzSqlDatabaseInstanceFailoverGroup Gets or lists SQL Managed Instance failover groups.
Switch-AzSqlDatabaseInstanceFailoverGroup Executes a failover of a SQL Managed Instance failover group.
Remove-AzResourceGroup Removes a resource group.

Portal

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:

[!div class="checklist"]

  • 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:

[!div class="nextstepaction"] Connect to SQL Managed Instance Restore a database to SQL Managed Instance