Azure SQL - Geo-Replication

Enable same or cross-region high-availability for Azure SQL

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.

Geo-replication is supported in-region and cross-region. This tutorial deals with cross-region geo-replication of Azure SQL. If you would like cross-region auto-failover with Azure SQL, check out the Cloud Maker Auto-Failover Groups 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 two Azure SQL Servers configured to use geo-replication of 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 geo-replication for Azure SQL. Please reach out if you would like further information.

Drag, drop & configure your primary Azure SQL server

Next, 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 (N.B Microsoft recommends 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 (N.B. 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. primarydb (N.B. 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)

That's it, your Azure SQL instance in Region A is now configured. The next step is to add our secondary Azure SQL Server in Region B.

Add the secondary Azure SQL Server & enable geo-replication

Next, we need to add the secondary Azure SQL server to the design and configure geo-replication between the primary and secondary Azure SQL servers in Region A and Region B.

Note: Geo-replication provides a cross-region high availability solution for your Azure SQL Databases. Geo-replication leverages Always-On Availability Groups to facilitate replication. Read more about Azure SQL geo-replication here.

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

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

  3. Set the Administrator Login Name

  4. Set the Secret Parameter for the Administrator Login Password

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

  6. Set the name for the database, e.g. secondarydb (N.B. this can only be lowercase, numbers, and hyphens)

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

  8. Set 'Create Mode' to 'OnlineSecondary' using the drop-down menu

  9. Set the 'Source Database' to the primary database name from the dropdown

Awesome, you've now configured geo-replication between your primary and secondary Azure SQL servers.

Additionally, follow the Service Endpoints tutorial to secure network traffic between Azure SQL and your subnets.

Optional: Build your network and VM infrastructure

Finally, we can build our network infrastructure for use in conjunction with our Azure SQL Server.

  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

That's it! Save, then publish your blueprint and you can then head over the Pipelines area to configure a deployment pipeline and deploy your geo-replicated Azure SQL design to Azure.

Did this answer your question?