All Collections
Tutorials and Walkthroughs
Azure SQL
Azure SQL - Auto-Failover Groups
Azure SQL - Auto-Failover Groups

Design, configure and deploy a highly available Azure SQL solution

Nick Smith avatar
Written by Nick Smith
Updated over a week ago

Prerequisites

All droplets must be within an Azure Resource Group, configured with a valid name and location.

Auto-failover Groups are supported cross-region only. If you would like in-region high availability with Azure SQL, check out the Cloud Maker Geo-replication tutorial.

Here is an overview of the differences between Azure SQL geo-replication and auto-failover groups:

Capability

Geo-replication

Auto-failover Groups

Automatic Failover

No

Yes

Failover multiple databases simultaneously

No

Yes

Managed instance supported

No

Yes

Supports read-scale

Yes

Yes

Update connection string after failover

Yes

No

Can be in same region

Yes

No

Multiple replicas

Yes

No

Example Topology

This tutorial takes you through creating a simple network infrastructure, an Azure Virtual Machine and Azure SQL Servers configured to use auto-failover groups to replicate the primary Azure SQL database in Region A to a secondary Azure SQL database in Region B.

If you have followed the Azure SQL - Service Endpoint tutorial, you will have some of this topology designed already.

Note: This example would be part of a larger design. For instance, additional network and server resources would be deployed in Region B. As well as this, further network configuration would be required on the Azure Virtual Machine.

The purpose of this tutorial is to demonstrate configuring auto-failover groups for Azure SQL. Please reach out if you would like further information.

Add the Azure Resource Groups and primary Azure SQL server

First we will drag, drop & configure our Azure Resource Groups and then we will add the primary Azure SQL server to Resource Group A and configure a new database.

  1. Drag & drop two Azure Resource Groups onto your blueprint

  2. On the Properties Panel set the Name and Location for Resource Group A

  3. Set the Name and Location for Resource Group B (auto-failover groups require Resource Group B to be in a different region to Resource Group A. Microsoft recommend that Region Pairs are used for a High Availability configuration)

  4. Drag & drop an Azure SQL instance into Resource Group A

  5. Set the name of the Azure SQL Server (this can only be lowercase, numbers, and hyphens)

  6. Set the Administrator Login Name

  7. Set the Secret Parameter for the Administrator Login Password

  8. Select 'Databases' and create a new database by clicking 'Add new Database'

  9. Set the name for the database, e.g. mydatabase (this can only be lowercase, numbers, and hyphens)

  10. Set the 'Edition' property to Basic (the 'Edition' property is the main influence on the cost of deploying Azure SQL Server. Unless you have any specific requirements we recommend using the Basic edition)

We will come back to our primary Azure SQL Server in a later step to enable auto-failover groups.

Add the secondary Azure SQL Server to Resource Group B

We now need to add our secondary Azure SQL server to our design.

Note: Auto-failover groups provides a cross-region high availability solution for your Azure SQL Databases. If you require in-region resilience you can follow the Cloud Maker Azure SQL geo-replication tutorial here.

  1. Drag & drop your second Azure SQL instance into Resource Group B

  2. Set the name of the Azure SQL Server (this can only be lowercase, numbers, and hyphens)

  3. Set the Administrator Login Name

  4. Set the Secret Parameter for the Administrator Login Password

Great! You've now added both Azure SQL servers to your design. The final step is to configure auto-failover groups on the Primary Azure SQL server.

Configure auto-failover groups on the primary Azure SQL server

The final step is to configure auto-failover groups on our primary Azure SQL Server. You can read more about auto-failover groups here.

  1. Select your primary Azure SQL Server in Region A

  2. Select 'Failover Groups' from the property tree

  3. Click 'Add new Failover Group'

  4. Set the Name of your failover group

  5. Click 'Add new Database'

  6. From the drop-down menu select the database you created earlier

  7. Click 'Add new Partner Server' and select the secondary Azure SQL server

  8. Click 'Read Write Endpoint'

  9. Set 'Failover Policy' to automatic

  10. Set 'Failover With Data Loss Grace Period Minutes' to 5

That's it; you've successfully configured you primary and secondary Azure SQL servers with auto-failover groups.

Optional: Build your network and VM infrastructure

Now we've design and configured our Azure SQL auto-failover topology, we can build our network infrastructure for use in conjunction with Azure SQL Server. You can also see our Service Endpoints tutorial for creating a secure connection between Azure SQL and your subnet.

  1. Drag & drop an Azure Virtual Network into Resource Group A

  2. Set the Name for your Virtual Network and check the Address Space is as required

  3. Drag & drop an Azure Subnet into your Virtual Network (this will automatically add an NSG)

  4. Set the Name for your NSG

  5. Set the Name for your Subnet and check the Address Prefix is as required

  6. Drag & drop an Azure Virtual Machine into the Subnet

  7. Set the Name for the Virtual Machine

  8. Under 'Storage Profile' set the OS Disk Name for the Virtual Machine

  9. Under 'Network Interfaces' set the Network Interface Card Name for the Virtual Machine

  10. Under 'OS Profile' set the Computer Name, Admin Username and Password secret parameter for the Virtual Machine

You can now save, then publish your blueprint. You can then head over the Pipelines area to configure a deployment pipeline and deploy your Azure SQL auto-failover design to Azure.

Did this answer your question?