Skip to content

add-to-failover-group-powershell.md

Latest commit

 

History

History
174 lines (125 loc) · 12.2 KB

add-to-failover-group-powershell.md

File metadata and controls

174 lines (125 loc) · 12.2 KB
title PowerShell: Add a SQL managed instance to a failover group
titleSuffix Azure SQL Managed Instance
description Azure PowerShell example script to create a SQL managed instance, add it to a failover group, and test failover.
author Stralle
ms.author strrodic
ms.reviewer mathoma
ms.date 12/15/2023
ms.service azure-sql-managed-instance
ms.subservice high-availability
ms.topic sample
ms.custom
sqldbrb=1
devx-track-azurepowershell
ms.devlang powershell

Use PowerShell to add a SQL managed instance to a failover group

[!INCLUDEappliesto-sqldb]

[!div class="op_single_selector"]

This PowerShell script example creates two SQL managed instances, adds them to a failover group, and then tests failover from the primary SQL managed instance to the secondary SQL managed instance.

[!INCLUDE quickstarts-free-trial-note] [!INCLUDE updated-for-az] [!INCLUDE cloud-shell-try-it.md]

If you choose to install and use PowerShell locally, this tutorial requires Azure PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. If you're running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

Set your variables

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="SetVariables":::

Set subscription and create resource group

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="CreateResourceGroup":::

Command Notes
1. Connect-AzAccount Connect to Azure.
2. Set-AzContext Set the subscription context.
3. New-AzResourceGroup Create an Azure resource group.

Create both managed instances

First, create the primary SQL managed instance:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="CreatePrimaryInstance":::

Then, create the secondary SQL managed instance:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="CreateSecondaryInstance":::

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

Configure virtual network peering

Configure global virtual network peering between the virtual networks of the primary and secondary managed instances:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="VNetPeering":::

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 the failover group

Create the failover group:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="CreateFailoverGroup":::

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

Test planned failover

Test planned failover by failing over to the secondary replica, and then failing back.

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

Verify the roles of each server

Use the Get-AzSqlDatabaseInstanceFailoverGroup command to confirm the roles of each server:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="CheckRole":::

Fail over to the secondary server

Use the Switch-AzSqlDatabaseInstanceFailoverGroup to fail over to the secondary server.

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="Failover":::

Revert failover group back to the primary server

Use the Switch-AzSqlDatabaseInstanceFailoverGroup command to fail back to the primary server.

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="FailBack":::

Clean up deployment

Use the following command to remove the resource group and all resources associated with it. You'll need to remove the resource group twice. Removing the resource group the first time will remove the SQL managed instance 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 as well as the resource group.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Full script

The following snippet is the full script:

:::code language="powershell" source="~/../azure_powershell_scripts/azure-sql/managed-instance/failover-groups/add-managed-instance-to-failover-group-az-ps.ps1" id="FullScript":::

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 SQL managed instance.
Get-AzSqlInstance Returns information about Azure SQL Managed Instance.
New-AzPublicIpAddress Creates a public IP address.
New-AzVirtualNetworkGatewayIpConfig Creates an IP Configuration for a Virtual Network Gateway
New-AzVirtualNetworkGateway Creates a Virtual Network Gateway
New-AzVirtualNetworkGatewayConnection Creates a connection between the two virtual network gateways.
New-AzSqlDatabaseInstanceFailoverGroup Creates a new Azure 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.

Next steps

For more information on Azure PowerShell, see Azure PowerShell documentation.

Additional PowerShell script samples for SQL Managed Instance can be found in Azure SQL Managed Instance PowerShell scripts.