| title | Tutorial: Add SQL Managed Instance to a failover group | ||
|---|---|---|---|
| titleSuffix | Azure SQL Managed Instance | ||
| description | In this tutorial, learn to create a failover group between a primary and secondary Azure SQL Managed Instance. | ||
| author | rajeshsetlem | ||
| ms.author | rsetlem | ||
| ms.reviewer | mathoma | ||
| ms.date | 06/02/2022 | ||
| ms.service | sql-managed-instance | ||
| ms.subservice | high-availability | ||
| ms.topic | tutorial | ||
| ms.custom |
|
[!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:
- Global virtual network peering - the most performant and recommended way
- Azure ExpressRoute
- VPN gateways
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
- 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 may take several hours to complete. For more information on provisioning times, see SQL Managed Instance management operations.
To complete this tutorial, make sure you have:
- An Azure subscription. Create a free account if you don't already have one.
To complete the tutorial, make sure you have the following items:
- An Azure subscription. Create a free account if you don't already have one.
- Azure PowerShell
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.
-
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 SQLin 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. -
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.
-
Select Create on the SQL Managed Instances tile.
-
On the Create Azure SQL Managed Instance page, on the Basics tab:
- 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. - 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.
- Under Administrator Account, provide an admin login, such as
azureuser, and a complex admin password.
- 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
-
Leave the rest of the settings at default values, and select Review + create to review your SQL Managed Instance settings.
-
Select Create to create your primary managed instance.
Create your resource group and the primary managed instance using PowerShell.
# Connect-AzAccount
# The SubscriptionId in which to create these objects
$SubscriptionId = '<Subscription-ID>'
# Create a random identifier to use as subscript for the different resource names
$randomIdentifier = $(Get-Random)
# Set the resource group name and location for SQL Managed Instance
$resourceGroupName = "myResourceGroup-$randomIdentifier"
$location = "eastus"
$drLocation = "eastus2"
# Set the networking values for your primary managed instance
$primaryVNet = "primaryVNet-$randomIdentifier"
$primaryAddressPrefix = "10.0.0.0/16"
$primaryDefaultSubnet = "primaryDefaultSubnet-$randomIdentifier"
$primaryDefaultSubnetAddress = "10.0.0.0/24"
$primaryMiSubnetName = "primaryMISubnet-$randomIdentifier"
$primaryMiSubnetAddress = "10.0.0.0/24"
$primaryMiGwSubnetAddress = "10.0.255.0/27"
$primaryGWName = "primaryGateway-$randomIdentifier"
$primaryGWPublicIPAddress = $primaryGWName + "-ip"
$primaryGWIPConfig = $primaryGWName + "-ipc"
$primaryGWAsn = 61000
$primaryGWConnection = $primaryGWName + "-connection"
# Set the networking values for your secondary managed instance
$secondaryVNet = "secondaryVNet-$randomIdentifier"
$secondaryAddressPrefix = "10.128.0.0/16"
$secondaryDefaultSubnet = "secondaryDefaultSubnet-$randomIdentifier"
$secondaryDefaultSubnetAddress = "10.128.0.0/24"
$secondaryMiSubnetName = "secondaryMISubnet-$randomIdentifier"
$secondaryMiSubnetAddress = "10.128.0.0/24"
$secondaryMiGwSubnetAddress = "10.128.255.0/27"
$secondaryGWName = "secondaryGateway-$randomIdentifier"
$secondaryGWPublicIPAddress = $secondaryGWName + "-IP"
$secondaryGWIPConfig = $secondaryGWName + "-ipc"
$secondaryGWAsn = 62000
$secondaryGWConnection = $secondaryGWName + "-connection"
# Set the SQL Managed Instance name for the new managed instances
$primaryInstance = "primary-mi-$randomIdentifier"
$secondaryInstance = "secondary-mi-$randomIdentifier"
# Set the admin login and password for SQL Managed Instance
$secpasswd = "PWD27!"+(New-Guid).Guid | ConvertTo-SecureString -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("azureuser", $secpasswd)
# Set the SQL Managed Instance service tier, compute level, and license mode
$edition = "General Purpose"
$vCores = 8
$maxStorage = 256
$computeGeneration = "Gen5"
$license = "LicenseIncluded" #"BasePrice" or LicenseIncluded if you have don't have SQL Server license that can be used for AHB discount
# Set failover group details
$vpnSharedKey = "mi1mi2psk"
$failoverGroupName = "failovergroup-$randomIdentifier"
# Show randomized variables
Write-host "Resource group name is" $resourceGroupName
Write-host "Password is" $secpasswd
Write-host "Primary Virtual Network name is" $primaryVNet
Write-host "Primary default subnet name is" $primaryDefaultSubnet
Write-host "Primary SQL Managed Instance subnet name is" $primaryMiSubnetName
Write-host "Secondary Virtual Network name is" $secondaryVNet
Write-host "Secondary default subnet name is" $secondaryDefaultSubnet
Write-host "Secondary SQL Managed Instance subnet name is" $secondaryMiSubnetName
Write-host "Primary SQL Managed Instance name is" $primaryInstance
Write-host "Secondary SQL Managed Instance name is" $secondaryInstance
Write-host "Failover group name is" $failoverGroupName
# Suppress networking breaking changes warning (https://aka.ms/azps-changewarnings
Set-Item Env:\SuppressAzurePowerShellBreakingChangeWarnings "true"
# Set the subscription context
Set-AzContext -SubscriptionId $subscriptionId
# Create the resource group
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup
# Configure the primary virtual network
Write-host "Creating primary virtual network..."
$primaryVirtualNetwork = New-AzVirtualNetwork `
-ResourceGroupName $resourceGroupName `
-Location $location `
-Name $primaryVNet `
-AddressPrefix $primaryAddressPrefix
Add-AzVirtualNetworkSubnetConfig `
-Name $primaryMiSubnetName `
-VirtualNetwork $primaryVirtualNetwork `
-AddressPrefix $PrimaryMiSubnetAddress `
| Set-AzVirtualNetwork
$primaryVirtualNetwork
# Configure the primary managed instance subnet
Write-host "Configuring primary MI subnet..."
$primaryVirtualNetwork = Get-AzVirtualNetwork -Name $primaryVNet -ResourceGroupName $resourceGroupName
$primaryMiSubnetConfig = Get-AzVirtualNetworkSubnetConfig `
-Name $primaryMiSubnetName `
-VirtualNetwork $primaryVirtualNetwork
$primaryMiSubnetConfig
# Configure the network security group management service
Write-host "Configuring primary MI subnet..."
$primaryMiSubnetConfigId = $primaryMiSubnetConfig.Id
$primaryNSGMiManagementService = New-AzNetworkSecurityGroup `
-Name 'primaryNSGMiManagementService' `
-ResourceGroupName $resourceGroupName `
-location $location
$primaryNSGMiManagementService
# Configure the route table management service
Write-host "Configuring primary MI route table management service..."
$primaryRouteTableMiManagementService = New-AzRouteTable `
-Name 'primaryRouteTableMiManagementService' `
-ResourceGroupName $resourceGroupName `
-location $location
$primaryRouteTableMiManagementService
# Configure the primary network security group
Write-host "Configuring primary network security group..."
Set-AzVirtualNetworkSubnetConfig `
-VirtualNetwork $primaryVirtualNetwork `
-Name $primaryMiSubnetName `
-AddressPrefix $PrimaryMiSubnetAddress `
-NetworkSecurityGroup $primaryNSGMiManagementService `
-RouteTable $primaryRouteTableMiManagementService | `
Set-AzVirtualNetwork
Get-AzNetworkSecurityGroup `
-ResourceGroupName $resourceGroupName `
-Name "primaryNSGMiManagementService" `
| Add-AzNetworkSecurityRuleConfig `
-Priority 100 `
-Name "allow_management_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange 9000,9003,1438,1440,1452 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 200 `
-Name "allow_misubnet_inbound" `
-Access Allow `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix $PrimaryMiSubnetAddress `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 300 `
-Name "allow_health_probe_inbound" `
-Access Allow `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix AzureLoadBalancer `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1000 `
-Name "allow_tds_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 1433 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1100 `
-Name "allow_redirect_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 11000-11999 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1200 `
-Name "allow_geodr_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 5022 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 4096 `
-Name "deny_all_inbound" `
-Access Deny `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 100 `
-Name "allow_management_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange 80,443,12000 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 200 `
-Name "allow_misubnet_outbound" `
-Access Allow `
-Protocol * `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix $PrimaryMiSubnetAddress `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1100 `
-Name "allow_redirect_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 11000-11999 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1200 `
-Name "allow_geodr_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 5022 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 4096 `
-Name "deny_all_outbound" `
-Access Deny `
-Protocol * `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Set-AzNetworkSecurityGroup
Write-host "Primary network security group configured successfully."
Get-AzRouteTable `
-ResourceGroupName $resourceGroupName `
-Name "primaryRouteTableMiManagementService" `
| Add-AzRouteConfig `
-Name "primaryToMIManagementService" `
-AddressPrefix 0.0.0.0/0 `
-NextHopType Internet `
| Add-AzRouteConfig `
-Name "ToLocalClusterNode" `
-AddressPrefix $PrimaryMiSubnetAddress `
-NextHopType VnetLocal `
| Set-AzRouteTable
Write-host "Primary network route table configured successfully."
# Create the primary managed instance
Write-host "Creating primary SQL Managed Instance..."
Write-host "This will take some time, see https://learn.microsoft.com/azure/sql-database/sql-database-managed-instance#managed-instance-management-operations or more information."
New-AzSqlInstance -Name $primaryInstance `
-ResourceGroupName $resourceGroupName `
-Location $location `
-SubnetId $primaryMiSubnetConfigId `
-AdministratorCredential $mycreds `
-StorageSizeInGB $maxStorage `
-VCore $vCores `
-Edition $edition `
-ComputeGeneration $computeGeneration `
-LicenseType $license
Write-host "Primary SQL Managed Instance created successfully."
This portion of the tutorial uses the following PowerShell cmdlets:
| Command | Notes |
|---|---|
| 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. |
| Add-AzRouteConfig | Adds a route to a route table. |
| Set-AzRouteTable | Updates a route table. |
| New-AzSqlInstance | Creates a managed instance. |
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:
-
In the Azure portal, navigate to your resource group and select the virtual network for your primary instance.
-
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.
To create a virtual network, follow these steps:
-
In the Azure portal, select Create a resource and search for virtual network.
-
Select the Virtual Network option and then select Create on the next page.
-
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. defaultis 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.
This step is only necessary if you're using the Azure portal to deploy SQL Managed Instance. Skip ahead to step 3 if you're using PowerShell.
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.
-
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 SQLin the search box. (Optional) Select the star next to Azure SQL to add it as a favorite item in the left-hand navigation. -
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.
-
Select Create on the SQL managed instances tile.
-
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. -
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.
-
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_AScollation and the(UTC) Coordinated Universal Timetime zone.
-
Select Review + create to review the settings for your secondary managed instance.
-
Select Create to create your secondary managed instance.
Create the secondary managed instance using PowerShell.
# Configure the secondary virtual network
Write-host "Configuring secondary virtual network..."
$SecondaryVirtualNetwork = New-AzVirtualNetwork `
-ResourceGroupName $resourceGroupName `
-Location $drlocation `
-Name $secondaryVNet `
-AddressPrefix $secondaryAddressPrefix
Add-AzVirtualNetworkSubnetConfig `
-Name $secondaryMiSubnetName `
-VirtualNetwork $SecondaryVirtualNetwork `
-AddressPrefix $secondaryMiSubnetAddress `
| Set-AzVirtualNetwork
$SecondaryVirtualNetwork
# Configure the secondary managed instance subnet
Write-host "Configuring secondary MI subnet..."
$SecondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet `
-ResourceGroupName $resourceGroupName
$secondaryMiSubnetConfig = Get-AzVirtualNetworkSubnetConfig `
-Name $secondaryMiSubnetName `
-VirtualNetwork $SecondaryVirtualNetwork
$secondaryMiSubnetConfig
# Configure the secondary network security group management service
Write-host "Configuring secondary network security group management service..."
$secondaryMiSubnetConfigId = $secondaryMiSubnetConfig.Id
$secondaryNSGMiManagementService = New-AzNetworkSecurityGroup `
-Name 'secondaryToMIManagementService' `
-ResourceGroupName $resourceGroupName `
-location $drlocation
$secondaryNSGMiManagementService
# Configure the secondary route table MI management service
Write-host "Configuring secondary route table MI management service..."
$secondaryRouteTableMiManagementService = New-AzRouteTable `
-Name 'secondaryRouteTableMiManagementService' `
-ResourceGroupName $resourceGroupName `
-location $drlocation
$secondaryRouteTableMiManagementService
# Configure the secondary network security group
Write-host "Configuring secondary network security group..."
Set-AzVirtualNetworkSubnetConfig `
-VirtualNetwork $SecondaryVirtualNetwork `
-Name $secondaryMiSubnetName `
-AddressPrefix $secondaryMiSubnetAddress `
-NetworkSecurityGroup $secondaryNSGMiManagementService `
-RouteTable $secondaryRouteTableMiManagementService `
| Set-AzVirtualNetwork
Get-AzNetworkSecurityGroup `
-ResourceGroupName $resourceGroupName `
-Name "secondaryToMIManagementService" `
| Add-AzNetworkSecurityRuleConfig `
-Priority 100 `
-Name "allow_management_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange 9000,9003,1438,1440,1452 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 200 `
-Name "allow_misubnet_inbound" `
-Access Allow `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix $secondaryMiSubnetAddress `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 300 `
-Name "allow_health_probe_inbound" `
-Access Allow `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix AzureLoadBalancer `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1000 `
-Name "allow_tds_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 1433 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1100 `
-Name "allow_redirect_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 11000-11999 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1200 `
-Name "allow_geodr_inbound" `
-Access Allow `
-Protocol Tcp `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 5022 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 4096 `
-Name "deny_all_inbound" `
-Access Deny `
-Protocol * `
-Direction Inbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 100 `
-Name "allow_management_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange 80,443,12000 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 200 `
-Name "allow_misubnet_outbound" `
-Access Allow `
-Protocol * `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix $secondaryMiSubnetAddress `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1100 `
-Name "allow_redirect_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 11000-11999 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 1200 `
-Name "allow_geodr_outbound" `
-Access Allow `
-Protocol Tcp `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix VirtualNetwork `
-DestinationPortRange 5022 `
-DestinationAddressPrefix * `
| Add-AzNetworkSecurityRuleConfig `
-Priority 4096 `
-Name "deny_all_outbound" `
-Access Deny `
-Protocol * `
-Direction Outbound `
-SourcePortRange * `
-SourceAddressPrefix * `
-DestinationPortRange * `
-DestinationAddressPrefix * `
| Set-AzNetworkSecurityGroup
Get-AzRouteTable `
-ResourceGroupName $resourceGroupName `
-Name "secondaryRouteTableMiManagementService" `
| Add-AzRouteConfig `
-Name "secondaryToMIManagementService" `
-AddressPrefix 0.0.0.0/0 `
-NextHopType Internet `
| Add-AzRouteConfig `
-Name "ToLocalClusterNode" `
-AddressPrefix $secondaryMiSubnetAddress `
-NextHopType VnetLocal `
| Set-AzRouteTable
Write-host "Secondary network security group configured successfully."
# Create the secondary managed instance
$primaryManagedInstanceId = Get-AzSqlInstance -Name $primaryInstance -ResourceGroupName $resourceGroupName | Select-Object Id
Write-host "Creating secondary SQL Managed Instance..."
Write-host "This will take some time, see https://learn.microsoft.com/azure/sql-database/sql-database-managed-instance#managed-instance-management-operations or more information."
New-AzSqlInstance -Name $secondaryInstance `
-ResourceGroupName $resourceGroupName `
-Location $drLocation `
-SubnetId $secondaryMiSubnetConfigId `
-AdministratorCredential $mycreds `
-StorageSizeInGB $maxStorage `
-VCore $vCores `
-Edition $edition `
-ComputeGeneration $computeGeneration `
-LicenseType $license `
-DnsZonePartner $primaryManagedInstanceId.Id
Write-host "Secondary SQL Managed Instance created successfully."
This portion of the tutorial uses the following PowerShell cmdlets:
| Command | Notes |
|---|---|
| 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. |
| Add-AzRouteConfig | Adds a route to a route table. |
| Set-AzRouteTable | Updates a route table. |
| New-AzSqlInstance | Creates a managed instance. |
Note
The steps listed below will create peering links between the virtual networks in both directions.
- In the Azure portal, go to the Virtual network resource for your primary managed instance.
- Select Peerings under Settings and then select + Add.
-
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 VirtualNetworkflow. 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. -
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.
Create global virtual network peering between virtual networks hosting primary and secondary instance.
# Peer the virtual networks
Write-host "Peering primary VNet to secondary VNet..."
$primaryVirtualNetwork = Get-AzVirtualNetwork `
-Name $primaryVNet `
-ResourceGroupName $resourceGroupName
$secondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet `
-ResourceGroupName $resourceGroupName
Write-host "Peering primary VNet to secondary VNet..."
Add-AzVirtualNetworkPeering `
-Name primaryVnet-secondaryVNet `
-VirtualNetwork $primaryVirtualNetwork `
-RemoteVirtualNetworkId $secondaryVirtualNetwork.Id
Write-host "Peering secondary VNet to primary VNet..."
Add-AzVirtualNetworkPeering `
-Name secondaryVNet-primaryVNet`
-VirtualNetwork $secondaryVirtualNetwork `
-RemoteVirtualNetworkId $primaryVirtualNetwork.Id
Write-host "Checking peering state on the primary virtual network..."
Get-AzVirtualNetworkPeering `
-ResourceGroupName $resourceGroupName `
-VirtualNetworkName $primaryVNet `
| Select PeeringState
Write-host "Checking peering state on the secondary virtual network..."
Get-AzVirtualNetworkPeering `
-ResourceGroupName $resourceGroupName `
-VirtualNetworkName $secondaryVNet `
| Select PeeringState
This portion of the tutorial uses the following PowerShell cmdlets:
| Command | Notes |
|---|---|
| Get-AzVirtualNetwork | Gets a virtual network in a resource group. |
| Add-AzVirtualNetworkPeering | Adds a peering to a virtual network. |
| Get-AzVirtualNetworkPeering | Gets a peering for a virtual network. |
In this step, you will create the failover group and add both managed instances to it.
Create the failover group using the Azure portal.
-
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 SQLin the search box. (Optional) Select the star next to Azure SQL to add it as a favorite item in the left-hand navigation. -
Select the primary managed instance you created in the first section, such as
sql-mi-primary. -
Under Data management, navigate to Failover groups and then choose Add group to open the Instance Failover Group page.
-
On the Instance Failover Group page, type the name of your failover group, such as
failovergrouptutorial. Then choose the secondary managed instance, such assql-mi-secondary, from the drop-down. Select Create to create your failover group.
-
Once failover group deployment is complete, you will be taken back to the Failover group page.
Create the failover group using PowerShell.
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
This portion of the tutorial uses the following PowerShell cmdlet:
| Command | Notes |
|---|---|
| New-AzSqlDatabaseInstanceFailoverGroup | Creates a new Azure SQL Managed Instance failover group. |
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.
-
Navigate to your secondary managed instance within the Azure portal and select Instance Failover Groups under settings.
-
Note managed instances in the primary and in the secondary role.
-
Select Failover and then select Yes on the warning about TDS sessions being disconnected.
-
Review that managed instance is the primary and which managed instance is the secondary. If failover succeeded, the two instances should have switched roles.
-
Go to the new secondary managed instance and select Failover once again to fail the primary instance back to the primary role.
Test failover using PowerShell.
# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName `
-Location $location -Name $failoverGroupName
# Fail over the primary managed instance to the secondary role
Write-host "Failing primary over to the secondary location"
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName `
-Location $drLocation -Name $failoverGroupName | Switch-AzSqlDatabaseInstanceFailoverGroup
Write-host "Successfully failed failover group to secondary location"
Revert the failover group back to the primary server:
# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName `
-Location $drLocation -Name $failoverGroupName
# Fail the primary managed instance back to the primary role
Write-host "Failing primary back to primary role"
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName `
-Location $location -Name $failoverGroupName | Switch-AzSqlDatabaseInstanceFailoverGroup
Write-host "Successfully failed failover group to primary location"
# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName `
-Location $location -Name $failoverGroupName
This portion of the tutorial uses the following PowerShell cmdlets:
| 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 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.
- Navigate to your resource group in the Azure portal.
- Select the managed instance(s) and then select Delete. Type
yesin 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. - Once the managed instance is deleted, delete the virtual cluster by selecting it in your resource group, and then choosing Delete. Type
yesin the text box to confirm you want to delete the resource and then select Delete. - Delete any remaining resources. Type
yesin the text box to confirm you want to delete the resource and then select Delete. - Delete the resource group by selecting Delete resource group, typing in the name of the resource group,
myResourceGroup, and then selecting Delete.
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.
Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Write-host "Removing SQL Managed Instance and virtual cluster..."
Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Write-host "Removing residual resources and resource group..."
This portion of the tutorial uses the following PowerShell cmdlet:
| Command | Notes |
|---|---|
| Remove-AzResourceGroup | Removes a resource group. |
[!code-powershell-interactivemain]
This script uses the following commands. Each command in the table links to command-specific documentation.
| Command | Notes |
|---|---|
| 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. |
| 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. |
There are no scripts available for the Azure portal.
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