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

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.