Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Test HA by using failover

  1. Home
  3. Test HA by using failover

Go back to DP-300 Tutorials

In this we will learn how to configure a failover group for Azure SQL Database.

Single database

Create the failover group and add a single database to it using the Azure portal or PowerShell.

Create failover group

For creating failover group and add your single database to it using the Azure portal.

  • Firstly, select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, 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.
  • Secondly, select the database you want to add to the failover group.
  • Thirdly, select the name of the server under Server name to open the settings for the server.
  • Then, select Failover groups under the Settings pane, and then select Add group to create a new failover group.
  • Lastly, on the Failover Group page, enter or select the required values, and then select Create.
    • Databases within the group: Choose the database you want to add to your failover group. Here, adding the database to the failover group will automatically start the geo-replication process.

Test failover

Test failover of your failover group using the Azure portal.

  • Firstly, select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, 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.
  • Secondly, select the database you want to add to the failover group.
  • Thirdly, select Failover groups under the Settings pane and then choose the failover group you just created.
  • Then, review which server is primary and which server is secondary.
  • After that, select Failover from the task pane to fail over your failover group containing your database.
  • Next, elect Yes on the warning that notifies you that TDS sessions will be disconnected.
  • Then , review which server is now primary and which server is secondary. If failover succeeded, the two servers should have swapped roles.
  • Lastly, select Failover again to fail the servers back to their original roles.

Elastic pool

Create the failover group and add an elastic pool to it using the Azure portal, or PowerShell.

Create the failover group

For creating your failover group and add your elastic pool to it using the Azure portal.

  • Firstly, select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, 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.
  • Secondly, select the elastic pool you want to add to the failover group.
  • Thirdly, on the Overview pane, select the name of the server under Server name to open the settings for the server.
  • Then, select Failover groups under the Settings pane, and then select Add group to create a new failover group.
  • After that, on the Failover Group page, enter or select the required values, and then select Create. Either create a new secondary server, or select an existing secondary server.
  • Next, select Databases within the group then choose the elastic pool you want to add to the failover group. However, if an elastic pool does not already exist on the secondary server, a warning appears prompting you to create an elastic pool on the secondary server. Select the warning, and then select OK to create the elastic pool on the secondary server.
  • Lastly, select Select to apply your elastic pool settings to the failover group, and then select Create to create your failover group. Adding the elastic pool to the failover group will automatically start the geo-replication process.

SQL Managed Instance

Create a failover group between two managed instances in Azure SQL Managed Instance by using the Azure portal or PowerShell. However, you will need to either configure ExpressRoute or create a gateway for the virtual network of each SQL Managed Instance, connect the two gateways, and then create the failover group. After that, 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 primary virtual network gateway

Create the primary virtual network gateway using the Azure portal.

  • Firstly, in the Azure portal, go to your resource group and select the Virtual network resource for your primary managed instance.
  • Secondly, select Subnets under Settings and then select to add a new Gateway subnet. Leave the default values.
  • 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. Then, select the Virtual network gateway resource published by Microsoft.
  • After that, fill out the required fields to configure the gateway your primary managed instance.
Dp-300 practice tests
The following table shows the values necessary for the gateway for the primary managed instance:
  1. Subscription: The subscription where your primary managed instance is.
  2. Name: The name for your virtual network gateway.
  3. Region: The region where your primary managed instance is.
  4. Gateway: type Select VPN.
  5. VPN: Type Select Route-based
  6. SKU: Leave default of VpnGw1.
  7. Location: The location where your secondary managed instance and secondary virtual network is.
  8. Virtual network: Select the virtual network for your secondary managed instance.
  9. Public IP: address Select Create new.
  10. Public IP: address name Enter a name for your IP address.
  • Then, leave the other values as default, and then select Review + create to review the settings for your virtual network gateway.
  • Lastly, select Create to create your new virtual network gateway.
Connect the gateways

Create connections between the two gateways using the Azure portal or PowerShell. However, two connections need to be created – the connection from the primary gateway to the secondary gateway. And then the connection from the secondary gateway to the primary gateway.

Create connections between the two gateways using the Azure portal.

  • Firstly, select Create a resource from the Azure portal.
  • Secondly, type connection in the search box and then press enter to search, which takes you to the Connection resource, published by Microsoft.
  • Thirdly, select Create to create your connection.
  • Next, on the Basics tab, select the following values and then select OK.
    • Select VNet-to-VNet for the Connection type.
    • Then, Select your subscription from the drop-down.
    • Select the resource group for your managed instance in the drop-down.
    • After that, Select the location of your primary managed instance from the drop-down.
  • Then, on the Settings tab, select or enter the following values and then select OK:
    • Choose the primary network gateway for the First virtual network gateway, such as Primary-Gateway.
    • Choose the secondary network gateway for the Second virtual network gateway, such as Secondary-Gateway.
    • Select the checkbox next to Establish bidirectional connectivity.
    • Either leave the default primary connection name, or rename it to a value of your choice.
    • Provide a Shared key (PSK) for the connection, such as mi1m2psk.
    • Create gateway connection
  • Lastly, on the Summary tab, review the settings for your bidirectional connection and then select OK to create your connection.
Test HA by using failover DP-300 online course

Reference: Microsoft Documentation

Go back to DP-300 Tutorials

Menu