How to configure Fail-over and Geo-Replication for Microsoft Azure Database?

Dung DinhData Platform Consultant
CERTIFIED EXPERT
Published:
Edited by: Andrew Leniart
High Availability and Disaster Recovery are two of the most factors to make sure our business running. However, it requires much effort to configure and maintain. With Microsoft Azure solution, it's quite simple for us to configure.

Prerequisite condition

The following article gives some basic steps to create a SQL Database in Microsoft Azure

Introduction

Active Geo-Replication is Azure SQL Database feature that allows you to create readable replicas of your database in the same or different data center (region).

This feature is applied to Database level, not Server level.

Configuration

1- Login to Microsoft Azure Portal and connect to the database create. In my example, I created the database named as ddazuredemo



2- On Settings panel, click on Geo-Replication. I planned to replicated data from the East US region to the South Central US region



3- Click on the South Central US region. Create Secondary panel appears ==> Click on Target Server. In this step, we create a new secondary database server

Server Name: southcentralusdemo.database.window.net

Server admin login: adminacc

Check on Allow Azure Services access server



Click on the Select button

Pricing Tier: Basic, 1GB

Click OK and wait for a few minutes for initializing deployment

 


4- Create a new table and insert data into Primary Database Server and then observing how data is replicated to the Secondary server


4.1 Connect to the Primary Server ddazuredemo.database.window.net with SQL Server Management Studio

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StgSales](
[Date] [int] NULL,
[CustomerCode]
nvarchar NULL,
[ProductCode]
nvarchar NULL,
[SalesAmount] [float] NULL
) ON [PRIMARY]
GO

4.2  Insert data into StgSales table

INSERT INTO [dbo].[StgSales]
([Date]
,[CustomerCode]
,[ProductCode]
,[SalesAmount])
VALUES(20170101,’AW00011000′,’FR-R92B-58′,100),(20170101,’AW00011001′,’HL-U509′,200)



4.2 Connect to the Secondary Server southcentralus.database.window.net and query data.



4.3 The secondary server is only Read-Only mode so we cannot insert data




Fail-Over Configuration

With Geo-Replication, we must manually configure Fail-Over through our application or we do it manually in case the Primary Server is crashed or need to be upgraded.

Auto-failover groups are an extension of active Geo-Replication. It is designed to manage the failover of multiple Geo-replicated databases simultaneously using an application initiated failover or by delegating failover to be done by the SQL Database service based on user-defined criteria.

Auto-failover is applied at Server level, different with Geo-Replication is at the Database level

1- Connect to the database server ddazuredemo

2- Click on Failover groups —> + Add group


Failover groups appears

Failover group name: ddfailoverdemo

Secondary server: southcentralus

Read/Write failover policy: Automation

Database within this group: ddazuredemo. This is the place where allow us to select what databases we want to auto-failover

Click Create


After finishing the Auto-failover, we can see there are two connection end-point

Read/write listener endpoint: ddfailoverdemo.database.windows.net

Read-only listener endpoint: ddfailoverdemo.secondary.database.windows.net

When you connect to ddfailoverdemo.database.windows.net, it means we connect to the Primary database server, it can be either in the East US database server or South Central US database server.  In case, East US database is down, South Central US will be the Primary.

In case, we want to use data for reporting purpose or for load balancing of read-only workloads, we can use Read-only listener endpoint.


3- Connect to ddfailoverdemo.database.windows.net

4- Connect to ddfailoverdemo.secondary.database.windows.net


Testing Auto-FailOver

Because Microsoft Database Azure does not support start/stop server so it’s quite difficult for users to verify Auto-Failover.

Instead, we use Forced Failover on Azure Portal. After forcing Failover, the Primary database server is South Central US


Obviously, the Read/Write listener endpoint and Read-Only listener endpoint connection have no changes. We still work with these endpoints and don’t care which database server is PRIMARY.


0
505 Views
Dung DinhData Platform Consultant
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community